Linkserver connection fails between SqlServer 2005 and Oracle 10g

SQL Server Data Access

Jay87


This is what I did:

The instructions for changing the registry entries per previous MSDN postings were:

For Windows 2000
Oracle

8.1 [HKEY_LOCAL_MACHINE\SOFTWARE
\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"

Changed the registry entries on my WindowsXP machine:


entry name old value new values
"OracleXaLib" xa80.dll oraclient10.dll
"OracleSqlLib" sqllib80.dll orasql10.dll
"OracleOciLib" oci.dll oci.dll

Then created the Linkserver definition:

EXEC sp_addlinkedserver 'ORA10G', 'ORACLE', 'MSDAORA', 'ORA10G'
Command(s) completed successfully.

EXEC sp_addlinkedsrvlogin 'ORA10G', 'FALSE',NULL, 'scott', 'tiger'

Command(s) completed successfully.

Note:

scott/tiger is given as an example here; used my real userid/pw instead. BTW, I am able to connect

to the Oracle database using the userid/pw from the same host that is running the Sqlserver db.So, the

Oracle client software is installed and is working correctly.

Then attempted to query a table on the Oracle database:

select count(*) from ORA10G..<schemaname>.<tablename>

Got the following error message(s)

OLE DB provider "MSDAORA" for linked server "ORA10G" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "ORA10G".

All the previous discussions and suggested solutions are all for SqlServer 2000 and Oracle8. But I did not see much discussions on SqlServer2005 and Oracle10g. Any help to resolve this problem will be appreciated.

Thank you.

Top

Re: Linkserver connection fails between SqlServer 2005 and Oracle 10g

Anton Klimov - MSFT


MSDAORA does not officially support Oracle10.
Did you try Oracle's own provider





Top

Re: Linkserver connection fails between SqlServer 2005 and Oracle 10g

Jay87

I don't see the option to use Oracle's driver during the dialog to create the new Linkserver. What do I have to do to include Oracle's driver in that drop down list Do I have to copy some file to a specific folder before attempting to create the Linkserver Besides, I do not know the name of the Oracle's driver file on my XP workstation - am still researching that one.

Any suggestions from you will be most appreciated!

Jay



Top

Re: Linkserver connection fails between SqlServer 2005 and Oracle 10g

Anton Klimov - MSFT

Oracle's provider should be OraOLEDB.Oracle.
Have a look at http://msdn2.microsoft.com/en-us/library/ms152516(SQL.90).aspx and the other links referred from there.



Top

Re: Linkserver connection fails between SqlServer 2005 and Oracle 10g

Brian Matuschak

Anton,

It turns out that I had wrong registry entries due to the fact I installed a company version of an Oracle driver setup earlier; I since have installed the Oracle client "Administrator" option for 10g for my networking components. However, I cannot seem to get my linked server in SS 2005 to work despite the fact that I was able to connect to my Oracle service using the command prompt:

C:\Documents and Settings\brmatusc>sqlplus brmatusc/*******@PADBSPK

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 1 12:25:48 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

And I am able to connect to the database through ODBC through Access 2003. But I'm getting the same kind of error when I try to create the linked server within Management Studio:

TITLE: Microsoft SQL Server Management Studio
------------------------------

"The linked server has been created but failed a connection test. Do you want to keep the linked server "

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "PADBSPK".
OLE DB provider "OraOLEDB.Oracle" for linked server "PADBSPK" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303)

Any suggestions

Thanks,

Brian J. Matuschak


Top

Re: Linkserver connection fails between SqlServer 2005 and Oracle 10g

Emanuel Peixoto

Hi, Jay87,

try to use the sp_setnetname stotred procedure to link the name of your linked server to the correct name of your oracle server machine

sp_setnetname
@server = 'server', 
          @netname = 'network_name'




Top

Re: Linkserver connection fails between SqlServer 2005 and Oracle 10g

Emanuel Peixoto 

sp_setnetname
@server = 'PADBSPK', 
          @netname = 'network_name'





Top

Re: Linkserver connection fails between SqlServer 2005 and Oracle 10g

Brian Matuschak

Thanks, Emanuel. However, I get this following error message:

Msg 15576, Level 16, State 1, Procedure sp_setnetname, Line 43

You cannot set network name on server 'PADBSPK' because it is not a linked SQL Server.

In addition, BOL states the following in the topic: "Sets the network names in sys.servers to their actual network computer names for remote instances of SQL Server 2005." So the implication is that it has to be an SS 2005 linked server rather than another type of DB server.

Brian J. Matuschak


Top

Re: Linkserver connection fails between SqlServer 2005 and Oracle 10g

Emanuel Peixoto

Hi, can you see you oracle server in sys.servers

to see the servers you can also execute sp_helpserver.

if your server is not in sys.servers,executing:

EXEC sp_addserver 'rpcserv2'





Top

Re: Linkserver connection fails between SqlServer 2005 and Oracle 10g

Emanuel Peixoto

After you execute a sp_addserver, then go back to execute sp_setnetname





Top

Re: Linkserver connection fails between SqlServer 2005 and Oracle 10g

Brian Matuschak

Emanuel,

I can see the linked server through Object Explorer and through the results of sp_helpserver. However, when I use the sp_addserver sproc, it only lets me use the SQLNCLI provider, and it won't let me configure it for data access. I get this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

"The linked server has been updated but failed a connection test. Do you want to edit the linked server properties "

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Named Pipes Provider: Could not open a connection to SQL Server [1214].
OLE DB provider "SQLNCLI" for linked server "PADBSPK" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "PADBSPK" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 1214)

Thanks for your help anyway,

BJM


Top

Re: Linkserver connection fails between SqlServer 2005 and Oracle 10g

Brian Matuschak

Finally, success! Apparently, the client tools for Oracle that are needed for the 10g client do not create a needed environment variable for using the OraOLEDB.Oracle provider and throw an error such as the following:

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "PADBSPK".
OLE DB provider "OraOLEDB.Oracle" for linked server "PADBSPK" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303)

To correct this problem, go to "My Computer," click the "Advanced" tab, and click the "Environment Variables" button. Create an "ORACLE_HOME" environment variable by clicking the "New" button and entering "ORACLE_HOME" (without the quotes) in the "Variable name" text box, and the path to the client in the "Variable value" box. (Mine was in the default directory of C:\Oracle\Product\10.2.0\client1, so this was the value used.)

In order to query the data, it is not stated in Books Online that within the four-part naming convention that you need to use brackets such as the following: SELECT * FROM [LINKEDSERVERNAME]..[DATABASE].[TABLE].

Hope this saves someone hours of work!

Brian J. Matuschak

brian@electronic-atlas.com





Top