InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 07:37:54 GMT20http://www.sqlservercentral.com/Forums/Topic862065-146-1.aspxCreate the ODBC connection and then create linked Server with Microsoft OLE DB for ODBC driversWed, 08 Jun 2011 01:07:33 GMTSyed Jahanzaib Bin hassanhttp://www.sqlservercentral.com/Forums/Topic862065-146-1.aspxNo sure if this will help but we had a problem with a 64bit SQL server and Oracle support said to only install the 32bit client into the SQL server. It now works. For some reason the 64bit client does not work well with Windows 64 bit.Hope this helps,RudyTue, 07 Jun 2011 10:56:59 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic862065-146-1.aspx[quote][b]LOOKUP_BI-756009 (2/9/2010)[/b][hr]Ok I shall try and see if it connects thru BIDS[/quote]In a development box it is generally fixed by adding the TNSNAMES.ORA file in the Oracle home folder which I think you have done based on your original post. You should check the folder permissions to make sure the person in the TNSNAMES file is in the folder users list.Tue, 09 Feb 2010 09:51:52 GMTGift Peddiehttp://www.sqlservercentral.com/Forums/Topic862065-146-1.aspxOk I shall try and see if it connects thru BIDSTue, 09 Feb 2010 09:45:23 GMTLOOKUP_BI-756009http://www.sqlservercentral.com/Forums/Topic862065-146-1.aspxThe TNSNAMES.ORA error relates to the authenticated user permissions to the Oracle home folder. You could try connecting to oracle without linked server in BIDs and then try again. There is fixed way to fix it if I find it I will post it.Tue, 09 Feb 2010 09:41:18 GMTGift Peddiehttp://www.sqlservercentral.com/Forums/Topic862065-146-1.aspxIf I remove the 'Allow In Process' on OraOLEDB.Oracle provider.I get the following error when executing SELECT * FROM OPENQUERY(TestOraLink, 'select * from test_table2')[b]Error MSGOLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink" returned message "ORA-01017: invalid username/password; logon denied".Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink".[/b]If 'Allow In Process; is selected,I receive the following message[b]OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".[/b]Tue, 09 Feb 2010 09:38:40 GMTLOOKUP_BI-756009http://www.sqlservercentral.com/Forums/Topic862065-146-1.aspxAll,Please give me some tips or guidance on what can be done.ThanksTue, 09 Feb 2010 09:26:17 GMTLOOKUP_BI-756009http://www.sqlservercentral.com/Forums/Topic862065-146-1.aspxI have SQL 2005 on my system and would like to connect to [u]Oracle 10g[b][/b][/u] as a linked server.This is the error mssg Im getting[b]OLE 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".[/b]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 below[b]gp = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = cs.org)(PORT = 1526)) ) (CONNECT_DATA = (SERVICE_NAME = gp2) ))[/b]4) Executing tnsping gp returns [b]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)[/b]5) Execute sqlplus login/123@gp returns[b]SQL*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 options[/b]6) 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 ? ThanksMon, 08 Feb 2010 14:13:22 GMTLOOKUP_BI-756009