sql: set connection timeout in the connection string

Question

In a console app I have a connection string like this:

Data Source=localhost;Initial Catalog=database_name;Integrated Security=True;Connect Timeout=100

I've read here that I need to add Connect Timeout to the connection string to set the timeout, but if I run a query that contains

WAITFOR DELAY '00:00:40';

I'll get a timeout exception, so it seems that the timeoput from the connection string is ignored. so how to set the timeout in the connection string (and not on the connection or command objects)?

How are you using this? Through a SqlConnection object? There is a Timeout property on that you can set instead.... — Milney
– Milney, Commented Apr 10, 2017 at 11:57
but I need to set the timeout only in the connection string, because this string will be used to create many connections... — Buda Gavril
– Buda Gavril, Commented Apr 10, 2017 at 11:58
the connect timeout in the string only affects how long it takes to CONNECT to the server, not to execute queries. The only way is to set it on the SqlConnection and SqlCommand object sorry — Milney
– Milney, Commented Apr 10, 2017 at 11:59

Damien_The_Unbeliever · Accepted Answer · 2017-04-10 11:59:05Z

6

There are two timeouts relating to SQL connections/commands - there is a connection timeout, that affects how long a connection is willing to wait when you try to open it, and a command timeout that affects how long an individual command being executed will wait.

You need to adjust the second of these - by e.g. setting the CommandTimeout property on the SqlCommand object.

answered Apr 10, 2017 at 11:59

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Buda Gavril Over a year ago

and is there a way to set the command timeout in the connection string or not?

rory.ap Over a year ago

I've never heard of that. But that doesn't mean there isn't. Didn't you say it worked (i.e. threw a timeout exception)?

Damien_The_Unbeliever Over a year ago

@BudaGavril - no, there is not. The default 30 seconds is not unreasonable. It should be an exceptional situation where you need to set a longer/infinite timeout.

Buda Gavril Over a year ago

yes, I am creating an index on a very big table (more than 4 giga is only the table)...

Damien_The_Unbeliever Over a year ago

@BudaGavril - yes, so you need it for that command, not for every command run against the connection.

Collectives™ on Stack Overflow

sql: set connection timeout in the connection string

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related