Menu
I'm trying to link SQL Server 2005 to an Oracle 10g database. I've installed the Oracle client on the SQL server and validated that I can connect to the Oracle database using both tnsping and sqlplus. When I try to run a query in SQL Server I get the following:
OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_CSSA2APD" 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 "OraOLEDB.Oracle" for linked server "ORA_CSSA2APD".
Any ideas? I've tried both of the following queries with no luck:
select * from openquery(ORA_CSSA2APD, 'select count(rowid) from eservice_op.agent')
select count(rowid) from ORA_CSSA2APD..eservice_op.agent
992
Follow the steps below to link your Oracle to SQL Server right from the Object Explorer pane: On the Management Studio, choose your SQL Server Instance. Click on the Server Objects from the Object Explorer pane and select Linked Servers, then New Linked Server.
Oracle provider and enable Allow inprocess. To create the Linked Server, connect the SQL Server engine from SQL Server Management Studio and navigate to Server Objects. Expand the Server Objects section, and you will find Linked Servers. Right-click on Linked Servers to create the New Linked Server.
SQL for Accessing, Defining, and Maintaining Data Oracle SQL provides an easy, elegant, performant architecture for accessing, defining, and maintaining data. Use SQL with Oracle and PHP, Java, Python, . NET, Hadoop, Node.
Oracle Database Gateways provide the ability to transparently access data residing in a non-Oracle system from an Oracle environment. The following sections briefly cover Heterogeneous Services, the technology that the Oracle Database Gateway for SQL Server is based on.
This issue happened to me, as well, but only with certain Windows user accounts. A combination of enabling the "Allow inprocess" provider option for the OraOLEDB.Oracle provide (SSMS > Server Objects > Linked Servers > Provides > OraOLEDB.Oracle), restarting the SQL Server Windows service and lastly adjusting the permissions on the TNSNAMES.ora file directly.
59
I suspect an environment setting. That is, your session is picking up the TNSNAMES.ORA file but the session underlying SQL Server is not. I'd check were ORACLE_HOME and, possibly, TNS_ADMIN are being set and pointing to.
Are you able to use the easy connect syntax for the database with the SQL Server connection .
IE replace ORA_CSSA2APD with hostname:1521/service_name
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
