Oct
14
2015

SQL Server to Oracle Linked server error 7303: ORA-12504 listener was not given the service_name in connect_data

While configuring Linked server to oracle database from SQL server, faced following error,

 

Error: “ORA-12504: TNS: listener was not given the SERVICE_NAME in CONNECT_DATA” (Microsoft SQL Server Error: 7303)

 

 

I did following validation checks to ensure things are in place:

  1. Tnsnames.ora – validated that SID is present in tnsnames.ora file which is generally located in C:\Oracle\product\11.2.0\client_1\network\admin\ folder
  2. Validate syntax of connection string, it should be in below format,

    ORA123 =

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = abcd123.domain.com)(PORT = 1521))

    (CONNECT_DATA =

    (SERVER = DEDICATED)

    (SERVICE_NAME = ora123)

    )

    )

  3. Validated that linked server is set as per tnsnames.ora entry,

     

  4. Validated that under providers, OraOLEDB.Oracle setting is for “Allow inprocess”

     

  5. Done tnsping which came out OK,

 

After validating all above, still the error was same.

 

After reviewing some blogs, seems fix was to update the “Provider String” with entry as below, once that is done, it fixed the issue,

 

Use provider string as below,

“data source=//ora123:1521/abcd123.domain.com”

 

Alternatively, user name and password can also be supplied but it is not recommended,

“userid=test123;[email protected]$k12098;data source=//ora123:1521/abcd123.domain.com”

 

 

After creating linked server, test using below query,

select
*
from
openquery(ora123,
‘select * from v$database’)

 

Hope this helps someone in same situation.

 

Useful references:

 

Install and setup linked server for oracle

https://www.mssqltips.com/sqlservertip/1433/how-to-setup-linked-servers-for-sql-server-and-oracle-64-bit-client/

http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/

 

Listener issues:

https://community.oracle.com/thread/367507?start=0&tstart=0

http://docs.oracle.com/cd/B19306_01/win.102/b14307/featConnecting.htm#sthref114

 

Query Oracle DB using linked server:

http://stackoverflow.com/questions/11309762/querying-an-oracle-database-from-sql-server

https://dbbulletin.wordpress.com/2010/06/30/using-powershell-for-oracle-dba-scripts/    

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

1 Comment + Add Comment

  • Thanks a lot for the information. It saved my day.

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 103 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

November 2018
M T W T F S S
« Nov    
 1234
567891011
12131415161718
19202122232425
2627282930  

View Post by Categories

%d bloggers like this: