Supress timeout for long call to a database.

[Lokean]

my vb.net program hits a SQL server database where the query can take as much as four hours to run.

Right now, it times out on the vb.net side after only 30 seconds.

my call to the database is through a tablehandler.

If V.sSerialNum Is Nothing Then
s.FillAllErrByModel(t, V.sStartDate, V.sEndDate, V.sModel, V.sProduct)

end if

I would much appreciate it if someone could tell me
1)What code to use to extend the timeout time to 4 hours
2)where to place it

thank you.

[Plater]

There is a timeout statement in your conenction string (well I think it's optional so you might not have it, check for it and change it)

But WOW 4 hours!! 40seconds is considered to be too ridiculously long and you need re-think how you do things. I cannot imagine waiting 4 hours.

[Lokean]

There is a timeout statement in your conenction string (well I think it's optional so you might not have it, check for it and change it)

But WOW 4 hours!! 40seconds is considered to be too ridiculously long and you need re-think how you do things. I cannot imagine waiting 4 hours.

Yeah,

I'm not the DBA, just the poor sod that has to hit the database...

I did a search through my project and didn't find the timeout statement.

Where would I insert it?

[Plater]

It goes in your SqlCommand.CommandTimeout

[nateraaaa]

The Command Timeout is calculated in seconds so if you need to assign the timeout to 4 hours you will need to do 60 seconds X 60 minutes X 4 to get the correct number of seconds to set the timeout for.

Nathan

[Lokean]

Thanks.

but now it's telling me that the commandtimeout property is "Read only"

How do I reset it?

[Plater]

Mine isn't read only...
what object are you using?
SqlCommand?
SqlDataAdapter?

[LacrosseB0ss]

I believe this property is in the actual database or application settings if I remember correctly. We were having issues with connections being opened and not closing and slowing down our servers. I want to say your .config file but I'm not sure if that's right.

The file I remember seeing was an XML file or something similar that showed all properties for everything (Apache, SqlServer, ASP, PHP, etc) and they all had different values. I think this is the file where you can change that if anyone knows what it's called.

As an aside, I'm with the other poster. 4 hours?!?! The default is a minute I think. And when I was working on the aforementioned (php) app the connections were alive on the server for 2 hours. At the beginning of the day everything was fine but around lunch it would timeout. 4 hours is a bit excessive and will lead to problems.

Hope this helps;
- LB

[Lokean]

Mine isn't read only...
what object are you using?
SqlCommand?
SqlDataAdapter?

Here's what I'm using

Dim t As New analyticsDataSet.dfGetErrors_TypeDataTable
Dim s As New analyticsDataSetTableAdapters.dfGetErrors_TypeTabl eAdapter

when I try:

s.Connection.ConnectionTimeout = 1500

I get Property 'ConnectionTimeout' is 'ReadOnly'.

[Plater]

Here's what I'm using

Dim t As New analyticsDataSet.dfGetErrors_TypeDataTable
Dim s As New analyticsDataSetTableAdapters.dfGetErrors_TypeTabl eAdapter

when I try:

s.Connection.ConnectionTimeout = 1500

I get Property 'ConnectionTimeout' is 'ReadOnly'.

CommandTimeout not ConnectionTimeout
(ConectionTimeout is set in connectionstring)
CommandTimeout is set in your SqlCommand (or SqlDataAdapter.SelectCommand) object