Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server connecting to Oracle problem Expand / Collapse
Author
Message
Posted Monday, February 8, 2010 2:13 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 10, 2014 2:54 PM
Points: 413, Visits: 1,356
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 getting
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 1
Cannot 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 machine
2) The C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN has 2 files [tnsnames] and [sqlnet]
3) Contents on tnsnames is as below
gp =
(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-2
010 16:02:10
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\client_1etwork\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = cs.org)(PORT = 1526))) (CONNECT_DATA = (SERVICE_NAME = gp2
01)))
OK (20 msec)


5) Execute sqlplus login/123@gp returns

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 8 16:04:35 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


6) In mngt studio, used following script to create linked server
EXEC sp_addlinkedserver @server = 'TestOraLink', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle', @datasrc = 'gp'

7) Followed by
EXEC 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 ? Thanks
Post #862065
Posted Tuesday, February 9, 2010 9:26 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 10, 2014 2:54 PM
Points: 413, Visits: 1,356
All,

Please give me some tips or guidance on what can be done.

Thanks
Post #862530
Posted Tuesday, February 9, 2010 9:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 10, 2014 2:54 PM
Points: 413, Visits: 1,356
If 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')

Error MSG
OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink".


If 'Allow In Process; is selected,I receive the following message
OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Post #862550
Posted Tuesday, February 9, 2010 9:41 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 13, 2014 2:52 PM
Points: 3,428, Visits: 14,451
The 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.



Kind regards,
Gift Peddie
Post #862553
Posted Tuesday, February 9, 2010 9:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 10, 2014 2:54 PM
Points: 413, Visits: 1,356
Ok I shall try and see if it connects thru BIDS
Post #862556
Posted Tuesday, February 9, 2010 9:51 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 13, 2014 2:52 PM
Points: 3,428, Visits: 14,451
LOOKUP_BI-756009 (2/9/2010)
Ok I shall try and see if it connects thru BIDS


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.



Kind regards,
Gift Peddie
Post #862561
Posted Tuesday, June 7, 2011 10:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
No 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,

Rudy



Post #1121198
Posted Wednesday, June 8, 2011 1:07 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 18, 2011 3:54 AM
Points: 492, Visits: 481
Create the ODBC connection and then create linked Server with Microsoft OLE DB for ODBC drivers

Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com
Post #1121544
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse