Microsoft Sql Server Error 7303 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 2012 linked server oracle SQL server 2005 SP2 SQL Server > SQL Server Database Engine Question
Microsoft Sql Server Error 7303 Linked Server
0 Sign in to vote Hi all,I'm not able to create a link server to access an Oracle DB from cannot create an instance of ole db provider oraoledb.oracle for linked server 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
Sql Server Oracle Linked Server Query Syntax
Management studio.I'm able to Ping the 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 oracle linked server equivalent 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 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\MSCommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on
Cannot Initialize The Data Source Object Of Ole Db Provider "oraoledb.oracle" For Linked Server
and reload this page. Please enter a title. You can not
Sql Server Linked Server Oracle Provider String
post a blank message. Please type your message and try again. More discussions in Oracle Provider for sql server linked server oracle access denied OLE DB All PlacesDatabaseDatabase Application DevelopmentWindows and .NETOracle Provider for OLE DB This discussion is archived 2 Replies Latest reply on Oct 10, 2012 1:55 AM by 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 967297 Steps to connect SQL Server to Oracle works but WHY ?? 888236 Sep 21, 2011 6:18 PM The following steps is what I have to do in order to get SQL Server to talk to an Oracle database. These steps, for me, work 100% of the time. Any deviation from the following steps will https://community.oracle.com/thread/2289794 result in failure. Rebooting the machine didn't make any difference between each of the steps. My question is why I have to install the client a second time? All the documentation that I have found at Oracle, Microsoft, or elsewhere state that I only need the InstantClient. ========================================= Some server I don't have access to with Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit installed Windows XP SP3 - 32bit with Microsoft SQL Server 2005 SP3 installed The following steps are done on the Windows XP machine. 1) install win32_11gR2_client Select: InstantClient (174.0MB) Path: C:\Oracle 2) install ODTwithODAC112021 Select: Oracle Data Access Components for Oracle Client Oracle Base: C:\Oracle Name: OraClient11g_home1 Path: C:\Oracle\product\11.2.0\client_1 3) Add entries for tnsnames.ora and sqlnet.ora 4) Ensure environmental variable ORACLE_HOME is set to C:\Oracle\product\11.2.0\client_1 5) SQL Server Management Studio Check "Allow Inprocess" for Oracle Provider OraOLEDB.Oracle Restart SQL Server Try to add link to Oracle - fails to connect "The test connection to the linked server failed." Addit
revised guide for connecting with SSIS using newer components. Unfortunately, setting up a linked server to Oracle is not as easy as it should be. In fact, you"ll have to install some software on your server http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/ to make things work. I"ve put together a short guide on connecting to Oracle from SQL Server. The process doesn"t take very long but there are several hoops to jump through. Install Oracle Database 10g Client Release 2 Install using the InstantClient option Install Oracle 10g Release 2 ODAC (64-bit, 32-bit) Select the Oracle Data Access Components option (not .NET!) Edit TNSNAMES.ora