MSSQL: Why does CommandTimeout=0 NOT mean infinity?

kamiller42 · Post by **kamiller42** » Wed 02 Mar 2011 16:52

I have a couple of stored procedures the users can kick off at different times. These procedures are very process intensive and can easily take over 30 minutes to run.

I am using TUniStoredProc with CommandTimeout=0 in the SpecificOptions property. Yet, around 30 minutes, the component throws a
"Lock time out period exceeded." The procedures run fine in a database tool. They take a while, but never fail.

The database is SQL Server 2005. Is there another option I should be looking at to make sure this action never times out?

kamiller42 · Post by **kamiller42** » Wed 02 Mar 2011 17:29

Some additional information, LockMode is set to None. The procedure does not return a result set. It performs a lot of data crunching and then returns to the user.

I have a TUniTransaction on the main data module along with the connection component. The DefaultConnection is set to the database component. The trans isolation level is set to ReadUncommitted. The transaction is not assigned to the stored procedure component. I assume the transaction being assigned to the connection component defines the default transaction parameters for operation conducted using the database component.

AndreyZ · Post by **AndreyZ** » Thu 03 Mar 2011 11:40

Hello,

This problem is connected with the specificity of SQL Server work. To avoid the problem, you should increase the TUniConnection.SpecificOptions.LockTimeout option value.

kamiller42 · Post by **kamiller42** » Tue 08 Mar 2011 16:03

This appears to be working. I ran a stored procedure for 48 minutes without a timeout error. Thank you!

AndreyZ · Post by **AndreyZ** » Wed 09 Mar 2011 08:09

It is good to see that this problem was solved. If any other questions come up, please contact us.