May 7, 2013 at 2:59 pm
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
May 7, 2013 at 3:13 pm
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
May 7, 2013 at 3:21 pm
Edit: Wrong Thread
May 7, 2013 at 5:22 pm
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.
May 8, 2013 at 1:09 am
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
May 8, 2013 at 9:40 am
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