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?


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

  • My SQL Server is on a 32 bit machine - is this still an issue? Dec 16 '10 at 9: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? 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... Dec 22 '10 at 8:56

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .