SQL Server 7 stored procedure won't time out using command object
Posted on 2002-05-29
I am baffled on this one...
We have a VB 6 component running in MTS. I have set the commandtimeout on the command to be 90 seconds. Occasionally on one stored procedure call, we get a timeout error, which is fine (this stored procedure can take awhile to run).
However, sometimes the stored procedure NEVER times out. Our custom logging shows it will hang inside the stored procedure call indefinitely. It will NOT stop by itself, I have to kill the process and shut down the package in MTS.
When this happens, the CPU percentage on the application server running the component (not the database server) goes up to 100% until I kill the task/shut down the package.
I don't think this is a data or stored proc problem. The error that gets logged in the event viewer of Windows NT is "One or more errors occurred during processing of command." The source on the error is not SQL Server, it is the component. Also, this stored procedure gets called 2000+ times per day without incident.