Run a SQL command in the event my connection is broken? (SQL Server)

Question

Here's the sequence of events my hypothetical program makes...

1. Open a connection to server.
2. Run an UPDATE command.
3. Go off and do something that might take a significant amount of time.
4. Run another UPDATE that reverses the change in step 2.
5. Close connection.

But oh-no! During step 3, the machine running this program literally exploded. Other machines querying the same database will now think that the exploded machine is still working and doing something.

What I'd like to do, is just as the connection is opened, but before any changes have been made, tell the server that should this connection close for whatever reason, to run some SQL. That way, I can be sure that if something goes wrong, the closing update will run.

(To pre-empt the answer, I'm not looking for table/record locks or transactions. I'm not doing resource claims here.)

Many thanks, billpg.

Answer 1

I'm not sure there's anything built in, so I think you'll have to do some bespoke stuff...

This is totally hypothetical and straight off the top of my head, but:

1. Take the SPID of the connection you opened and store it in some temp
   table, with the text of the reversal update.
2. Use an a background process (either SSIS or something else) to monitor the temp table and check that the SPID is still present as an open connection.
3. If the connection dies then the background process can execute the stored revert command
4. If the connection completes properly then the SPID can be removed from the temp table so that the background process no longer reverts it when the connection closes.

Comments or improvements welcome!