February 8, 2010 at 2:13 pm
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
February 9, 2010 at 9:26 am
All,
Please give me some tips or guidance on what can be done.
Thanks
February 9, 2010 at 9:38 am
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".
February 9, 2010 at 9:41 am
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
February 9, 2010 at 9:45 am
Ok I shall try and see if it connects thru BIDS
February 9, 2010 at 9:51 am
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
June 7, 2011 at 10:57 am
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
Rudy
June 8, 2011 at 1:07 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply