TRY...CATCH doesn't work when executing sp_stop_job in the try - by JR-J

Status : 

  Postponed<br /><br />
		Due to current priorities, the product team decided to postpone the resolution of this item.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 249878 Comments
Status Closed Workarounds
Type Bug Repros 5
Opened 1/4/2007 8:03:33 AM
Access Restriction Public


It would appear that this issue is due to the fact that sp_stop_job calls sp_sqlagent_notify which in turn calls an extended stored procedure - xp_sqlagent_notify.

All is well until this call is made.  I assume the execution context is lost at this point.

 I don't believe Books Online lists this as a restriction.

Sign in to post a comment.
Posted by visdtm on 3/4/2009 at 8:36 AM
This issue has *still* not been corrected in SQL 2008.

Even if you check to ensure that the job is running before you call "sp_stop_job", if the timing is right the job may stop just before the call to "sp_stop_job" is made. This is even more annoying if you are calling the script via "sqlcmd" and are using the "-b" option so that you can respond to script errors. There's no way to distinguish this bogus error reported by "sqlcmd" from any "real" errors that may occur.

Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to stop job MYJOB (from User MYDOMAIN\MYUSER) refused because the job is not currently running.
Posted by Microsoft on 1/4/2007 at 12:23 PM
Thanks for raising this issue. Although it does not make the bar for being included in SP2 at this time, we are tacking the issue for future releases of SQL Server.

Tomer Verona
SQL Server Development