Microsoft Sql Server Error 7303 Linked Server Oracle
Contents |
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove sql server 2012 linked server oracle From My Forums Asked by: not able to create connect to sql server from oracle a link server to access an Oracle DB from SQL server 2005 SP2 SQL sql server oracle linked server query syntax Server > SQL Server Database Engine Question 0 Sign in to vote Hi all,I'm not able to create a link server to access oracle linked server equivalent an Oracle DB from SQL server. My environment: - Windos XP- SQL 2005 Dev SP2- Oracle 10g & Client & Oracle Provider for OLD DB 10.2... - MDAC 2.8 I linked Oracle from within SQL Management studio.I'm able to Ping the server using TNSPING oracleservername and
Cannot Initialize The Data Source Object Of Ole Db Provider "oraoledb.oracle" For Linked Server
I'm able to connect to sql server using oracel toolsI test the connection to Oracle via OraOLEDB.Oracle.1 using a UDL file and the connection is successful.Also I'm able to connect to the oracle server using the SSSI 2005 DTSwizard using the Oracle Provider for OLE DBI'm NOT Able to connect using the linked server: sp_addlinkedserver 'LS_ora10g', 'Oracle','OraOLEDB.Oracle','ORA10G_NAME.COM'gosp_addlinkedsrvlogin 'LS_ora10g','False', NULL, 'user', 'password' MyTNSNAME.org is like:ORA10G_NAME.COM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ORA10G_NAME.COM)(PORT = 1521)) ) (CONNECT_DATA = (SID = oracleservername) (SERVER = DEDICATED) ) When I try test the connection o GOT "The test connection to the linked server failed." Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "LS_ora10g". (Microsoft SQL Server, Error: 7302) Any ideas what is wrong ?? Is my sqlquery missing something???Thanks
Monday, September 10Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server
Cannot Create An Instance Of Ole Db Provider "oraoledb.oracle" For Linked Server
2005 » Administering » Linked Server connecting to Oracle problem Linked Server sql server linked server oracle provider string connecting to Oracle problem Rate Topic Display Mode Topic Options Author Message LOOKUP_BI-756009LOOKUP_BI-756009 Posted Monday, February 8, 2010 sp_helplinkedsrvlogin 2:13 PM SSC-Addicted Group: General Forum Members Last Login: 2 days ago @ 9:10 AM Points: 415, Visits: 1,479 I have SQL 2005 on my system and would like to https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5c57848b-d61e-4920-abad-4ba9eb1ceac5/not-able-to-create-a-link-server-to-access-an-oracle-db-from-sql-server-2005-sp2?forum=sqldatabaseengine 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 http://www.sqlservercentral.com/Forums/Topic862065-146-1.aspx 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
he was facing while creating a Linked Server to an Oracle database. He was trying to create a Linked Server to a Oracle Database from a SQL Server 2005 instance. He http://www.sqldbadiaries.com/2010/12/27/cannot-create-an-instance-of-ole-db-provider-oraoledb-oracle-for-linked-server/ had the Oracle Client components installed on the server hosting the SQL Server http://stackoverflow.com/questions/14499977/cannot-create-an-instance-of-ole-db-provider-error-as-windows-authentication-u instance. After giving all the details in the Linked Server properties when clicking on Ok, he would receive the following error message. Since the linked server is Oracle, the first thing that I tried was to check if the Server hosting the SQL Server instance, was able to connect to the listener on the Oracle linked server Database Server using tnsping. tnsping successfully connected to the Oracle database from the SQL Server box. Then I moved on to check the Provider details while creating the Linked Server. The Linked Server provider was Oracle Provider for OLE DB. This is the driver that gets installed along with the Oracle Client Components. This KB article states that Error 7302 can be a result of MSDAORA.dll not being registered linked server oracle correctly. In this case it had to be an issue with the dll related to Oracle Provider for OLE DB i.e.OraOleDB.dll. Before checking the dll related issues, I wanted to check if something was missing in the registry. The list of Providers in the Linked Server is populated from the Providers key in the registry. This key is located under the instance name under the below key. The entries in the registry looked like this. I could not find anything unusual here. Now it was confirmed that something must be wrong with the OraOleDB.dll. This dll is located under the bin directory of the folder where Oracle Client Components are installed (usually called oracle_home). When I navigated to that folder, I could not find any such files. To confirm this, I checked that directory on a *working* instance, the OraOleDB.dll was listed there. Now my job was easy. On the server which was having issues with the Linked Server, the Oracle Client Components were either not installed correctly or it was corrupt. I copied the dll from the *working* server and pasted it under
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 hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask 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 “Cannot create an instance of OLE DB provider” error as Windows Authentication user up vote 12 down vote favorite 7 I am trying to run openrowset from MS SQL Server on an Oracle server. When i execute the following command: select * from OPENROWSET('OraOLEDB.Oracle','srv';'user';'pass', 'select * from table') the following error occurs Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "(null)". Can anyone tell me how I can use openrowset with OraOLEDB.Oracle? I am using 64 bit version of MS SQL Server and Oracle OLEDB driver. Edit I have tried this on two machines running Windows 7 x64 & Windows Server 2008 x64 with MS SQL Server 2008 x64. Both showed the same error message. sql-server oracle oledb windows-authentication linked-server share|improve this question edited Nov 21 '13 at 14:10 patrickmdnet 2,3381627 asked Jan 24 '13 at 11:14 th1rdey3 2,05441544 I am trying to run OpenQuery against my linked server and getting same error. My linked server is using OraOLEDB.Oracle provider and I have already enabled the allow In Process option. The funny part is I have been using this linked server without any issue since last week, I did sql server reboot and it suddenly started to give error. –Amit Patel Jan 24 '13 at 17:22 add a comment| 4 Answers 4 active oldest votes up vote 28 down vote In SQL Server Enterprise Manager, open \Server Objects\Linked Servers\Providers, right click on the OraOLEDB.Oracle provider, select properties and check the "Allow inprocess" option. Recreate your linked server and test again. share|improve this answer edited Oct 1 '15 at 19:52 answered Jan 24 '13 at 12:28 David Brabant 18.1k64167 2 Brabank, I already tried that, but it still shows the same error. –th1rdey3 Jan 24 '13 at 12:47 Did you recreate your linked server? –David Brabant Jan 24 '13 at 12:48 yes, I have even restarted the sql service before recreating the linked server.