January 11, 2017 at 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.
January 13, 2017 at 2:40 am
N_Muller - Wednesday, January 11, 2017 11:15 AMI 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
N_Muller - Wednesday, January 11, 2017 11:15 AMI 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
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