What happens to SP when VB times out?

We have a typical web application set up. ASP, with COM middle tier, and SQL Server 2000 database. Here's the question. What happens in the scenario where a stored procedure takes a long time to finish, and a VBScript timeout occurs? Even though the database-web connection is broken, will the stored procedure finish executing behind the scenes? If not, how does it know to stop?

Are there certain environmental parametes which could affect the behavior in this situation?

Thanks!

~H

The stored procedure will stop executing and if it was a part og transaction, all the changes will be rolled back. When a connection is broken, I guess Open Data Services component is notified at the server which does the clean up.

Refer to Inside SQL Server 2000 for more details

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

quote:Originally posted by LuisMartin

Check:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;287499

Thanks - but that article just explains how to set the timeout value. Also, we're not using COM transactions. I want to know what happens to stored procedure execution when called from VB via ADO and the VB connection times out. Thanks!

~H

As mentioned if the connection is broken due to a VB timeout any transactions will be rollbacked. If you are not using transactions and have say multiple update statments in the SP then you could end up with 3 out of 5 updates being performed. If you have multiple updates/inserts/deletes always use transactions. If it's a single update statment and the connection is broken and the update wasn't fisnished it will also be rollbacked.

quote:Originally posted by Argyle

As mentioned if the connection is broken due to a VB timeout any transactions will be rollbacked. If you are not using transactions and have say multiple update statments in the SP then you could end up with 3 out of 5 updates being performed. If you have multiple updates/inserts/deletes always use transactions. If it's a single update statment and the connection is broken and the update wasn't fisnished it will also be rollbacked.

Thanks for the reply. We have a lot of situations where VB calls a single stored procedure which then triggers off a series of steps all on the database. Almost all of the logic is in stored procedures and it doesn't come back to the VB until the process is complete.

These procs contain many inserts and updates - done in cursors, etc. In this case, if the VB times out, it sounds like you're saying that whatever process was going on in the database at that time will continue, but further calls will not execute. Am I understanding this correctly?

Thanks,
~H