1

I have a SQL 2005 server that I need to link to an Oracle server.

I've installed the Oracle client, and followed several sets of instructions found online. I have an error every time I test the connection, telling me that the "Oracle client and networking components were not found."

This is using the MSDAORA provider - when I use the Oracle for OLE DB provider, I get "TNS:could not resolve the connect identifier specified".

I have some knowledge of SQL server, but no real knowledge of Oracle. The setup is: SQL Server 2005 on Windows 2003 (32bit), Oracle 11g on Windows 2008 (64bit).

I also read somewhere that there is a problem connecting a 32-bit server to a 64-bit one. Is this something that could be getting in my way?

What do the errors mean, and what might I be missing to make this all work?

pete the pagan-gerbil
  • 181
  • 2
  • 13

1 Answers1

1

This is a know issue with 64bit Oracle Client not working correctly with 64bit SQL Server.

Luckily there is a workaround. See this article: http://www.mssqltips.com/tip.asp?tip=1433

HTH, Dan

SQL3D
  • 670
  • 1
  • 6
  • 11
  • My SQL Server is on a 32 bit machine - is this still an issue? – pete the pagan-gerbil Dec 16 '10 at 09:22
  • 1
    In that case, no this issue doesn't apply. Just out of curiosity, have you tried running a TNSPING from your SQL Server to your Oracle server? – SQL3D Dec 16 '10 at 17:04
  • I will be honest with you - I don't even know what a TNSPING is. I click 'test connection' in SQL server, and it gives me the error messages described. Is TNSPING something different that might help diagnose the problem? – pete the pagan-gerbil Dec 21 '10 at 17:10
  • Sorry, TNSPING is installed with the Oracle client. On your SQL Server, go to the command prompt and type in "TNSPING oracleservername" (without the quotes). This will tell you if your TNSNames.ora file is set up correctly and if your SQL Server can even hit your Oracle server. – SQL3D Dec 21 '10 at 17:57
  • OK, I tried that - it seems to get all the information from TNSNames correctly, then prints 'OK (80ms)'. I'm presuming that means everything is hunky-dory... – pete the pagan-gerbil Dec 22 '10 at 08:56
  • Hmm, well it sounds like your Oracle client is set up correctly. And just to verify, you have the name of the Oracle server as it is listed in TNSNames.ora, entered in the "Data Source" field in your Linked Server Properties? – SQL3D Dec 22 '10 at 16:08
  • *facepalm* That's what was missing. I went over to double-check, and it was mis-spelled (though I'd removed and re-added a dozen times!) Thank you so much for your help! – pete the pagan-gerbil Dec 22 '10 at 17:21
  • Lol, no problem. It happens... – SQL3D Dec 22 '10 at 18:07