sp_serveroption connection timeout

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sp_serveroption connection timeout

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-27 : 09:24:46

I'm inserting a very large table into a remote database so I need to increase the connection timeout. I've changed it to high values but after testing it looks like it times out after 60 seconds. So there must be a cap on timeouts. Where can I increase the cap?


EXEC sp_serveroption 
	 @server = 'ServerAliasName',
     @optname = 'connect timeout',
     @optvalue = '120';

Another question how does the query timeout differ from the connection timeout


EXEC sp_serveroption 
	 @server = 'ServerAliasName', 
     @optname = 'query timeout',     
     @optvalue = '120' ;

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-27 : 09:47:36

Just to add that if I reduce the timeout to say 2 seconds then it will timeout after about 2 secs. But entering 120 or 1000 seconds doesn't increase the connection more than 60 secs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 09:55:56

Connection timeout is the time SQL Server will spend trying to connect to the other server.
Query timeout is the time SQL Server will give any query to return from execution before killing the statement.

E 12°55'05.63"
N 56°04'39.26"

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-27 : 10:26:45

quote:
Originally posted by Peso

Connection timeout is the time SQL Server will spend trying to connect to the other server.
Query timeout is the time SQL Server will give any query to return from execution before killing the statement.

E 12°55'05.63"
N 56°04'39.26"

Yes,of course that makes sense. Anyway i tested with query timeout (setting up sqlcommand/sqlconnection in code) set to 2 seconds which took 25seconds to return an error then I changed it to 2000 seconds which returned an error after 65secs. Any ideas why I can't increase it over the 60sec limit?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-27 : 10:35:18

Can't you use Export/Import wizard or DTS/SSIS to do it?

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-27 : 10:43:46

quote:
Originally posted by insanepaul

quote:
Originally posted by Peso

Connection timeout is the time SQL Server will spend trying to connect to the other server.
Query timeout is the time SQL Server will give any query to return from execution before killing the statement.

E 12°55'05.63"
N 56°04'39.26"

Thanks for the help...I've increased the query time to 200secs and then set the sqlcommand variable to 200secs... command.CommandTimeout = 200; which worked.

Yes,of course that makes sense. Anyway i tested with query timeout (setting up sqlcommand/sqlconnection in code) set to 2 seconds which took 25seconds to return an error then I changed it to 2000 seconds which returned an error after 65secs. Any ideas why I can't increase it over the 60sec limit?

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-11-27 : 10:47:00

quote:
Originally posted by sodeep

Can't you use Export/Import wizard or DTS/SSIS to do it?

It's all done programmatically...the end user isn't technical. I found the problem. I setup data acces as usual -
sqlcommand command = new sqlcommand();

then i increased the timeout of the command variable:
command.Timeout = 200;

So this was what was capping the original timeout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources