June 13, 2011 at 10:28 am
I get to Nashville several times a year. Can I share in a couple?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 13, 2011 at 10:29 am
MyDoggieJessie (6/11/2011)
Using SQL 2008, SP2 assuming you replace "MyJob" with a valid SQL Agent Job on your server, how can you get around the fact that error # 22022 is not caught by the TRY...CATCH block?
BEGIN TRY
EXEC msdb.dbo.sp_start_job 'MyJob'
EXEC msdb.dbo.sp_start_job 'MyJob'
END TRY
BEGIN CATCH
PRINT ('Cannot start job')
END CATCH
Output:
Job 'MyJob' started successfully.
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Instance 6 (from User IMA\dork) refused because the job is already running from a request by User IMA\dork.
I see the same behavior.
I think the problem is that the error message is being raised in a proc that is called by sp_start_job or by a proc called at a lower level, but error doesn't "bubble up" to the calling level. I believe that the message may actually come from extended procedure master.dbo.xp_sqlagent_notify.
As a work around, you can check the return code from msdb.dbo.sp_start_job. It returns a 1 when you get this error.
DECLARE @RET1 INT
DECLARE @RET2 INT
BEGIN TRY
EXEC @RET1 = msdb.dbo.sp_start_job 'MyJob'
EXEC @RET2 = msdb.dbo.sp_start_job 'MyJob'
END TRY
BEGIN CATCH
PRINT ('Cannot start job')
END CATCH
SELECT RET1 = @RET1, RET2 = @RET2
Results:
Job 'MyJob' started successfully.
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job MyJob (from User IMA\dork) refused because the job already has a pending request from User IMA\dork.
RET1 RET2
----------- -----------
0 1
June 13, 2011 at 10:37 am
@Dixie
Absolutely! Some great pubs here in Nashville!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply