1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shmewnix
  • 1,553
  • 10
  • 32
  • 66
  • check this [enter link description here][1] [1]: http://stackoverflow.com/questions/2748706/connection-timeout-and-connection-lifetime – SP007 Feb 26 '13 at 19:53

1 Answers1

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

Eli Gassert
  • 9,745
  • 3
  • 30
  • 39
  • 3
    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.** – Nicholas Carey Feb 26 '13 at 19:54
  • 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. – Shmewnix Feb 27 '13 at 12:41
  • 1
    See http://stackoverflow.com/questions/1192171/how-can-i-change-table-adapters-command-time-out and http://stackoverflow.com/questions/975898/control-tableadapter-command-timeout-globally – Eli Gassert Feb 27 '13 at 12:52