set timeout in openrowset?

• nzhang6666

  SSCrazy

  Points: 2217

  More actions

  August 7, 2012 at 9:33 am

  #265556

  Hi all,

  Is there a way to set timeout in openrowset? My project requires to connect from a sql server 2005 server to 150 databases (sql server 2000) and run some query there. Some connections may be broken. The broken connection may take 40s to timeout, the query only runs about 2 seconds. So I want to set a timeout in openrowset so the overall run time can be reduced.

  Thanks.

• nzhang6666

  SSCrazy

  Points: 2217

  More actions

  August 8, 2012 at 2:09 am

  #1522993

  so there is no way?

• nzhang6666

  SSCrazy

  Points: 2217

  More actions

  August 8, 2012 at 9:19 am

  #1523189

  It seems trying to add timeout in openrowset is not doable. The workaround is to try to ping to the server use xp cmdshell 'ping -n 2 -w 3000 ip' , -n means number of retries (default is 4), -w means timeout for each try (default may be 4 seconds).