Before making changes directly to a production database (inserts, updates, and deletes) I always start a transaction: "BEGIN TRAN".
If at any time during the change I see results which I didn't expect or if I accidentally run an update statement without a "WHERE"-clause ;-) I can simply rollback the changes and start over: "ROLLBACK TRAN".
Problem: After making some initial changes, I want to run an additional script to apply the next set of changes. I don't want to run the new script in the existing window because I want to preserve the result set from the first script. I can't open a new query window because it did not take part in the transaction which was started in the first query, so the query in the second window will be blocked until the first transaction is committed or rolled back.
What to do? Enter "sp_BindSession". By providing "sp_BindSession" with the appropriate token, I can "join" the transaction from the first query window. The required token can be retrieved from within the transaction by executing "sp_GetBindToken" (which returns a transaction ID). Neat, huh?
PS: A BIG THANKS to Paul Rony from SplendidCRM Software for answering my post on SQL Server Central with this info!
posted @ Tuesday, January 27, 2009 8:56 AM