Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SQL SP which has a set a transaction. All the statements handled inside the TRY CATCH block. If we explicitly cancel the job by close the button in the JOb execution window, will it get into the catch block and automaticall rollback all the transaction tried in the TRY block ?

share|improve this question
add comment

migrated from stackoverflow.com Feb 27 '12 at 14:52

This question came from our site for professional and enthusiast programmers.

2 Answers

No, it probably won't. I can't say for sure, but client "aborts" normally don't rollback if the connection is returned to the connection pool

Add SET XACT_ABORT ON to your code or stored procedure

See (SO) http://stackoverflow.com/questions/917773/do-i-really-need-to-use-set-xact-abort-on/919279#919279 and SQL Server Transaction Timeout

share|improve this answer
add comment

I would say no. Assuming that pressing close button sends "stop notification" then here is what msdn says:
"When SQL Server Agent receives the stop notification, it waits for the job step that is currently running to finish before it processes the cancel request. Some long-running Transact-SQL statements such as BACKUP, RESTORE, and some DBCC commands can take a long time to finish."

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.