Setting Up An Oracle Linked Server

Question

Hi, I have a requirement to set up an Oracle linked server, Ive never done this before and in fact never touched Oracle so I dont really know what I am doing.

I have managed to set up a Oracle ODBC Driver on the server which when I click Test Coonnection tells me "Connection successful" so I beleive this is OK

The following script is what I have done to set up the link server

 

/****** Object:  LinkedServer [ORACLE_TEST]    Script Date: 09/15/2011 15:43:35 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ORACLE_TEST', @srvproduct=N'ORACLE', @provider=N'MSDASQL', @datasrc=N'SYSTEM_DNS_DATA_SOURCE_NAME'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORACLE_TEST',@useself=N'False',@locallogin=NULL,@rmtuser=N'SYSTEM_DNS_DATA_SOURCE_USER_NAME',@rmtpassword='SYSTEM_DNS_DATA_SOURCE_PASSWORD'

GO
EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORACLE_TEST', @optname=N'use remote collation', @optvalue=N'true'

When I click Test connection I receive the following error:

 

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "ORACLE_TEST".
OLE DB provider "MSDASQL" for linked server "ORACLE_TEST" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "ORACLE_TEST" returned message "[Oracle][ODBC][Ora]ORA-28547: connection to server failed, probable Oracle Net admin error
". (.Net SqlClient Data Provider)

 Any ideas to help solve please!?

 

Answer 1

James,

 

try this:

 

EXEC sp_addlinkedserver

@server = 'name',

@srvproduct = 'Oracle',

@provider = 'OraOLEDB.Oracle',

@datasrc = '(DESCRIPTION=ADDRESS = (PROTOCOL = TCP)(HOST = Host)(PORT = Post))(CONNECT_DATA=(SID=SID)))'

GO

EXEC sp_addlinkedsrvlogin

@rmtsrvname = 'Name',

@useself = 'FALSE',

@rmtuser = 'User',

@rmtpassword = 'Password'

GO

 

 

The information of datastc are on tnsnames.ora

---

Fabrizzio A. Caputo
Certificações:
Oracle OCA 11g
MCITP SQL Server 2008 Implementation and Maintenance
MCITP SQL Server 2008 Developer
Blog Pessoal: www.fabrizziocaputo.wordpress.com
Blog Empresa: www.tripletech.com.br/blog
Twitter: @FabrizzioCaputo
Email: [e-mail]

Answer 2

I get a different error message

 

The OLE DB provider "OraOLEDB.Oracle" has not been registered. (.Net SqlClient Data Provider)

 

I am have instantclient installed and trying to connect via odbc.

What do I need to install to make your method work?

*EDIT*

I installed Oracle Data Access Components and now I get this error

 

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TESTORACLE2".
OLE DB provider "OraOLEDB.Oracle" for linked server "TESTORACLE2" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (.Net SqlClient Data Provider)

 

Any ideas?

Answer 3

Hi jameslester78,

Could you please check that you have used the data source field with the same as Oracle Net Manager Service Naming tree?

Please take a look at the following thread with the same issue:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1562c89a-c0c9-4fc7-9aed-9ee3b6516a54

---

Best Regards,
Stephanie Lv

Forum Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact [e-mail].

Answer 4

James,

 

try this:

 

EXEC sp_addlinkedserver

@server = 'name',

@srvproduct = 'Oracle',

@provider = 'OraOLEDB.Oracle',

@datasrc = '(DESCRIPTION=ADDRESS = (PROTOCOL = TCP)(HOST = Host)(PORT = Post))(CONNECT_DATA=(SID=SID)))'

GO

EXEC sp_addlinkedsrvlogin

@rmtsrvname = 'Name',

@useself = 'FALSE',

@rmtuser = 'User',

@rmtpassword = 'Password'

GO

 

 

The information of datastc are on tnsnames.ora

---

Fabrizzio A. Caputo
Certificações:
Oracle OCA 11g
MCITP SQL Server 2008 Implementation and Maintenance
MCITP SQL Server 2008 Developer
Blog Pessoal: www.fabrizziocaputo.wordpress.com
Blog Empresa: www.tripletech.com.br/blog
Twitter: @FabrizzioCaputo
Email: [e-mail]

Thanks, this resolved my issue. Kind Regards