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.
| Author |
Topic |
|
dcarva
Posting Yak Master
140 Posts |
 Posted - 2006-06-20 : 10:24:08
|
| Hello,I work on a product that executes a given number of stored procedures that run nightly maintenance tasks. For customers with large databases, these stored procedures can take up to 30 minutes. The command object that kicks off our stored procedures has a CommandTimeout value of 1 hour. For testing purposes, I have modified the stored procedure to run in a loop. When using SQL2000, I can clearly see that the stored proc times out in 1 hour as expected. However, the same code in SQL 2005 never times out. It runs forever. Any ideas why? I have tried 2 different SQL 2005 installations (default settings) and it never times out. ThanksDanny |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-20 : 12:55:32
|
| Are you using connection pooling?Peter LarssonHelsingborg, Sweden |
 |
|
|
dcarva
Posting Yak Master
140 Posts |
Posted - 2006-06-20 : 13:44:13
|
| In this particular case, I don't think so. We simply open the connection, call the store procedure, then close the connection. We are not making a lot of connections. This particular connection only occurs once every day. Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-20 : 13:56:09
|
| It's not SQL Server that cancels the query after it exceeds the CommandTimeout value. This value never makes it outside of the application. It's the application that cancels the query. So the problem is not from within SQL Server.SQL Server, regardless of the version, will allow you to run queries indefinitely. Tara Kizeraka tduggan |
|
|
|
dcarva
Posting Yak Master
140 Posts |
Posted - 2006-06-20 : 15:45:20
|
| The same code works differently on SQL 2000 than on SQL 2005. I tried a third SQL 2005 box today. Same thing. All of my SQL 2000 machines adhere to the commandtimeout. Not sure why the same code works differently on different versions of SQL. The OS is always Windows 2003.Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-20 : 15:47:59
|
| I'd suggest posting your question in a forum that supports the programming language that you are using. Perhaps you need to upgrade your driver, MDAC, or the .NET framework.Tara Kizeraka tduggan |
|
|
|
|
|
|
|
|
CommandTimeout not taking effect on SQL 2005