How to terminate a running stored procedure

minli98 wrote:
>
> I forgot to mention that I am on a shared hosting plan. Am I wrong to think
> that I don't have the access to kill errant sql jobs?
>


Very likely, but it would depend on your exact contract with your
hosting provider.

Also, if your provider is worth their contracts, they probably already
have processes in place to kill errant database processing. Otherwise
it would be very easy for users to kill each other affecting the hosting
provider's reputation with their clients.

Hi Dan,

Thanks for the comment. I am not so worried with running cfquery because I usually don't do any looping. Pretty much all cfquery I do is of select/update/insert variety. However, by migrating to stored procedures, I am hoping to move more of the data manipulation from coldfusion to t-sql.

I forgot to mention that I am on a shared hosting plan. Am I wrong to think that I don't have the access to kill errant sql jobs?

Thanks,
Min

Using cfquery presents the same problem as using stored procedures. What happens in both cases is.

Cold Fusion asks the db to do something.
The db starts doing it.
When the db finishes, it sends something back to Cold Fusion.

If the Cold Fusion template times out, the db does not stop what it was doing. If you want to stop it, you have to do so at the db level, not on Cold Fusion.

The way we do it on our data warehouse (redrick running on unix) is to schedule a job to run every 5 minutes. This job checks for any query being run by Cold Fusion that has been going longer than 120 seconds. If it finds any, it stops them and sends an email.