I'm a newbie, forced into being a database query-writer by necessity, so please bear with my horrible explanations. I have google-searched this to the end of google, and cannot find an answer for my particular problem, though I've been able to solve many issues to get here -- so, I am trying.

I have a SQL Server database set up on a SQL 2005 server, and I need to link Oracle (11g) data to it.

I've successfully installed the necessary Oracle software onto that Windows 2003 server housing my SQL 2005 database. I set up my linked server, and can query when I remote desktop into the Win2003 server and run SSMS from there.

HOWEVER, I have SSMS 2008 installed on MY PC. This PC can also (independent of SQL) access the same Oracle database - I have client software set up on it. But when I try to access the Linked Server, remotely, from my PC, using SSMS 2008, I get errors:

HOWEVER, I have SSMS 2008 installed on MY PC. This PC can also (independent of SQL) access the same Oracle database - I have client software set up on it. But when I try to access the Linked Server, remotely, from my PC, using SSMS 2008, I get errors:

Just to understand your setup, before trying to fix anything -

So you've only got SSMS, as a client tool to access and manage the 2005 Server on Win 2003, on your PC. Not a second MS Sql Database service, right?

In that case, you don't need the Oracle client software on that pc, in a Linked server context. You'd only need it on the MS Sql (2005) host.

How are you accesing the Linked server from SSMS 2008? First connected to the remote 2005 db engine?

Sorry -- I didn't see your reply until now. I hope I can answer your questions.

orafad wrote:

Just to understand your setup, before trying to fix anything -

So you've only got SSMS, as a client tool to access and manage the 2005 Server on Win 2003, on your PC. Not a second MS Sql Database service, right?

In that case, you don't need the Oracle client software on that pc, in a Linked server context. You'd only need it on the MS Sql (2005) host.

How are you accesing the Linked server from SSMS 2008? First connected to the remote 2005 db engine?

Where is the Oracle server running? On a third machine?

I have SSMS 2008 as a client tool on my pc. I have SSMS 2005 on the server on which my 2005 SQL databases (and the link to my Oracle Linked Server).

I have the Oracle 11g client on both - it's on MY pc because I need it for other things; it's on the SQL database server because I'm pretty sure I needed it to create the linked server connection in SSMS, there.

To simplify, you could try entering <hostname>:<port>/<servicename> (fill in Oracle related details) for Data source in the Linked server properties.

I don't know the port #!

In this case, tnsping from the Win 2003 instead. That's where Linked server does the lookup.

There is NO tnsping installed, apparently, with Oracle 11g client? I cannot find tnsping.exe on that server. The client is there, my entries pointing to my TNS_ADMIN are in my registry...

Any help is appreciated -- not sure why I didn't get an email notification of your previous reply, but I'll keep my eyes open, now...

I've been successful in setting up a linked server on our SQL Server 2008 server to an Oracle 11g database, but still cannot access this linked server when I run SSMS from a PC other than the SQL Server.

3) I have set up a linked server called EPLIVE on SISDB2, using provider OraOLEDB.Oracle. I was somehow successful in making this connection by installing both the 32-bit and 64-bit Oracle clients (11.2.0.1.0). Relevant (I believe) options installed are Oracle Net, Oracle Net Listener, Oracle Netca Client, Oracle ODBC Driver, Oracle Services for MTS, Oracle Provider for OLE DB, Oracle Data Provider for .NET, Oracle Providers for ASP.NET. Not sure I needed all those options, but that's where I am.

4) There are no local copies of TNSNAMES.ORA or SQLNET.ORA on SISDB2\C:. There is a registry entry called TNS_ADMIN that points to a SHARED tnsnames.ora on our network (UNC addressing) in both the HKLM\SOFTWARE\ORACLE and HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1 branches of the registry.

5) I can TNSPING my eplive SID/Service (sorry -- I get confused at the difference between SID and SERVICE) from a command prompt on SISDB2.

6) I can sqlplus into eplive and query the database, from a command prompt on SISDB2.

7) On SISDB2, in SSMS, I can test the connection to EPLIVE, and run OPENQUERies against it.

8) Under the Provider properties, Allow InProcess is enabled, on SISDB2.

On my 32-bit Windows 7 desktop, I run SSMS 2008 locally.
1) I see the linked server when I connect to SISDB2.

2) Numbers 4, 5 and 6 from above are all also true on my Desktop PC.

3) Number 7 from above is NOT true -- I get an ORA-12154 error when I try to test the connection to the linked server EPLIVE on SISDB2 from a remote copy of SSMS 2008.

I've undoubtedly left off some information about my configuration, but I do not understand why, if EPLIVE is a recognized SID on the desktop, I cannot use the linked server that is functional in a "remote" copy of SSMS 2008.

I would greatly appreciate any clues that can resolve this, for me. I've googled the daylights out of this problem, and find lots of things to try, but nothing that has resolved this.