Linked Servers and Oracle 10G
-
Hello everyone....
I am trying to create a Linked Server to an Oracle 10G box, in SQL Server 2005 (32-bit). I installed the 10G drivers, checked the TNSNames.ora file, and rebooted the box.
I can now successfully import and export to the Oracle instance with the wizard, and both the Microsoft OLEDB driver AND the Oracle.OLEDB driver work just fine. I was even able to create an SSIS package that imports data, and that connects fine as well.
When I go to set up the box as a Linked Server however, I get the dreaded "ORA-12154:TNS:could not resolve the connect identifier specified." I get the same result no matter which of the two drivers I use.
So what's the difference between the wizard/SSIS environments and the Linked Server piece? Anyone? Help?
Oracle is the DEBIL! Ack!
-
hAHaHa!! Madness!
Ok, after tearing into the SSIS package, and scripting out the commands that the Linked Server Wizard generates, I found a solution! (I have no idea why this works, a matter left for the guru's at Microsoft)
But! Using the Microsoft OLEDB for Oracle driver, and putting "OraOLEDB.Oracle.1" in the provider string, Management studio connected! And is happy! Contrary to another post on here I read, I CAN see all the tables and views on the Oracle box just fine.
When I went to connect to a second Oracle box, I no longer needed the provider string. Apparently showing Management Studio the first time was enough. Now everything works the way the documentation said it would....
When all else fails, try something silly.
-
Hi Dex
I'm trying to do the same but I don't know what to install exactly on the SQL server. Can you provide a short instruction, please?
Kind regards
Rene
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply