Problems with Linked Server

  • I am trying to create a Linked Server to an Oracle db.

    I have installed Oracle Database 10g Client Release 2 and then installed Oracle 10g Release 2 ODAC

    I changed the TNSNAMES.ora to this

    MyDatabase =

    (DESCRIPTION =

    (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = ServerName)(PORT = 1535))

    )

    (CONNECT_DATA =

    (SERVICE_NAME = MyDatabase )

    )

    )

    I then Rebooted the server and started to configure the Link Server using OraOLEDB.Oracle

    I did this below

    Linked Server - HSP

    Provider - Oracle Provider for OLE DB

    Product Name - Oracle

    Data Source - ServerName taken from the HOST in TNSNAMES.ora

    Under Security I chose Be made using this security context and entered the username and password

    When clicked ok it created a Linked Server, but when I ran SELECT TOP 10 * FROM HSP ..SYS.HELP to test the link it returned this error.

    OLE DB provider "OraOLEDB.Oracle" for linked server "HSP" 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 "HSP".

    I have tried to google it and have had no joy in getting it to work. How can I get it working?

  • No listener means that the Oracle server side could not be contacted from the client. Have you tested the connection via sqlplus or tnsping?

  • DNA_DBA (1/29/2010)


    No listener means that the Oracle server side could not be contacted from the client. Have you tested the connection via sqlplus or tnsping?

    No, Whats SQLPlus, how can I do this, also how can I tnsping?

  • if you've installed the oracle client you should have both tnsping and sqlplus.

    at a command prompt, type

    [font="Courier New"]tnsping MyDatabase[/font]

    this should return some info about the connection and show OK if it connected.

    then type

    [font="Courier New"]sqlplus s/s@MyDatabase [/font]

    this should return invalid username/password - this will show the client has tried to connect to the server.

    MyDatabase is the name you have set up in tnsnames.ora

  • DNA_DBA (1/29/2010)


    if you've installed the oracle client you should have both tnsping and sqlplus.

    at a command prompt, type

    [font="Courier New"]tnsping MyDatabase[/font]

    this should return some info about the connection and show OK if it connected.

    then type

    [font="Courier New"]sqlplus s/s@MyDatabase [/font]

    this should return invalid username/password - this will show the client has tried to connect to the server.

    MyDatabase is the name you have set up in tnsnames.ora

    Ok, this I tried that and this is my results

    C:\Documents and Settings\lislj>tnsping MyDatabase

    TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 01-FEB-2

    010 10:55:20

    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 = MyServerName)(PORT = 1535))) (CONNECT_DATA = (SERVICE_NAME = MyDatabase)))

    OK (0 msec)

    C:\Documents and Settings\lislj>sqlplus s/s@MyDatabase

    SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 1 10:56:45 2010

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

    ERROR:

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

    Enter user-name: MyUsername

    Enter password:

    ERROR:

    ORA-12560: TNS:protocol adapter error

    Enter user-name: MyUsername

    Enter password:

    ERROR:

    ORA-12560: TNS:protocol adapter error

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

    What does any of this mean?

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

    What does any of this mean?

    It means you are not able to connect to Oracle from the SQL Server machine. Check the TNSNAMES.ora file to make sure you have the right connection settings. Your Oracle DBA should be able to help you here.

  • Looks like you've got both Oracle 10g client and 11g client installed. The 10g client knows where to find the database but the 11g client doesn't - can you check the tnsnames.ora under where Oracle 11g is installed.

  • DNA_DBA (2/1/2010)


    Looks like you've got both Oracle 10g client and 11g client installed. The 10g client knows where to find the database but the 11g client doesn't - can you check the tnsnames.ora under where Oracle 11g is installed.

    I checked it under E:\app\lislj\product\11.1.0\client_1\Network\Admin and there was nothing there. I copied the tnsnames.ora & sqlnet.ora from the 10g and saved it here. I then tried tnsping MyDatabase & sqlplus s/s@MyDatabase and got the same results as before.

    Where can I go from here?

  • jez.lisle (2/1/2010)


    DNA_DBA (2/1/2010)


    Looks like you've got both Oracle 10g client and 11g client installed. The 10g client knows where to find the database but the 11g client doesn't - can you check the tnsnames.ora under where Oracle 11g is installed.

    I checked it under E:\app\lislj\product\11.1.0\client_1\Network\Admin and there was nothing there. I copied the tnsnames.ora & sqlnet.ora from the 10g and saved it here. I then tried tnsping MyDatabase & sqlplus s/s@MyDatabase and got the same results as before.

    Where can I go from here?

    Where can I go from here?

  • 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?

  • 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?

  • 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?

  • 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

  • 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.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply