Post reply

Linked server to Oracle timing out

  • August 27, 2020 at 8:41 pm

    Go to Answer

    #3784240

    Hello experts,

    I am trying to get a linked server to an Oracle database to work. But I don't know why it keeps hanging when I try to expand the Tables section, with this error:

    Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

    Here are some details:

    1. telnet works from the SQL Server to the Oracle server.
    2. tnsping works from the SQL Server to the Oracle server on non-SSL and SSL ports.
    3. This is the upcoming, upgraded version of a current SQL Server host where the linked server does not time out - and I essentially used the scripted linked server from the current host with the necessary modifications.

    My noob question is, if telnet and tnsping work, wouldn't that prevent any situation where there is a timeout? I mean, obviously not because it is happening. But I don't have enough networking knowledge to determine how to find the blocking point.

    I checked the SQL Server's network protocols in Configuration Manager, and all I see is the Named Pipes is Disabled, though not sure if that is even relevant. Named Pipes are enabled on the current server, so could that be it?

    If not, could someone kindly point me to some other methods that I might use to narrow down what is happening?

    Thanks for any help.

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • August 28, 2020 at 7:21 am

    #3784319

    Haven't really used TNSPing but looking at descriptions of it the utility just seems to verify connectivity to the Oracle db on the IP and port that Oracle is listening on; it doesn't seem to actually connect to the database using any credentials. Have you verified the Oracle username and password you are trying to use in the linked server configuration to make sure it is correct?

    Joie Andrew
    "Since 1982"

  • August 28, 2020 at 7:50 am

    Answer

    #3784321

    on the linked server provider and properties ensure that the property "allow inprocess" is set to true.

  • August 28, 2020 at 3:18 pm

    #3784411

    Many thanks for your responses.

    I marked Frederico's response as the answer because it resolved the timeout issue. But thanks to both of you for your advice.

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 4 posts - 1 through 3 (of 3 total)

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