June 11, 2011 at 10:31 am
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.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 11, 2011 at 4:56 pm
BEGIN TRY
IF EXISTS(
select 1 FROM msdb.dbo.sysjobs_view sjv
join msdb.dbo.sysjobactivity sja
on sja.job_id = sjv.job_id
JOIN msdb.dbo.sysjobhistory sjh ON sja.job_history_id = sjh.instance_id
where name='test')
EXEC msdb.dbo.sp_start_job 'test'
END TRY
BEGIN CATCH
PRINT ('Cannot start job')
END CATCH
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 12, 2011 at 9:30 am
mister.magoo (6/11/2011)
BEGIN TRY
IF EXISTS(
select 1 FROM msdb.dbo.sysjobs_view sjv
join msdb.dbo.sysjobactivity sja
on sja.job_id = sjv.job_id
JOIN msdb.dbo.sysjobhistory sjh ON sja.job_history_id = sjh.instance_id
where name='test')
EXEC msdb.dbo.sp_start_job 'test'
END TRY
BEGIN CATCH
PRINT ('Cannot start job')
END CATCH
Nicely done, Magoo, but I don't believe (I could certainly be wrong) the CATCH code will ever run because the TRY code will never generate an error. Instead, I think we need more traditional IF/ELSE checking...
--===== If the job isn't already running, start the job.
-- Otherwise, raise an error indicating the job cannot start
-- and the reason why
IF EXISTS
(
SELECT 1
FROM msdb.dbo.sysjobs_view sjv
JOIN msdb.dbo.sysjobactivity sja ON sja.job_id = sjv.job_id
JOIN msdb.dbo.sysjobhistory sjh ON sja.job_history_id = sjh.instance_id
WHERE [name] ='test'
)
EXEC msdb.dbo.sp_start_job 'test';
ELSE RAISERROR('"Test" job is already running. Cannot start job at this time.',16,1);
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2011 at 9:44 am
No, you are right Jeff, I was leaving it to the OP to deal with that....
I would absolutely do it the way you so kindly offered.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 12, 2011 at 12:57 pm
Appreciate the input guys (sincerely) and it's immensely VALUED but this still doesn't resolve the error at hand:
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Instance 2 (from User IMA\dork) refused because the job is already running from a request by User IMA\dork.
It always run fine the first time, however, when the second instance runs a few milliseconds later you immediately get the error above.
Our reporting team has created this model (highly unscalable be that as it may) that uses rs.exe to render reports that either get written to shared directories, emailed, faxed, and etc. There are multiple jobs running that fetch records from the reporting tables to see which report is "next" to run, when it picks it up (like a reporting queue) it fires off builds the report, then does whatever action is necessary.
In this situation we have these jobs running simultaneously which are being told to run during each loop (fetching the next report), so if (and clearly when) the job is already running it throws this error.
Now, from my testing all that happens is this error...which doesn't stop anything from processing it just makes the job return as having been failed...and will run again the next time the main loop reiterates. I can live with this...
But, really want to learn/understand "WHY" a TRY/CATCH or IF/ELSE doesn't trap and handle the error appropriately. It's not like it's an error from one of the severity levels not handled by those commands.
Help?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 12, 2011 at 1:22 pm
MyDoggieJessie (6/12/2011)
Appreciate the input guys (sincerely) and it's immensely VALUED but this still doesn't resolve the error at hand:
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Instance 2 (from User IMA\dork) refused because the job is already running from a request by User IMA\dork.
It always run fine the first time, however, when the second instance runs a few milliseconds later you immediately get the error above.
Our reporting team has created this model (highly unscalable be that as it may) that uses rs.exe to render reports that either get written to shared directories, emailed, faxed, and etc. There are multiple jobs running that fetch records from the reporting tables to see which report is "next" to run, when it picks it up (like a reporting queue) it fires off builds the report, then does whatever action is necessary.
In this situation we have these jobs running simultaneously which are being told to run during each loop (fetching the next report), so if (and clearly when) the job is already running it throws this error.
Now, from my testing all that happens is this error...which doesn't stop anything from processing it just makes the job return as having been failed...and will run again the next time the main loop reiterates. I can live with this...
But, really want to learn/understand "WHY" a TRY/CATCH or IF/ELSE doesn't trap and handle the error appropriately. It's not like it's an error from one of the severity levels not handled by those commands.
Help?
First, please post the actual code that generated the error. Do not "paraphrase it". Thanks.
Second, I believe the reason why Try/Catch doesn't work here is because it's not an error in T-SQL... it's an SQL Server Agent error and it doesn't pass back an indication of error anymore than an external exe might.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2011 at 1:35 pm
Actual code (really not much different that already posted regards what I'm trying to accomplish but here it is:
DECLARE @Cnt int
DECLARE @job_id uniqueidentifier
DECLARE @job_1 varchar(100), @job_2 varchar(100), @job_3 varchar(100), @job_4 varchar(100), @job_5 varchar(100)
SELECT @job_1 = 'Instance 1', @job_2 = 'Instance 2', @job_3 = 'Instance 3', @job_4 = 'Instance 4', @job_5 = 'Instance 5'
/* Used to store the state of the existing jobs */
CREATE TABLE #enum_jobs (Job_ID uniqueidentifier, Last_Run_Date int, Last_Run_Time int, Next_Run_Date int, Next_Run_Time int,
Next_Run_Schedule_ID int, Requested_To_Run int, Request_Source int, Request_Source_ID varchar(100), Running int, Current_Step int,
Current_Retry_Attempt int, [State] int )
/* Determine how many reports need to be ran */
SELECT @Cnt = COUNT(*)
FROM dbo.CC_Report_Schedules WITH(NOLOCK)
WHERE CONVERT(VARCHAR(10),NextDateTime,121) <= CONVERT(varchar(10),GETDATE(),121)
AND Running = 0 AND Frequency NOT IN(2,99)
WHILE (@Cnt > 0 )
BEGIN
/* Populate table with current status */
SET @job_id = (SELECT dbo.fx_FetchSQLAgentJobID(@job_1))
INSERT INTO #enum_jobs
EXEC master.dbo.xp_sqlagent_enum_jobs 1,sa, @job_id
/* Execute avaliable jobs*/
IF ((select [State] from #enum_jobs WHERE job_id = dbo.fx_FetchSQLAgentJobID(@job_1)) = 4)
EXEC msdb.dbo.sp_start_job @job_1
SET @job_id = (SELECT dbo.fx_FetchSQLAgentJobID(@job_2))
INSERT INTO #enum_jobs
EXEC master.dbo.xp_sqlagent_enum_jobs 1,sa, @job_id
/* Execute avaliable jobs*/
IF ((select [State] from #enum_jobs WHERE job_id = dbo.fx_FetchSQLAgentJobID(@job_2)) = 4)
EXEC msdb.dbo.sp_start_job @job_2
SET @job_id = (SELECT dbo.fx_FetchSQLAgentJobID(@job_3))
INSERT INTO #enum_jobs
EXEC master.dbo.xp_sqlagent_enum_jobs 1,sa, @job_id
/* Execute avaliable jobs*/
IF ((select [State] from #enum_jobs WHERE job_id = dbo.fx_FetchSQLAgentJobID(@job_3)) = 4)
EXEC msdb.dbo.sp_start_job @job_3
SET @job_id = (SELECT dbo.fx_FetchSQLAgentJobID(@job_4))
INSERT INTO #enum_jobs
EXEC master.dbo.xp_sqlagent_enum_jobs 1,sa, @job_id
/* Execute avaliable jobs*/
IF ((select [State] from #enum_jobs WHERE job_id = dbo.fx_FetchSQLAgentJobID(@job_4)) = 4)
EXEC msdb.dbo.sp_start_job @job_4
SET @job_id = (SELECT dbo.fx_FetchSQLAgentJobID(@job_5))
INSERT INTO #enum_jobs
EXEC master.dbo.xp_sqlagent_enum_jobs 1,sa, @job_id
/* Execute avaliable jobs*/
IF ((select [State] from #enum_jobs WHERE job_id = dbo.fx_FetchSQLAgentJobID(@job_5)) = 4)
EXEC msdb.dbo.sp_start_job @job_5
TRUNCATE TABLE #Enum_jobs
SELECT @Cnt = COUNT(*)
FROM dbo.CC_Report_Schedules WITH(NOLOCK)
WHERE CONVERT(VARCHAR(10),NextDateTime,121) <= CONVERT(varchar(10),GETDATE(),121) AND Running = 0 AND Frequency NOT IN(2,99)
END
DROP TABLE #enum_jobs
Actual Errors:
(1 row(s) affected)
Job 'Instance 1' started successfully.
(1 row(s) affected)
Job 'Instance 2' started successfully.
(1 row(s) affected)
Job 'Instance 3' started successfully.
(1 row(s) affected)
Job 'Instance 4' started successfully.
(1 row(s) affected)
Job 'Instance 5' started successfully.
(1 row(s) affected)
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Instance 1 (from User IMA\Dork) refused because the job already has a pending request from User IMA\Dork.
(1 row(s) affected)
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Instance 2 (from User IMA\Dork) refused because the job already has a pending request from User IMA\Dork.
(1 row(s) affected)
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Instance 3 (from User IMA\Dork) refused because the job already has a pending request from User IMA\Dork.
(1 row(s) affected)
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Instance 4 (from User IMA\Dork) refused because the job already has a pending request from User IMA\Dork.
(1 row(s) affected)
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Instance 5 (from User IMA\Dork) refused because the job already has a pending request from User IMA\Dork.
(1 row(s) affected)
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Instance 1 (from User IMA\Dork) refused because the job already has a pending request from User IMA\Dork.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 12, 2011 at 5:41 pm
I suspect the TRY...CATCH doesn't work the way you expect because the TRY is actually succeeding.
Procedure dbo.sp_start_job runs twice, successfully sending start job requests over to SQLServerAgent which is a totally separate process, not at all like a nested sub procedure. The error is being returned by SQLServerAgent, which is outside the scope of your TRY...CATCH block.
__________________________________________________
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 12, 2011 at 9:57 pm
The Dixie Flatline (6/12/2011)
I suspect the TRY...CATCH doesn't work the way you expect because the TRY is actually succeeding.Procedure dbo.sp_start_job runs twice, successfully sending start job requests over to SQLServerAgent which is a totally separate process, not at all like a nested sub procedure. The error is being returned by SQLServerAgent, which is outside the scope of your TRY...CATCH block.
@MyDogJessie,
I know Dixie Flatline. He wouldn't say something unless he actually saw it. His comment tells me that you had TRY/CATCH in your code. Since I can find no TRY/CATCH in your code AND I see that you edited your entry AND I trust Dixie, I can only assume that you changed the code instead of leaving what you actually had and you did it without saying so.
That means I'm all done with this post because I'm too busy for those kinds of games. My recommendation is that you go back and look at the code I posted and implement it. Good luck with your problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2011 at 1:00 am
Good God Jeff. Of course there's TRY...CATCH blocks all over the code above. After all. THATS what I originally posted. There's no conspiracy here. And yes, I changed the code because THERE WERE TYPOS in it and it wouldn't run the way I had copied it in there.
I've valued your input in the past...not sure who peed in your corn flakes this morning but there's no foul play going on here in this post. I'm just a guy trying to figure out how to fix something to complete a project!!!
Considering you didn't like the original "test" code I had placed in the original post, I took the time to edit the post with a REAL example so you could run the code and see what I was seeing. I never did "paraphrase" anything. And this way you could see for yourself.
In any event thanks for your help earlier...with any luck someone else can help solve this problem...because I'm fresh out of ideas on this one.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 13, 2011 at 2:29 am
I take it that the code that calls the code you posted has the try ... catch block around the call?
I don't have an answer to why try...catch fails...previous answers sound right...
However, surely the problem to be solved is why you are trying to start the same job more than once in such a short timeframe?
If it were me, I would be looking at the code that decides when to call this code to see if that issue can be resolved.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 13, 2011 at 8:13 am
MyDoggieJessie (6/13/2011)
Good God Jeff. Of course there's TRY...CATCH blocks all over the code above. After all. THATS what I originally posted. There's no conspiracy here. And yes, I changed the code because THERE WERE TYPOS in it and it wouldn't run the way I had copied it in there.I've valued your input in the past...not sure who peed in your corn flakes this morning but there's no foul play going on here in this post. I'm just a guy trying to figure out how to fix something to complete a project!!!
Considering you didn't like the original "test" code I had placed in the original post, I took the time to edit the post with a REAL example so you could run the code and see what I was seeing. I never did "paraphrase" anything. And this way you could see for yourself.
In any event thanks for your help earlier...with any luck someone else can help solve this problem...because I'm fresh out of ideas on this one.
Then perhaps it's a "lingo" thing... here's the code you had posted...
DECLARE @Cnt int
DECLARE @job_id uniqueidentifier
DECLARE @job_1 varchar(100), @job_2 varchar(100), @job_3 varchar(100), @job_4 varchar(100), @job_5 varchar(100)
SELECT @job_1 = 'Instance 1', @job_2 = 'Instance 2', @job_3 = 'Instance 3', @job_4 = 'Instance 4', @job_5 = 'Instance 5'
/* Used to store the state of the existing jobs */
CREATE TABLE #enum_jobs (Job_ID uniqueidentifier, Last_Run_Date int, Last_Run_Time int, Next_Run_Date int, Next_Run_Time int,
Next_Run_Schedule_ID int, Requested_To_Run int, Request_Source int, Request_Source_ID varchar(100), Running int, Current_Step int,
Current_Retry_Attempt int, [State] int )
/* Determine how many reports need to be ran */
SELECT @Cnt = COUNT(*)
FROM dbo.CC_Report_Schedules WITH(NOLOCK)
WHERE CONVERT(VARCHAR(10),NextDateTime,121) <= CONVERT(varchar(10),GETDATE(),121)
AND Running = 0 AND Frequency NOT IN(2,99)
WHILE (@Cnt > 0 )
BEGIN
/* Populate table with current status */
SET @job_id = (SELECT dbo.fx_FetchSQLAgentJobID(@job_1))
INSERT INTO #enum_jobs
EXEC master.dbo.xp_sqlagent_enum_jobs 1,sa, @job_id
/* Execute avaliable jobs*/
IF ((select [State] from #enum_jobs WHERE job_id = dbo.fx_FetchSQLAgentJobID(@job_1)) = 4)
EXEC msdb.dbo.sp_start_job @job_1
SET @job_id = (SELECT dbo.fx_FetchSQLAgentJobID(@job_2))
INSERT INTO #enum_jobs
EXEC master.dbo.xp_sqlagent_enum_jobs 1,sa, @job_id
/* Execute avaliable jobs*/
IF ((select [State] from #enum_jobs WHERE job_id = dbo.fx_FetchSQLAgentJobID(@job_2)) = 4)
EXEC msdb.dbo.sp_start_job @job_2
SET @job_id = (SELECT dbo.fx_FetchSQLAgentJobID(@job_3))
INSERT INTO #enum_jobs
EXEC master.dbo.xp_sqlagent_enum_jobs 1,sa, @job_id
/* Execute avaliable jobs*/
IF ((select [State] from #enum_jobs WHERE job_id = dbo.fx_FetchSQLAgentJobID(@job_3)) = 4)
EXEC msdb.dbo.sp_start_job @job_3
SET @job_id = (SELECT dbo.fx_FetchSQLAgentJobID(@job_4))
INSERT INTO #enum_jobs
EXEC master.dbo.xp_sqlagent_enum_jobs 1,sa, @job_id
/* Execute avaliable jobs*/
IF ((select [State] from #enum_jobs WHERE job_id = dbo.fx_FetchSQLAgentJobID(@job_4)) = 4)
EXEC msdb.dbo.sp_start_job @job_4
SET @job_id = (SELECT dbo.fx_FetchSQLAgentJobID(@job_5))
INSERT INTO #enum_jobs
EXEC master.dbo.xp_sqlagent_enum_jobs 1,sa, @job_id
/* Execute avaliable jobs*/
IF ((select [State] from #enum_jobs WHERE job_id = dbo.fx_FetchSQLAgentJobID(@job_5)) = 4)
EXEC msdb.dbo.sp_start_job @job_5
TRUNCATE TABLE #Enum_jobs
SELECT @Cnt = COUNT(*)
FROM dbo.CC_Report_Schedules WITH(NOLOCK)
WHERE CONVERT(VARCHAR(10),NextDateTime,121) <= CONVERT(varchar(10),GETDATE(),121) AND Running = 0 AND Frequency NOT IN(2,99)
END
DROP TABLE #enum_jobs
I don't know about you but I can't find the words "TRY" or "CATCH" anywhere in that code. Are you calling the "IF" statements "TRY/CATCH"?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2011 at 9:07 am
Jeff, see the very first post
This was the code that I originally posted. Pretty simplistic. Later when you requested me not to paraphrase anything (which I don't get because the original post is what generates the error...no paraphrase, it's a direct copy), I took a snippet of the code and updated this entire post so any one could copy and paste it into Mgmt Studio and run it.
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.
Appreciate everyone's help on this.
I will go with the answers hinting that there's no error being generated in the code itself, rather that of the agent because it happens outside the scope of the TSQL, it's not caught by the TRY...CATCH or the IF...ELSE.
Thanks to everyone for the assistance!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 13, 2011 at 9:43 am
I guess this has all been a misunderstanding. So far as I'm concerned, we proved the original post wasn't going to do a thing for you. I posted an alternate to the first post based on Magoo's "IF" solution. I asked for the the actual code so I could whittle on that. That's the only code I care about now and it doesn't have Try/Catch in it. 😀 All the talk after that post about Try/Catch only served to confuse the situation and I've apparently taken it all wrong.
I'll give it (the post with the actual code) a whirl when I get home tonight.
Apologies for the confusion on this. Heh.. but I had help. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2011 at 10:12 am
No worries Jeff. I value your comments and a lot of times look to see your answers on others posts because you've always got something good to say! Thanks for sticking with me in this: If you're ever in the Nashville area, the beers are on me!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply