Linked Server Error Ora 12154
360 games PC games Windows games Windows phone games Entertainment All Entertainment Movies & TV Music Business & Education Business Students & educators Developers Sale Sale Find a store Gift cards Products Software & services Windows Office Free downloads & security Internet Explorer Microsoft Edge Skype OneNote OneDrive Microsoft Health MSN Bing Microsoft Groove Microsoft Movies & TV Devices & Xbox All Microsoft devices Microsoft Surface All Windows PCs & tablets PC accessories Xbox & games Microsoft Lumia All Windows phones Microsoft HoloLens For business Cloud Platform Microsoft Azure Microsoft Dynamics Windows for business Office for business Skype for business Surface for business Enterprise solutions Small business solutions Find a solutions provider Volume Licensing For developers & IT pros Develop Windows apps Microsoft Azure MSDN TechNet Visual Studio For students & educators Office for students OneNote in classroom Shop PCs & tablets perfect for students Microsoft in Education Support Sign in Cart Cart Javascript is disabled Please enable javascript and refresh the page Cookies are disabled Please enable cookies and refresh the page CV: {{ getCv() }} English (United States) Terms of use Privacy & cookies Trademarks © 2016 Microsoft
up Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2005 » Administering » Linked Server connecting to Oracle problem Linked Server connecting to Oracle problem Rate Topic Display Mode Topic Options Author Message LOOKUP_BI-756009LOOKUP_BI-756009 Posted Monday, February 8, 2010 2:13 PM SSC-Addicted Group: General Forum Members Last Login: Yesterday @ 9:10 AM Points: 415, Visits: 1,479 I have SQL 2005 on my system and https://support.microsoft.com/en-us/kb/280106 would like to connect to Oracle 10g as a linked server.This is the error mssg Im gettingOLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink".This is what has been done http://www.sqlservercentral.com/Forums/Topic862065-146-1.aspx so far 1) Installed Oracle Client for 10g on my machine2) The C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN has 2 files [tnsnames] and [sqlnet]3) Contents on tnsnames is as belowgp = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = cs.org)(PORT = 1526)) ) (CONNECT_DATA = (SERVICE_NAME = gp2) ))4) Executing tnsping gp returns TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-FEB-2010 16:02:10Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:C:\oracle\product\10.2.0\client_1etwork\admin\sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = cs.org)(PORT = 1526))) (CONNECT_DATA = (SERVICE_NAME = gp201)))OK (20 msec)5) Execute sqlplus login/123@gp returnsSQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 8 16:04:35 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options6) In mngt studio, used following script to create linked serverEXEC sp_addlinkedserver @server = 'TestOraLink', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle', @datasrc = 'gp'7) Followed byEXEC sp_addlinkedsrvlogin 'TestOraLink', false, 'login', 'login', '123'8) To test execute SELECT * FROM OPEN
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about http://stackoverflow.com/questions/34020041/sql-server-linked-oracle-server-working-on-one-instance-but-not-another-on-the-s hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139543 Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL Server linked Oracle Server working on one instance but not another on the same server up vote 0 down vote favorite I have two instances of SQL Server 2012 Standard linked server edition running on the same SQL Server. The SQL Server Service for both is running as the same domain account. I have two linked servers (to oracle servers) that are working fine on one instance, however they do not work on the other. Both instances are 64 bit as is the Oracle client (version Oracle version 11.2.01) The error message given is: The linked server has been updated but failed a connection test. Do you want to edit linked server error the linked server properties? Additional information: An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectInfo) Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "[LINKEDSERVERNAME]" OLE DB Provider "OraOLEDB.Oracle" for linked server "LINKEDSERVERNAME" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303) -- My TNS Names.ORA file resides in C:\Oracle\ODAC11201x64 On the SQL server and has the relevant entries. There is also an identical file that resides in C:\Oracle\ODAC11201x64\Network\Admin The linked server settings are identical on both instances as are the providers (both OraOLEDB.Oracle providers have both of the linkes servers listed as using them and both have 'Dynamic parameter' and 'Allow inprocess' ticked. I have restarted the SQL server service for the instance on which the linked servers do not work after creating them. I am completely stumped. Everything seems identical and I can't think what would be different on the one instance to the other that would cause this. sql-server oracle share|improve this question edited Dec 1 '15 at 12:19 Bob Jarvis 24.6k43766 asked Dec 1 '15 at 12:04 Kev 1 Just a suggestion, script both of them out and compare them. I find it easier to see the difference than looking at the settings on multiple tabs. –Steve Dec 1 '15 at 15:44 add a comment| active oldest votes Know someone who can answer? Share a
SQL Server experts to answer whatever question you can come up with. Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums SQL Server 2005 Forums SQL Server Administration (2005) Oracle Linked Server SEtup Error Reply to Topic Printer Friendly Author Topic LOOKUP_BI Constraint Violating Yak Guru 295 Posts Posted-02/08/2010: 16:18:07 I have SQL 2005 on my system and would like to connect to Oracle 10g as a linked server.This is the error mssg Im gettingOLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink".This is what has been done so far 1) Installed Oracle Client for 10g on my machine2) The C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN has 2 files [tnsnames] and [sqlnet]3) Contents on tnsnames is as belowgp =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = cs.org)(PORT = 1526)))(CONNECT_DATA =(SERVICE_NAME = gp2)))4) Executing tnsping gp returns TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-FEB-2010 16:02:10Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:C:\oracle\product\10.2.0\client_1etwork\admin\sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = cs.org)(PORT = 1526))) (CONNECT_DATA = (SERVICE_NAME = gp201)))OK (20 msec)5) Execute sqlplus login/123@gp returnsSQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 8 16:04:35 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options6) In mngt studio, used following script to create linked serverEXEC sp_addlinkedserver @server = 'TestOraLink', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle', @datasrc = 'gp'7) Followed byEXEC sp_addlinkedsrvlogin 'TestOraLink', false, 'login', 'login', '123'8) To test execute SELECT * FROM OPENQUERY(TestOraLink, 'select * from test_table2')Can someone please guide me on what is it that Im missing ? Thanks Kristen Test United Kingdom 22859 Posts Posted-02/09/2010: 08:03:40