I don't see why you'd be slammed for that, it's a perfectly sensible approach. I'm interested that you recommend the MS OLEDB provider though rather than a current Oracle one. The former doesn't support all Oracle datatypes particularly well (or in some cases at all). It would of course require a client install on the sql server which may be beyond acceptability :(
Niall On 4 Jan 2010 19:46, Kellyn Pedersen <kjped1313@xxxxxxxxx> wrote:
I may get slammed for this, but as a DBA in both platforms and utilizing both products in the past, I have to ask- Is there anyway you can simply utilize the Microsoft OLE DB Provider for Oracle, adding the Oracle database server as a linked server on the SQL Server side and query from there?
I find this to be more stable and require less DBA upkeep....
Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
"Go away before I replace you with a very small and efficient shell script..."
--- On Mon, 1/4/10, Ana Choto achoto@xxxxxxxxxxxx> wrote:
From: Ana Choto achoto@xxxxxxxxxxxx> Subject: Querying MS Sql from Oracle To: oracle-l@xxxxxxxxxxxxx Date: Monday, January 4, 2010, 9:05 AM
We have some MS Sql databases and need to run some queries from Oracle. Our Oracle database is at 9.2.0.8 version running on Solaris. According to Oracle we need to set up a Database Gateway for ODBC either on a Windows platform, Linux or Solaris.
Our MS Sql server is 64 bit, and apparently DG4ODBC is only certified for 32 bit on Windows and Linux. If on Solaris a third party like the ODBC driver from Data Direct Technologies is needed, and the 9i database needs to be patched. After installing DG4ODBC a listener and tnsnames.ora need to be configured. Then a database link needs to be created on Oracle.
The 9i database has something called hsodbc but Oracle says that is not certified anymore.
Has someone in the list done something like this?
Thank you
Ana
--------------------------------------------------------- Ana E. Choto Oracle Database Administrator American University Office of
Information Technology Phone (202) 885-2275 Fax (202) 885-2224
** AU IT will never ask for your password via e-mail. Don't share your password with anyone!