Post reply

Linked server timeout within a query

  • January 11, 2017 at 11:15 am

    #317487

    I know I can find out the status of a linked server using sp_testlinkedserver. However the query takes quite sometime until it returns "Login timeout expired". Is it possible to change the timeout for a linked server query without changing the overall timeout setting of the linked server?

    I don't need to wait a long time only to find out if the linked server is down, but if it is down, I need to wait as long as it takes for the linked server to return the data I requested. As far as I'm concerned, I could create a view on the remote server to return GETDATE() and use the view in a four-part query between a try/catch to identify if the linked server is up or down, and process data accordingly. However I cannot wait for the query to timeout at the linked server setting.

  • January 13, 2017 at 2:40 am

    #1922504

    N_Muller - Wednesday, January 11, 2017 11:15 AM

    I know I can find out the status of a linked server using sp_testlinkedserver. However the query takes quite sometime until it returns "Login timeout expired". Is it possible to change the timeout for a linked server query without changing the overall timeout setting of the linked server? I don't need to wait a long time only to find out if the linked server is down, but if it is down, I need to wait as long as it takes for the linked server to return the data I requested. As far as I'm concerned, I could create a view on the remote server to return GETDATE() and use the view in a four-part query between a try/catch to identify if the linked server is up or down, and process data accordingly. However I cannot wait for the query to timeout at the linked server setting.

    use sp_configure , look after Connection Timeout here : https://technet.microsoft.com/en-us/library/ms186839(v=sql.105).aspx

  • January 13, 2017 at 3:01 am

    #1922508

    N_Muller - Wednesday, January 11, 2017 11:15 AM

    I don't need to wait a long time only to find out if the linked server is down, but if it is down, I need to wait as long as it takes for the linked server to return the data I requested.

    I think you mean " but if it isn't down", don't you?  Linked server objects have two timeout properties - Connection Timeout and Query Timeout.  Have you tried setting the Connection Timeout low and the Query Timeout higher?

    John

  • January 14, 2017 at 1:20 pm

    #1922755

    Thanks.  I had forgotten about the connection timeout.

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

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