January 27, 2011 at 4:25 am
hi All,
I am getting following error. How to handle this error.
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to stop job Test_sample_job_SSIS (from User sa) refused because the job is not currently running.
DECLARE
@ErrorNumber INT
,@ErrorSeverity INT
,@ErrorState INT
,@ErrorMessage AS NVARCHAR(2050)
BEGIN TRY
Exec msdb..sp_stop_job Test_sample_job_SSIS'
END TRY
BEGIN CATCH
SELECT
@ErrorNumber=ERROR_NUMBER()
,@ErrorSeverity=ERROR_SEVERITY()
,@ErrorState=ERROR_STATE()
,@ErrorMessage=ERROR_MESSAGE()
IF @ErrorNumber=22022 or @ErrorMessage like '%refused because the job is not currently running.'
Exec msdb..sp_start_job 'Test_sample_job_SSIS '
END CATCH
######################
January 27, 2011 at 6:04 am
your sp_stop_job procedure ends normally.
So, your next statement is still executed in the "begin try" part.
Declare @rc int
BEGIN TRY
print 'start'
Exec @rc=msdb..sp_stop_job 'yourjob'
if @rc=1 -- means sproc failed
begin
Exec @rc=msdb..sp_start_job 'yourjob'
end
END TRY
begin catch
....
end catch
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 27, 2011 at 6:22 am
Thanks...
How to handle the following error while i am going to start a job?
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job testing_sample_SSIS (from User sa) refused because the job is already running from a request by User sa.
Actual problem is my job is in continuos loop. So when it comes to Sp_start_job its throwing the above error.
Please look into the code:
Declare @status INT, @jobID uniqueidentifier,@mon int
Declare @rc int
Declare @cnt INT
Select @jobID=job_id from sysjobs where name ='testing_sample_ssis'
print 'start'
Exec msdb..sp_start_job 'testing_sample_ssis'
InLoop:
SET @cnt=0
SET @mon=DATEPART(month,getdate())
SET @status=(select top 1 run_status from msdb..sysjobhistory where job_id=@jobID
AND run_date=CAST(DATEPART(yyyy,GETDATE())AS VARCHAR(4))
+(CASE WHEN @mon<10 then '0'+cast(@mon as VARCHAr(1))Else CAST(@mon as varchar(2)) END)
+CAST(DATEPART(Day,GETDATE()) AS VArchar(2))
AND step_name='(Job outcome)'
order by instance_id desc
)
PRINT @status
Print 'Hi-1'
IF @status =1 or @status IS NULL
BEGIN
Exec msdb..sp_start_job 'testing_sample_ssis'
SET @cnt=0
WHILE @cnt<=1000
BEGIN
SET @cnt=@cnt+1
END
END
SET @status=(select top 1 run_status from msdb..sysjobhistory where job_id=@jobID
AND run_date=CAST(DATEPART(yyyy,GETDATE())AS VARCHAR(4))
+(CASE WHEN @mon<10 then '0'+cast(@mon as VARCHAr(1))Else CAST(@mon as varchar(2)) END)
+CAST(DATEPART(Day,GETDATE()) AS VArchar(2))
AND step_name='(Job outcome)'
order by instance_id desc
)
PRINT @status
Print 'Hi-2'
IF @status in (2,3)
BEGIN
Exec msdb..sp_stop_job 'testing_sample_ssis'
SET @cnt=0
WHILE @cnt<=1000
BEGIN
SET @cnt=@cnt+1
END
END
ELSE
BEGIN
SET @cnt=0
WHILE @cnt<=1000
BEGIN
SET @cnt=@cnt+1
END
END
GOTO Inloop
January 27, 2011 at 9:19 am
How about:
Declare @status INT
, @jobID uniqueidentifier
, @mon int
Declare @rc int
Declare @cnt INT
Select @jobID = job_id
from sysjobs
where name = 'testing_sample_ssis'
print 'start'
Exec msdb..sp_start_job 'testing_sample_ssis'
InLoop:
SET @cnt = 0
SET @mon = DATEPART(month, getdate())
SET @status = (
select top 1
run_status
from msdb..sysjobhistory H
inner join ( select max(instance_id) max_instance_id
from msdb..sysjobhistory
where job_id = @jobID
) Inst
on Inst.max_instance_id = H.instance_id
where job_id = @jobID
AND run_date = CAST(DATEPART(yyyy, GETDATE()) AS VARCHAR(4)) + ( CASE WHEN @mon < 10 then '0' + cast(@mon as VARCHAr(1))
Else CAST(@mon as varchar(2))
END ) + CAST(DATEPART(Day, GETDATE()) AS VArchar(2))
AND step_name = '(Job outcome)'
order by instance_id desc
)
PRINT @status
Print 'Hi-1'
IF @status = 1
or @status IS NULL
BEGIN
Exec msdb..sp_start_job 'testing_sample_ssis'
SET @cnt = 0
WHILE @cnt <= 1000
BEGIN
SET @cnt = @cnt + 1
END
END
SET @status = (
select top 1
H.run_status
from msdb..sysjobhistory H
inner join ( select max(instance_id) max_instance_id
from msdb..sysjobhistory
where job_id = @jobID
) Inst
on Inst.max_instance_id = H.instance_id
where job_id = @jobID
AND run_date = CAST(DATEPART(yyyy, GETDATE()) AS VARCHAR(4)) + ( CASE WHEN @mon < 10 then '0' + cast(@mon as VARCHAr(1))
Else CAST(@mon as varchar(2))
END ) + CAST(DATEPART(Day, GETDATE()) AS VArchar(2))
AND step_name = '(Job outcome)'
order by instance_id desc
)
PRINT @status
Print 'Hi-2'
IF @status in ( 2, 3 )
BEGIN
Exec msdb..sp_stop_job 'testing_sample_ssis'
SET @cnt = 0
WHILE @cnt <= 1000
BEGIN
SET @cnt = @cnt + 1
END
END
ELSE
BEGIN
SET @cnt = 0
WHILE @cnt <= 1000
BEGIN
SET @cnt = @cnt + 1
END
END
GOTO Inloop
btw you are looping a count til 1000.
That won't take long enough to do anything.
If you want to wait a couple of seconds, use waitfor.
Why don't you just schedule your job to run every minute.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply