Answered by:
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 OKThe 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!?- Edited by jameslester78 Thursday, September 15, 2011 3:03 PM
Thursday, September 15, 2011 3:02 PM
Answers
-
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 tnmff@microsoft.com.- Proposed as answer by Stephanie Lv Friday, September 23, 2011 12:07 PM
- Marked as answer by Stephanie Lv Monday, September 26, 2011 1:07 PM
Tuesday, September 20, 2011 12:20 PM
All replies
-
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: fabrizzio.antoniaci@gmail.comThursday, September 15, 2011 7:18 PM -
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?
Monday, September 19, 2011 8:17 AM -
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 tnmff@microsoft.com.- Proposed as answer by Stephanie Lv Friday, September 23, 2011 12:07 PM
- Marked as answer by Stephanie Lv Monday, September 26, 2011 1:07 PM
Tuesday, September 20, 2011 12:20 PM -
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: fabrizzio.antoniaci@gmail.comThanks, this resolved my issue. Kind Regards
Wednesday, June 3, 2015 1:13 PM