Oracle Linked Server Error 7303
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
Sql Server 2012 Linked Server Oracle
2005 SP2 SQL Server > SQL Server Database Engine Question 0 Sign in microsoft sql server error 7303 linked server to vote Hi all,I'm not able to create a link server to access an Oracle DB from SQL server. My sql server oracle linked server query syntax 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
Cannot Initialize The Data Source Object Of Ole Db Provider "oraoledb.oracle" For Linked Server
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 server: sp_addlinkedserver 'LS_ora10g', 'Oracle','OraOLEDB.Oracle','ORA10G_NAME.COM'gosp_addlinkedsrvlogin 'LS_ora10g','False', NULL, 'user',
Oracle Linked Server Equivalent
'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\MSDTC\MTxOCI OracleOciLib = oci.dllOracleSqlLib = SQLLib80.dllOracleXaLib = xa80.dll May be this values shoudl pointCommunityOracle 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 and reload this page. Please enter a title. You cannot create an instance of ole db provider oraoledb.oracle for linked server can not post a blank message. Please type your message and try again.
Sp_helplinkedsrvlogin
More discussions in Oracle Provider for OLE DB All PlacesDatabaseDatabase Application DevelopmentWindows and .NETOracle Provider for OLE DB This cannot initialize the data source object of ole db provider "msdaora" for linked server discussion is archived 2 Replies Latest reply on Oct 10, 2012 1:55 AM by 967297 Steps to connect SQL Server to Oracle works but WHY ?? 888236 Sep 21, 2011 6:18 PM 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 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 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? https://community.oracle.com/thread/2289794 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." Additional information: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Cannot initialize the data source object of OLE DB Provider "OraOLEDB.Oracle" for linked server "ORACLEDB". OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLEDB" returned message "". (Microsoft SQL Server, Error: 7303) 6) install win32_11gR2_client Select: Administrator (1.02GB) Oracle Base: C:\Oracle Software Location: C:\Oracle\p
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 http://stackoverflow.com/questions/3444576/linking-oracle-with-sql-server 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 http://dba.stackexchange.com/questions/36687/cannot-initialize-the-data-source-object-of-ole-db-provider-microsoft-ace-oledb programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Linking Oracle with SQL Server up vote 5 down vote favorite 1 I'm trying to link SQL Server 2005 to an linked server Oracle 10g database. I've installed the Oracle client on the SQL server and validated that I can connect to the Oracle database using both tnsping and sqlplus. When I try to run a query in SQL Server I get the following: OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_CSSA2APD" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of oracle linked server OLE DB provider "OraOLEDB.Oracle" for linked server "ORA_CSSA2APD". Any ideas? I've tried both of the following queries with no luck: select * from openquery(ORA_CSSA2APD, 'select count(rowid) from eservice_op.agent') select count(rowid) from ORA_CSSA2APD..eservice_op.agent sql-server oracle ora-12154 share|improve this question edited Aug 13 '10 at 14:00 APC 87.3k1384184 asked Aug 9 '10 at 22:07 bokoxev 121117 add a comment| 5 Answers 5 active oldest votes up vote 3 down vote accepted I suspect an environment setting. That is, your session is picking up the TNSNAMES.ORA file but the session underlying SQL Server is not. I'd check were ORACLE_HOME and, possibly, TNS_ADMIN are being set and pointing to. Are you able to use the easy connect syntax for the database with the SQL Server connection . IE replace ORA_CSSA2APD with hostname:1521/service_name share|improve this answer answered Aug 11 '10 at 0:06 Gary Myers 28.8k33157 The environment variables for both are set and accurate. I haven't tried the connect syntax you outlined above, but I did try creating a UDL and copying that into the connection string with no luck. I'll try your syntax above and let you know the results. I appreciate it. –bokoxev Aug 11 '10 at 18:28 1 I restarted the service because I wasn't sure if I had since I set the TNS_ADMIN environment variable and e
log in tour help Tour Start 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 Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server up vote 4 down vote favorite This is going to be a long story I'm afraid ;-( I have to read some text-files into a SQL database. This database is on a SQL 2008 R2 x64 database server. I want to use a linked server so after some research I found that I had to install the Microsoft.ACE.OLEDB.12.0 x64 provider. I did this on my local machine and after I created my linked server I had some trouble reading text files. The provider for the linked server was set to use Microsoft.ACE.OLEDB.12.0, the datasource pointed to the correct folder and the providerstring was set to "TEXT", but I could not test the connection. It was throwing this error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXXXX". OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXX" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303) After some research, I found that the SQL Service was running under the NETWORKING SERVICE-account. When I changed this to Local Account, everything was working fine. Note: I also had to change some settings for the provider, "Allow inprocess" is checked, otherwise it was not possible to browse the files in the configure datasource folder. But now I try to implement this solution on my customers network and I cannot get it to work. It is also a SQL 2008 R2 x64 Db-server, Microsoft.ACE.OLEDB.12.0 provider is installed, but it still throwing the mentioned error. So I asked them which account is used to run the SQL Service. This is an AD-account belonging tot the local Administrator group of this server and therefore it had enough privileges as far as I can see. The user who is testing i