SQL Query TimeOut

Question

I have a vb.net program that runs a stored function and fills a dataset. However, due to the amount of information pulled, sometimes it times out on certain databases.

How can I increase the timeout of the query so as not to get hit with a timeout?

In my form button I have the following code that is NOT working (it still times out and the program errors)

Me.1TableAdapter.Connection.ConnectionString = "Data Source=10.0.1.1;Initial Catalog=Database;Persist Security Info=True;User ID=USER;Password=PASSWORD; Connection Timeout = 120"

Me.1TableAdapter.Fill(Me.Dataset.1, TodayDt, TodayEnd)

Me.2TableAdapter.Fill(Me.Dataset.1, TodayDt, TodayEnd)

I get the error message:

System.Data.SQLClient.SQLException: Timeout expired. The timeout period elapsed piror to the completion of the operation or the server is not responding.

check this [enter link description here][1] [1]: stackoverflow.com/questions/2748706/… — SP007
– SP007, Commented Feb 26, 2013 at 19:53

Eli Gassert · Accepted Answer · 2013-02-26 19:47:26Z

11

A connection has a timeout, but so does the command running against the connection. That timeout is for how long to wait just trying to establish the connection. See http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx

So assuming you're using a SqlCommand then set the CommandTimeout property of the command.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

answered Feb 26, 2013 at 19:47

Eli Gassert

9,7713 gold badges33 silver badges40 bronze badges

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

3 Comments

Nicholas Carey Over a year ago

Right. The default query timeout is 30 seconds IIRC. I find it annoying that there's no way to specify a default query timeout in the connect string proper: you need to add a key for it in your config file to override it in a way that doesn't require a new build. Also note: This property is the cumulative time-out (for all network packets that are read during the invocation of a method) for all network reads during command execution or processing of the results. A time-out can still occur after the first row is returned, and does not include user processing time, only network read time.

Shmewnix Over a year ago

I'm a little confused on this. I don't really know where to start... I'm assuming I need to increase the timeout of the tableadapter fills... but The above links confuse me.

Eli Gassert Over a year ago

See stackoverflow.com/questions/1192171/… and stackoverflow.com/questions/975898/…

Collectives™ on Stack Overflow

SQL Query TimeOut

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related