Microsoft Sql Server Error 7303 Oraoledb Oracle
Contents |
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Asked by: not able to create a link server to access an Oracle DB from SQL server 2005 sql server 2012 linked server oracle SP2 SQL Server > SQL Server Database Engine Question 0 Sign in to
Cannot Create An Instance Of Ole Db Provider Oraoledb.oracle For Linked Server
vote Hi all,I'm not able to create a link server to access an Oracle DB from SQL server. My environment:
Microsoft Sql Server Error 7303 Linked Server
- 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
Sql Server Oracle Linked Server Query Syntax
server using TNSPING oracleservername and 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 oracle linked server equivalent 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 10, 2007 11:14 PM Reply | Quote All replies 2 Sign in to vote This is most probably due to the Oracle provider loaded out of process. If you set it to load in process then it should work. So in SSMS, go to Server Objects\Linked Servers\Providers and locate the Oracle provider. Set the 'AllowInProcess' option to true. You will have to restart SQL Server services and try again. Saturday, September 15, 2007 6:47 PM Reply | Quote 0 Sign in to vote Hi Thanks for your response. Yes I check the AllowInProcess set to true, but still doesn't work. My Registry on MSDTC is like this: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI OracleOciLib = oci.dllOracleSqlLib = SQLLib80.dllOracleXaLib = xa80.dll May be this values shoudl point to the new dhere 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 cannot initialize the data source object of ole db provider "oraoledb.oracle" for linked server ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join microsoft sql server error 7302 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 cannot create an instance of ole db provider for linked server 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 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 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 http://stackoverflow.com/questions/14499977/cannot-create-an-instance-of-ole-db-provider-error-as-windows-authentication-u 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. but still no luck. –th1rdey3 Jan 24 '13 at 12:56 2 Thanks for this answer, it saved my day. –binu Jul 30 '13 at 15:04 5 In my experi
he was facing while creating a Linked Server to an Oracle database. He was trying to create a Linked Server to http://www.sqldbadiaries.com/2010/12/27/cannot-create-an-instance-of-ole-db-provider-oraoledb-oracle-for-linked-server/ a Oracle Database from a SQL Server 2005 instance. He had the Oracle Client components installed on the server hosting the SQL Server instance. After giving all the details http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139543 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 linked server to check if the Server hosting the SQL Server instance, was able to connect to the listener on the Oracle 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 microsoft sql server 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 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 m
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 Produ