有沒有辦法多次執行同一個作業而不安排它？

我必須執行一個程序，它說：

   --first see what is the situation with the job
   EXEC msdb.dbo.sp_help_job @job_name=N'Create new gift vouchers'

   EXEC msdb.dbo.sp_start_job N'Create new gift vouchers' ; 
-- RUN THIS JOB FOR AS MANY TIMES AS THERE ARE INSERTS ABOVE --> 2 IN THIS CASE
   GO

這次我需要執行同一個作業（執行一個 SSIS 包，平均執行大約需要 8 分鐘）15 次！！！

有沒有辦法把一個腳本放在一起執行這個工作 15 次？

很長一段時間以來，我一直在使用以下儲存過程（我什至不記得我從哪裡得到程式碼）。它允許您啟動作業並等待作業完成，然後再將控制權返回給使用者。您可以在 tsql 腳本中堆疊 15 次此呼叫

CREATE procedure [dbo].[StartAgentJobAndWait](@job nvarchar(128), @maxwaitmins int = 5) --, @result int output)
as begin

set NOCOUNT ON;
set XACT_ABORT ON;

   BEGIN TRY

   declare @running as int
   declare @seccount as int
   declare @maxseccount as int
   declare @start_job as bigint
   declare @run_status as int

   set @start_job = cast(convert(varchar, getdate(), 112) as bigint) * 1000000 + datepart(hour, getdate()) * 10000 + datepart(minute, getdate()) * 100 + datepart(second, getdate())

   set @maxseccount = 60*@maxwaitmins
   set @seccount = 0
   set @running = 0

   declare @job_owner sysname
   declare @job_id UNIQUEIDENTIFIER

   set @job_owner = SUSER_SNAME()

   -- get job id
   select @job_id=job_id
   from msdb.dbo.sysjobs sj
   where sj.name=@job

   -- invalid job name then exit with an error
   if @job_id is null
       RAISERROR (N'Unknown job: %s.', 16, 1, @job)

   -- output from stored procedure xp_sqlagent_enum_jobs is captured in the following table
   declare @xp_results TABLE ( job_id                UNIQUEIDENTIFIER NOT NULL,
                               last_run_date         INT              NOT NULL,
                               last_run_time         INT              NOT NULL,
                               next_run_date         INT              NOT NULL,
                               next_run_time         INT              NOT NULL,
                               next_run_schedule_id  INT              NOT NULL,
                               requested_to_run      INT              NOT NULL, -- BOOL
                               request_source        INT              NOT NULL,
                               request_source_id     sysname          COLLATE database_default NULL,
                               running               INT              NOT NULL, -- BOOL
                               current_step          INT              NOT NULL,
                               current_retry_attempt INT              NOT NULL,
                               job_state             INT              NOT NULL)

   -- start the job
   declare @r as int
   exec @r = msdb..sp_start_job @job

   -- quit if unable to start
   if @r<>0
       RAISERROR (N'Could not start job: %s.', 16, 2, @job)

   -- start with an initial delay to allow the job to appear in the job list (maybe I am missing something ?)
   WAITFOR DELAY '0:0:01';
   set @seccount = 1

   -- check job run state
   insert into @xp_results
   execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

   set @running= (SELECT top 1 running from @xp_results)

   while @running<>0 and @seccount < @maxseccount
   begin
       WAITFOR DELAY '0:0:01';
       set @seccount = @seccount + 1

       delete from @xp_results

       insert into @xp_results
       execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

       set @running= (SELECT top 1 running from @xp_results)
   end

   -- result: not ok (=1) if still running

   if @running <> 0 begin
       -- still running
       return 0
   end
   else begin

       -- did it finish ok ?
       set @run_status = 0

       select @run_status=run_status
       from msdb.dbo.sysjobhistory
       where job_id=@job_id
         and cast(run_date as bigint) * 1000000 + run_time >= @start_job

       if @run_status=1
           return 1  --finished ok
       else  --error
           RAISERROR (N'job %s did not finish successfully.', 16, 2, @job)

   end

   END TRY
   BEGIN CATCH

   DECLARE
       @ErrorMessage    NVARCHAR(4000),
       @ErrorNumber     INT,
       @ErrorSeverity   INT,
       @ErrorState      INT,
       @ErrorLine       INT,
       @ErrorProcedure  NVARCHAR(200);

   SELECT
       @ErrorNumber = ERROR_NUMBER(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorLine = ERROR_LINE(),
       @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

   SELECT @ErrorMessage =
       N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
           'Message: '+ ERROR_MESSAGE();

   RAISERROR
       (
       @ErrorMessage,
       @ErrorSeverity,
       1,
       @ErrorNumber,    -- original error number.
       @ErrorSeverity,  -- original error severity.
       @ErrorState,     -- original error state.
       @ErrorProcedure, -- original error procedure name.
       @ErrorLine       -- original error line number.
       );

   END CATCH

end


GO

引用自：https://dba.stackexchange.com/questions/148556