September 10, 2014 at 10:48 am
Hi all,
I have a list of dates I need to loop over and pass each one into a remote job.
Is it possible? How would you do it?
Something along these lines:
DECLARE date_curr CURSOR fast_forward FOR
select dt from missing_dates;
OPEN date_curr
FETCH NEXT FROM date_curr INTO @curr_date
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = '' + @SystemName + '.msdb..sp_start_job @job_name = ''' + @JobName + ''''-- @curr_date needs to go here
PRINT 'EXEC '+ @sql
-- EXEC (@sql)
FETCH NEXT FROM date_curr INTO @curr_date
END;
CLOSE date_curr;
DEALLOCATE date_curr;
Thanks,
September 17, 2014 at 10:01 am
SQL Server will only execute one instance of a job at a time, so the loop will try to execute the same job multiple times without waiting for the first one to finish. In this case, I'd create an SSIS package that contains a looping container and then inside of that place an Execute Package Task. Admittedly, I really like SSIS and so tend to go there first looking for solutions. I've also seen people write a script where they'd create a table of some sort called RUN_NEXT_JOB that has a flag in it that would get set inside of the SQL agent job you're trying to run. Then the loop clears it at the beginning of the next iteration. So something like the below only proper syntax and formatting (yes, I'm being lazy :P)...
while @@fetch_status = 0
BEGIN
while 1=1
begin
if (select runFlag from RUN_NEXT_JOB) = 1
waitfor delay "00:10:00" --(or however long you want to wait between checks to execute)
else
begin
update RUN_NEXT_JOB set runFlag = 0
sp_start_job @parameters --(inside of the job, the runFlag column is set to 1 at the end of the job)
break
end
end
fetch next from @cursor
END
If nothing else, that should give you some ideas.
-G
September 17, 2014 at 10:43 am
Thanks Greg. I am not familiar with SSIS ... yet. Will have to look into it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply