• Nice procedure. However, I'd use some sort of flagging instead of a GOTO loop. If you combine the SET assignment with a query, you can return the status with extra variables.

    eg:
    WHILE @myFlag = 0
    BEGIN
      -- check if job has completed
      SET @jobresult= ISNULL(SELECT b.last_run_outcome
        from msdb.dbo.sysjobservers b (nolock)
        where  b.job_id=@Jobid
        and convert(varchar(12),last_run_date,121)>=@lastrundate
        and last_run_time>=@lastruntime), -1) 
      IF @jobresult <> -1
        SET @myFlag = 1
    
    END

    Also, under what security context does this procedure get called, and how does that affect running the jobs?

     

    --------------------
    Colt 45 - the original point and click interface