SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problems with Linked Server


Problems with Linked Server

Author
Message
J39L4753
J39L4753
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 382
DNA_DBA (2/2/2010)
hmmm.....
I just want to see if the client is connecting to the database as this will use the listener and prove the connection details are correct - can you start a command prompt, change to the 10g bin directory and run sqlplus from there?


Ok, this is the result doing samething on the 10g drive

E:\Oracle\product\10.2.0>tnsping MyDB

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-FEB-2
010 15:55:47

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
E:\oracle\product\10.2.0\db_1etwork\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = MyServer)(PORT = 1535))) (CONNECT_DATA = (SERVICE_NAME = EGR
TST4)))
OK (50 msec)

E:\Oracle\product\10.2.0>sqlplus s/s@MyDB

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Feb 3 15:56:41 2010

Copyright (c) 1982, 2008, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: usrname
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name: eg_sp
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

E:\Oracle\product\10.2.0>

What are your thoughts?
J39L4753
J39L4753
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 382
Just to help with things, this is the website I found some instructions from


http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/

J39L4753
J39L4753
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 382
I been reading the weblink again that I attached earlier, and realised that I had made an error and had previously installed 10g & 11g clients. I uninstalled it all and started again. This time I only have 10g installed.

I follwed the instructions and still in Managment Studio I cant connect to the Linked Server even though it is in the list. Then I tried the tnsping & sqlplus in Command Prompt. I kept getting the same errors as previous until I did a bit of research on the sqlplus. I then changed to sqlplus username/password@MyDB and I had success by connecting.

This below is the success of Command Prompt.

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\lislj>cd\

C:\>E:

E:\>cd E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN

E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>tnsping MyDB

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 04-FEB-2
010 10:39:21

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
E:\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 = MyServer)(PORT = 1535))) (CONNECT_DATA = (SERVICE_NAME = MyDB)))
OK (0 msec)

E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>sqlplus usrne/pwd@MYDB

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 4 10:39:52 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

How can I now connect through SQL Server Managment Studio?
dweil
dweil
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 3205
Could the path not be set up correctly?
Can you tnsping from c:\ or E:\?
Also try renaming sqlnet.ora.
Sqlnet.ora in not necessarily needed and it might just not be making it to tnsnames.ora.
From your post it looks like it is going to sqlnet.ora.
David Weil



J39L4753
J39L4753
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 382
dweil (2/4/2010)
Could the path not be set up correctly?
Can you tnsping from c:\ or E:\?
Also try renaming sqlnet.ora.
Sqlnet.ora in not necessarily needed and it might just not be making it to tnsnames.ora.
From your post it looks like it is going to sqlnet.ora.
David Weil


The path is set where the Oracle folder is and also where the tnsnames.ora is held

I have tried removing the sqlnet.ora and I end up with same results as I did in my last post

tnsping from the C:\ shows the results below

C:\Documents and Settings\lislj>tnsping MyDB

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 04-FEB-2
010 16:50:36

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
E:\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 = MyServer)(PORT = 1535))) (CONNECT_DATA = (SERVICE_NAME = MyDB)))
OK (0 msec)

C:\Documents and Settings\lislj>sqlplus usnme/pwd@MyDB

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 4 16:51:10 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name: usnme
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

C:\Documents and Settings\lislj>

on your post what do you mean, when you say

From your post it looks like it is going to sqlnet.ora.

dweil
dweil
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 3205
Try changing “HOST = xxxxxxxxx” to ”HOST = ip address”.
Oracle might not be resolving the host address correctly.
It that does not work you might try
1.renaming the tnsnames file,
2.make sure that tnsping does not work,
3.bring up Oracle Net manager and recreate the tnsnames file
4.try tnsping again
5.try sqlplus again
6. If sqlplus works try the linked server again

Sometimes Oracle is very sensitive to a space or tab in the wrong place in the tnsnames file.
Also there might be some control character in the tnsnames file that you do not see.
David Weil



J39L4753
J39L4753
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 382
dweil (2/4/2010)
Try changing “HOST = MyServer” to ”HOST = ip address”.
Oracle might not be resolving the host address correctly.
It that does not work you might try
1.renaming the tnsnames file,
2.make sure that tnsping does not work,
3.bring up Oracle Net manager and recreate the tnsnames file
4.try tnsping again
5.try sqlplus again
6. If sqlplus works try the linked server again

Sometimes Oracle is very sensitive to a space or tab in the wrong place in the tnsnames file.
Also there might be some control character in the tnsnames file that you do not see.
David Weil


I changed the Server to the IP Address and it worked fine as below

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\lislj>cd\

C:\>E:

E:\>cd E:\Oracle\product\10.2.0\client_1etwork\admin

E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>tnsping MyDB

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 04-FEB-2
010 18:11:51

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = IPAddress)(PORT = 1535))) (CONNECT_DATA = (SERVICE_NAME = MyDB)))
OK (30 msec)

E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>sqlplus usnme/pwd@MyDB

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 4 18:12:36 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

I tried it on the LinkedServer in Management Studio and it brought this error

OLE DB provider "OraOLEDB.Oracle" for linked server "MyLinkServer" returned message "ORA-12541: TNS:no listener".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "MyLinkServer".

I rebooted the Server before trying the LinkServer again.

How can it connect through sqlplus but not through Management Studio?
dweil
dweil
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 3205
I think you are getting closer now that you can connect via sqlplus.
Search the entire computer to see if there is more then one copy of tnsnames on it.

In SMSS, right click on the linked server
Click script linked server as
Click create to
Click new query editor window
You might get a hint from the sql to create the linked server
You might also want to post the sql without any confidential information hidden



J39L4753
J39L4753
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 382
dweil (2/4/2010)
I think you are getting closer now that you can connect via sqlplus.
Search the entire computer to see if there is more then one copy of tnsnames on it.

In SMSS, right click on the linked server
Click script linked server as
Click create to
Click new query editor window
You might get a hint from the sql to create the linked server
You might also want to post the sql without any confidential information hidden


Cool, Ill have a go at that now BigGrin

I forgot to change it, can you edit the HOST and put MyServer on your previous post please, it wont let me edit it :-)

Jez
J39L4753
J39L4753
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 382
jez.lisle (2/4/2010)
dweil (2/4/2010)
I think you are getting closer now that you can connect via sqlplus.
Search the entire computer to see if there is more then one copy of tnsnames on it.

In SMSS, right click on the linked server
Click script linked server as
Click create to
Click new query editor window
You might get a hint from the sql to create the linked server
You might also want to post the sql without any confidential information hidden


Cool, Ill have a go at that now BigGrin

I forgot to change it, can you edit the HOST and put MyServer on your previous post please, it wont let me edit it :-)

Jez


Ok, I did a search for all the tnsnames and found them in the folders below
E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN
E:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN

Should I delete or rename the one in db_1?

I also follow your instructions to create a LinkServer through SMSS I ended up with the same error,

OLE DB provider "OraOLEDB.Oracle" for linked server "SINGLEPLATFORM" returned message "ORA-12541: TNS:no listener".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SINGLEPLATFORM".

so I added the port onto the IP Address and tried again. I still got an error

OLE DB provider "OraOLEDB.Oracle" for linked server "HSP" returned message "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "HSP".

When I create my LinkServer name in SMSS can I use any name? and using OraOLEDB.Oracle is this correct?

dweil - can you edit the post you last wrote and over type the Server name. Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search