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 ««123»»

Problems with Linked Server Expand / Collapse
Author
Message
Posted Wednesday, February 3, 2010 9:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
Points: 117, 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?
Post #858784
Posted Thursday, February 4, 2010 2:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
Points: 117, 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/
Post #859420
Posted Thursday, February 4, 2010 3:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
Points: 117, 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?
Post #859484
Posted Thursday, February 4, 2010 9:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 129, Visits: 2,370
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



Post #859746
Posted Thursday, February 4, 2010 10:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
Points: 117, 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.
Post #859776
Posted Thursday, February 4, 2010 10:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 129, Visits: 2,370
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



Post #859794
Posted Thursday, February 4, 2010 12:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
Points: 117, 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?
Post #859855
Posted Thursday, February 4, 2010 12:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 129, Visits: 2,370
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



Post #859888
Posted Thursday, February 4, 2010 1:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
Points: 117, 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 :D

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
Post #859917
Posted Thursday, February 4, 2010 3:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 14, 2014 3:20 AM
Points: 117, 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 :D

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
Post #860009
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse