+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

"Timeout expired" when querying linked server

  1. "Timeout expired" when querying linked server

    Hi all,
    I am working with SS 2000 and get the following error when querying linked
    server:
    Server" Description="Timeout expired"?>
    The strange thing is that 1 out fo 5 times runs OK. Remote Connection
    Timeout on linked server is set to 60 seconds and Remote Query Timeout is
    set to 600 seconds. When fails, the query runs for 45 seconds. When
    successful, the query runs for 30 second. Linked server is queried by a SQL
    job which runs a DTS package. SQL job and DTS are not on linked server.

    Any suggestions?

    TIA
    Goran



  2. Re: "Timeout expired" when querying linked server

    Goran Djuranovic (goran.djuranovic@newsgroups.nospam) writes:
    > I am working with SS 2000 and get the following error when querying linked
    > server:
    > > Server" Description="Timeout expired"?>
    > The strange thing is that 1 out fo 5 times runs OK. Remote Connection
    > Timeout on linked server is set to 60 seconds and Remote Query Timeout
    > is set to 600 seconds. When fails, the query runs for 45 seconds. When
    > successful, the query runs for 30 second. Linked server is queried by a
    > SQL job which runs a DTS package. SQL job and DTS are not on linked
    > server.
    >
    > Any suggestions?


    Hm, what about increasing the query timeout for the linked server? It's
    a little funny that it dies after 45 seconds, when you asked for 60, but
    it's not exact science.

    A more radical idea would be to look into improve the performance for the
    query, so that it runs faster.

    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx


  3. RE: "Timeout expired" when querying linked server

    Hello Goran

    Thank you for contacting Microsoft Online Community Support. It is
    Mark,again. I'm glad to assist you with the issue.

    For this case, you indicated that the lind is queried by a SQL job which
    runs a DTS package; and when the query runs for 45 seconds, a time out
    error happaned. Is it correct? If I misunderstand anything, please tell me
    directly. It
    will help us to resolve this issue quickly. Once the issue resolved, I'll
    appreciate your verification.

    Before we move on, please help to confirm your main concern.
    1) would you like us to help to reduce the time the query runs?

    2) Is your main concern is to fix the time our error and make the query run
    successfully even if it will run for 45 seconds.

    "Timeout expired" is usually the command timeout, it is related to the
    Connection Timeout, Query Timeout or some other setting.

    To anrrow down the issue, please help to answer the following questions.
    1) How long does the query usually run?

    2) Please describ the scenario in detail. For example: Is the linked server
    another SQL Server or other database server?
    how many computers are involved into the issue?

    3) Provide me the query you try to run when the error happan. If it is not
    convenient to you to public it here, please email me. My email address is
    v-fathan@online.microsoft.com(remove online)

    4) Tell me how did you create the DTS. How many tasks are created in it and
    what are those tasks?

    5) Since the issue is related to a linked server, we should verify what is
    the problematic server. It is best for us to use SQL Profile to confirm. If
    it is not convenient, please email me. I would like to share you some
    general steps and give you a tdf file.

    6) give me the linked server configuration information. Please run the
    following script and send the result with TXT format to me.
    a) connect to SQL Server by Management Studio and "Ctrl+T"
    b) run the script
    SET NOCOUNT ON
    GO
    PRINT ''
    PRINT '==== SELECT GETDATE()'
    SELECT GETDATE()
    PRINT ''
    PRINT ''
    PRINT '==== SELECT @@version'
    SELECT @@VERSION
    GO
    PRINT ''
    PRINT '==== SQL Server name'
    SELECT @@SERVERNAME
    GO
    PRINT ''
    PRINT '==== Host (client) machine name'
    SELECT HOST_NAME()
    GO
    PRINT ''
    PRINT '==== sp_configure advanced'
    EXEC sp_configure 'show advanced', 1
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sp_configure
    GO
    PRINT ''
    PRINT '==== Active Trace Flags'
    DBCC TRACESTATUS(-1)
    GO
    PRINT ''
    PRINT '==== sp_helpsort'
    EXEC sp_helpsort
    GO
    PRINT '======== SQL commandline args'
    EXEC master..xp_instance_regenumvalues 'HKEY_LOCAL_MACHINE',
    'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
    GO
    PRINT '======== Default client netlib and server aliases'
    EXEC master..xp_regenumvalues 'HKEY_LOCAL_MACHINE',
    'Software\Microsoft\MSSQLServer\Client\ConnectTo'
    EXEC master..xp_regenumvalues 'HKEY_LOCAL_MACHINE',
    'Software\Microsoft\MSSQLServer\Client\SuperSocketNetLib'
    GO
    PRINT '======== MDAC version information'
    EXEC master..xp_regenumvalues 'HKEY_LOCAL_MACHINE',
    'Software\Microsoft\DataAccess'
    GO
    PRINT ''
    PRINT '==== sp_helpserver'
    EXEC master..sp_helpserver
    GO
    PRINT ''
    PRINT '==== Linked server properties'
    PRINT ''
    PRINT '======== sp_helplinkedservers'
    EXEC master..sp_linkedservers
    PRINT ''
    PRINT '======== sp_helplinkedsrvlogin'
    EXEC master..sp_helplinkedsrvlogin
    PRINT ''
    PRINT '======== xp_enum_oledb_providers'
    EXEC master..xp_enum_oledb_providers
    PRINT ''
    PRINT '======== OLEDB provider SQL registry properties'
    DECLARE @sql70or80xp sysname
    IF CHARINDEX ('7.00.', @@VERSION) = 0
    SET @sql70or80xp = 'master..xp_instance_'
    ELSE
    SET @sql70or80xp = 'master..xp_'
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name like '#providers%')
    DROP TABLE #providers
    CREATE TABLE #providers
    (prov_name varchar(255), parse_name varchar(255), prov_descr text)
    INSERT INTO #providers
    EXEC master..xp_enum_oledb_providers
    DECLARE @prov_name varchar(255)
    DECLARE @regpath varchar(4000)
    DECLARE curs INSENSITIVE CURSOR
    FOR SELECT prov_name FROM #providers
    FOR READ ONLY
    OPEN curs
    FETCH NEXT FROM curs INTO @prov_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    PRINT ''
    PRINT '======== Registry properties for provider ' + @prov_name
    SET @regpath = 'Software\Microsoft\MSSQLServer\Providers\' + @prov_name
    EXEC ('EXEC ' + @sql70or80xp + 'regenumvalues ''HKEY_LOCAL_MACHINE'', '''
    + @regpath + '''')
    FETCH NEXT FROM curs INTO @prov_name
    END
    CLOSE curs
    DEALLOCATE curs
    GO
    PRINT '==== ODBC DSN info'
    PRINT 'EXEC master.dbo.xp_cmdshell ''regedit /e %tmp%\odbc_pss.txt
    HKEY_LOCAL_MACHINE\SOFTWARE\ODBC'''
    EXEC master.dbo.xp_cmdshell 'regedit /e %tmp%\odbc_pss.txt
    HKEY_LOCAL_MACHINE\SOFTWARE\ODBC'
    PRINT 'EXEC master.dbo.xp_cmdshell ''dir %tmp%\odbc_pss.txt '''
    EXEC master.dbo.xp_cmdshell 'dir %tmp%\odbc_pss.txt '
    PRINT 'EXEC master.dbo.xp_cmdshell ''type %tmp%\odbc_pss.txt'''
    EXEC master.dbo.xp_cmdshell 'type %tmp%\odbc_pss.txt'
    PRINT 'EXEC master.dbo.xp_cmdshell ''del %tmp%\odbc_pss.txt'''
    EXEC master.dbo.xp_cmdshell 'del %tmp%\odbc_pss.txt'
    GO
    PRINT ''
    PRINT '==== SELECT GETDATE()'
    SELECT GETDATE()

    If anything is unclear, please let me know.

    I look forward to your update.

    Thanks.

    Best regards,
    Mark Han
    Microsoft Online Community Support
    ===========================================================
    Delighting our customers is our #1 priority. We welcome your
    comments and suggestions about how we can improve the
    support we provide to you. Please feel free to let my manager
    know what you think of the level of service provided. You can
    send feedback directly to my manager at: msdnmg@microsoft.com.
    ===========================================================
    Get notification to my posts through email? Please refer to
    http://msdn.microsoft.com/subscripti...ult.aspx#notif
    ications.

    Note: The MSDN Managed Newsgroup support offering is for
    non-urgent issues where an initial response from the community
    or a Microsoft Support Engineer within 1 business day is acceptable.
    Please note that each follow up response may take approximately
    2 business days as the support professional working with you may
    need further investigation to reach the most efficient resolution.
    The offering is not appropriate for situations
    that require urgent, real-time or phone-based interactions or complex
    project analysis and dump analysis issues. Issues of this nature are best
    handled working with a dedicated Microsoft Support Engineer by
    contacting Microsoft Customer Support Services (CSS) at
    http://msdn.microsoft.com/subscripti...t/default.aspx.
    ============================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.
    =========================================================


  4. Re: "Timeout expired" when querying linked server

    Erland & Mark,
    Remote Query Timeout is already 600 seconds, so no need to increase it, I
    think. The funny thing is now I cannot get it to fail. I let it run
    scheduled and manual 20 times (10 each), and it runs fine. I haven't changed
    anything. I found that 90% of the time the process runs between 15-20
    seconds (not 30), when it runs successfully. So, I honestly think it could
    be a network issue. Well, I will update you with more details, if it fails
    again.

    Thanks for your help.
    Goran


    "Erland Sommarskog" wrote in message
    news:Xns9B1D2F0C16FEYazorman@127.0.0.1...
    > Goran Djuranovic (goran.djuranovic@newsgroups.nospam) writes:
    >> I am working with SS 2000 and get the following error when querying
    >> linked
    >> server:
    >> >>SQL
    >> Server" Description="Timeout expired"?>
    >> The strange thing is that 1 out fo 5 times runs OK. Remote Connection
    >> Timeout on linked server is set to 60 seconds and Remote Query Timeout
    >> is set to 600 seconds. When fails, the query runs for 45 seconds. When
    >> successful, the query runs for 30 second. Linked server is queried by a
    >> SQL job which runs a DTS package. SQL job and DTS are not on linked
    >> server.
    >>
    >> Any suggestions?

    >
    > Hm, what about increasing the query timeout for the linked server? It's
    > a little funny that it dies after 45 seconds, when you asked for 60, but
    > it's not exact science.
    >
    > A more radical idea would be to look into improve the performance for the
    > query, so that it runs faster.
    >
    > --
    > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    >
    > Links for SQL Server Books Online:
    > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    > SQL 2000:
    > http://www.microsoft.com/sql/prodinf...ons/books.mspx
    >




  5. Re: "Timeout expired" when querying linked server

    Hi Goran,

    I'm glad that the issue disappears now. Congratulation.

    If the original issue happens again, it is welcom to post it here again.

    If I can assit you anything related to the technical issue, please tell me.
    It is my pleasure to assist you.

    Best regards,
    Mark Han
    Microsoft Online Community Support
    =========================================================
    Delighting our customers is our #1 priority. We welcome your
    comments and suggestions about how we can improve the
    support we provide to you. Please feel free to let my manager
    know what you think of the level of service provided. You can
    send feedback directly to my manager at: msdnmg@microsoft.com.
    =========================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.
    =========================================================


  6. Re: "Timeout expired" when querying linked server

    Ok guys,
    It looks like the following could be the problem:
    0x80040E31L ----- DB_E_ABORTLIMITREACHED ----- Execution aborted because a
    resource limit has been reached; no results have been returned.

    What resources is it talking about? Server, TempDB, ...?

    Thanks
    Goran


    "Erland Sommarskog" wrote in message
    news:Xns9B1D2F0C16FEYazorman@127.0.0.1...
    > Goran Djuranovic (goran.djuranovic@newsgroups.nospam) writes:
    >> I am working with SS 2000 and get the following error when querying
    >> linked
    >> server:
    >> >>SQL
    >> Server" Description="Timeout expired"?>
    >> The strange thing is that 1 out fo 5 times runs OK. Remote Connection
    >> Timeout on linked server is set to 60 seconds and Remote Query Timeout
    >> is set to 600 seconds. When fails, the query runs for 45 seconds. When
    >> successful, the query runs for 30 second. Linked server is queried by a
    >> SQL job which runs a DTS package. SQL job and DTS are not on linked
    >> server.
    >>
    >> Any suggestions?

    >
    > Hm, what about increasing the query timeout for the linked server? It's
    > a little funny that it dies after 45 seconds, when you asked for 60, but
    > it's not exact science.
    >
    > A more radical idea would be to look into improve the performance for the
    > query, so that it runs faster.
    >
    > --
    > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    >
    > Links for SQL Server Books Online:
    > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    > SQL 2000:
    > http://www.microsoft.com/sql/prodinf...ons/books.mspx
    >




  7. Re: "Timeout expired" when querying linked server

    Goran Djuranovic (goran.djuranovic@newsgroups.nospam) writes:
    > Ok guys,
    > It looks like the following could be the problem:
    > 0x80040E31L ----- DB_E_ABORTLIMITREACHED ----- Execution aborted because a
    > resource limit has been reached; no results have been returned.
    >
    > What resources is it talking about? Server, TempDB, ...?


    I'm afraid that error code does not give that much more information. In
    the MDAC Books Online, I find this text for DB_E_ABORTLIMITREACHED for
    several functions:

    Execution has been aborted because a resource limit has been reached. For
    example, a query timed out. No results have been returned.

    The resource limit is something in the OLE DB provider, but it's surely
    the query timeout.

    Did you look into trying to improve the performance on the other end?



    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx


  8. Re: "Timeout expired" when querying linked server

    Well guys, I think I found what it is. I had to explicitly set CommandTimeout in my ActiveX scipt in DTS package like this: CommandObj.CommandTimeout = 600. I don't know if this is a bug or something, but it didn't care that I had it set on the server (Remote Query Timeout = 600), nor in the ConnectionStirng (CommandTimeout = 600). One of those things you would never think of. :)

    Thanks for your help again.

    Goran Djuranovic


    "Goran Djuranovic" wrote in message news:OxlcgY0HJHA.1160@TK2MSFTNGP04.phx.gbl...
    > Ok guys,
    > It looks like the following could be the problem:
    > 0x80040E31L ----- DB_E_ABORTLIMITREACHED ----- Execution aborted because a
    > resource limit has been reached; no results have been returned.
    >
    > What resources is it talking about? Server, TempDB, ...?
    >
    > Thanks
    > Goran
    >
    >
    > "Erland Sommarskog" wrote in message
    > news:Xns9B1D2F0C16FEYazorman@127.0.0.1...
    >> Goran Djuranovic (goran.djuranovic@newsgroups.nospam) writes:
    >>> I am working with SS 2000 and get the following error when querying
    >>> linked
    >>> server:
    >>> >>>SQL
    >>> Server" Description="Timeout expired"?>
    >>> The strange thing is that 1 out fo 5 times runs OK. Remote Connection
    >>> Timeout on linked server is set to 60 seconds and Remote Query Timeout
    >>> is set to 600 seconds. When fails, the query runs for 45 seconds. When
    >>> successful, the query runs for 30 second. Linked server is queried by a
    >>> SQL job which runs a DTS package. SQL job and DTS are not on linked
    >>> server.
    >>>
    >>> Any suggestions?

    >>
    >> Hm, what about increasing the query timeout for the linked server? It's
    >> a little funny that it dies after 45 seconds, when you asked for 60, but
    >> it's not exact science.
    >>
    >> A more radical idea would be to look into improve the performance for the
    >> query, so that it runs faster.
    >>
    >> --
    >> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    >>
    >> Links for SQL Server Books Online:
    >> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    >> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    >> SQL 2000:
    >> http://www.microsoft.com/sql/prodinf...ons/books.mspx
    >>

    >
    >


  9. Re: "Timeout expired" when querying linked server

    Hi Goran,

    Thank you for the sharing the resolution.

    To address your concern, I would like to explain the following
    " remote query timeout option to specify how long, in seconds, a remote
    operation can take before Microsoft SQL Server times out. The default is
    600, which allows a 10-minute
    wait.

    " CommandObj.CommandTimeout is : Gets or sets the wait time before
    terminating the attempt to execute a command and generating an error. There
    is an article to share with you:
    http://msdn.microsoft.com/en-us/libr...sqlcommand.com
    mandtimeout(VS.71).aspx

    " in the connection string, connection time out is : The length of time (in
    seconds) to wait for a connection to the server before terminating the
    attempt and generating an error. There is a link to share with you:
    http://msdn.microsoft.com/en-us/libr....sqlconnection.
    connectionstring.aspx

    If anything I can assist you, please post it here.

    Best regards,
    Mark Han
    Microsoft Online Community Support
    =========================================================
    Delighting our customers is our #1 priority. We welcome your
    comments and suggestions about how we can improve the
    support we provide to you. Please feel free to let my manager
    know what you think of the level of service provided. You can
    send feedback directly to my manager at: msdnmg@microsoft.com.
    =========================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.
    =========================================================


  10. Re: "Timeout expired" when querying linked server

    Just a little correction, the command object I used was
    Server.CreateObject("ADODB.command"), but the CommandTimeout property has
    the same description as the one of SqlCommand.

    Thanks
    Goran

    "Mark Han[MSFT]" wrote in message
    news:$quG0psIJHA.5824@TK2MSFTNGHUB02.phx.gbl...
    > Hi Goran,
    >
    > Thank you for the sharing the resolution.
    >
    > To address your concern, I would like to explain the following
    > " remote query timeout option to specify how long, in seconds, a remote
    > operation can take before Microsoft SQL Server times out. The default is
    > 600, which allows a 10-minute
    > wait.
    >
    > " CommandObj.CommandTimeout is : Gets or sets the wait time before
    > terminating the attempt to execute a command and generating an error.
    > There
    > is an article to share with you:
    > http://msdn.microsoft.com/en-us/libr...sqlcommand.com
    > mandtimeout(VS.71).aspx
    >
    > " in the connection string, connection time out is : The length of time
    > (in
    > seconds) to wait for a connection to the server before terminating the
    > attempt and generating an error. There is a link to share with you:
    > http://msdn.microsoft.com/en-us/libr....sqlconnection.
    > connectionstring.aspx
    >
    > If anything I can assist you, please post it here.
    >
    > Best regards,
    > Mark Han
    > Microsoft Online Community Support
    > =========================================================
    > Delighting our customers is our #1 priority. We welcome your
    > comments and suggestions about how we can improve the
    > support we provide to you. Please feel free to let my manager
    > know what you think of the level of service provided. You can
    > send feedback directly to my manager at: msdnmg@microsoft.com.
    > =========================================================
    > This posting is provided "AS IS" with no warranties, and confers no
    > rights.
    > =========================================================
    >




+ Reply to Thread
Page 1 of 2 1 2 LastLast