BEGIN TRY - Catch not working

  • 1) Run job say TestJob

    2) Now execute below Begin try - catch for already running job and you will see below query error out

    any help?

    BEGIN TRY

    EXEC msdb.dbo.sp_start_job N'TestJob'

    END TRY

    BEGIN CATCH

    SELECT ERROR_MESSAGE()

    END CATCH

  • Could you please post the error message you get when the job is already running and the SQL attempts to start it again? Please be sure to post the entire message you get for SQL.

    Thanks

  • Edit: Wrong Thread

  • Msg 22022, Level 16, State 1, Line 0

    SQLServerAgent Error: Request to run job TestJob (from User TestDomain\testuser) refused because the job is already running from a request by User TestDomain\testuser.

  • I have hit this myself in the past. It is not so much the catch that does not work - that handles errors that are raised just fine. However sp_start_job appears to NOT actually return an error when it spits out the message saying the job is already running.

    You can test by just having a print @@error immediately after the call to sp_start_job, even if you get the message saying the job is running @@error will be zero. I suppose this is technically a bug in sp_start_job, but when I ran in to this I wanted to ignore the call if the job was already running so just ignoring the error was the right thing to do for me.

    Hope that helps

    Mike John

  • I think sp_start_job is one of the SPs which do not throw errors but return a status code instead.

    Try:

    DECLARE @RetVal int;

    EXEC @RetVal = msdb.dbo.sp_start_job N'TestJob';

    IF @RetVal > 0

    RAISERROR(N'TestJob failed to start!', 16, 1);

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply