Automic Workload Automation

  • 1.  Executing MS-SQL Agent Job and wait until the job has been finished

    Posted 10-28-2019 10:44 AM
    Edited by Mylene Chalut 11-04-2019 08:20 AM
    Hey guys!

    I am trying to start a Sql Agent job at a specific step, and the OA function SQL_EXECUTE_JOB does not work with step's parameter.  I found information in this post (https://community.broadcom.com/enterprisesoftware/communities/community-home/digestviewer/viewthread?MessageKey=444d7af4-c7e1-4956-a0a6-e7304256aff5&CommunityKey=2e1b01c9-f310-4635-829f-aead2f6587c4&tab=digestviewer#bm444d7af4-c7e1-4956-a0a6-e7304256aff5), however, we don't have the permissions here to query the sys tables.

    Is there another way to do this that I don't know ? Your help would be very appreciated..

    #SqlAgentJobs #sqldatabase #SQL_execute_job

    Mylene

    ------------------------------
    Programmer-an
    Statistics Canada
    ------------------------------
    ​​​​​​​​​​​​​​​​


  • 2.  RE: Executing MS-SQL Agent Job and wait until the job has been finished
    Best Answer

    Posted 01-08-2020 11:02 AM
    Edited by Mylene Chalut 01-08-2020 11:02 AM

    Just in case somebody has the same issue, I asked the DBAs to create the views that I needed from the system tables and I created the following SQL stored proc :

    CREATE PROCEDURE [SurveyInterface].[uspStartSqlAgentJob]
    	@JobName VARCHAR(100),
    	@StepName VARCHAR(100)
    AS
    BEGIN
        SET NOCOUNT ON;
    	SET XACT_ABORT ON;
    	SET ANSI_WARNINGS OFF;
    
    	DECLARE @RC int,
                @status INT,
    	    @jobID VARCHAR(100),
    	    @status_message NVARCHAR(4000),
    	    @JobHistoryID INT,
    	    @TimeStamp DATETIME,
    	    @Message VARCHAR(max);
    
    	SET @TimeStamp = GETDATE()
    	PRINT @TimeStamp 
    	WAITFOR DELAY '00:00:30'; -- Let the time for the job to start
    
        -- Get the JobId 
        SELECT @jobID = job_id
        FROM   msdb.dbo.Jobs_View
        WHERE job_name = @JobName
    
        PRINT 'Job Id: ' + @jobID
    
    	IF (@jobId IS NOT NULL)
    
    		BEGIN
    			-- Take ownership of the SQLAgent job
    			EXEC msdb.dbo.TakeJobOwnership @JobName = @JobName;	
    
    			-- Execute the SQLAgent job
    			EXEC @RC =  msdb.dbo.sp_start_job @job_name = @JobName, @step_name = @StepName;
    
    			-- Wait until job Id status record added to Job Activity table (view)
    			WHILE (SELECT MAX(job_history_id) 
    					  FROM msdb.dbo.JobActivity_View 
    					  WHERE job_id= @jobID
    					  AND start_execution_date > @TimeStamp ) IS NULL
    			  BEGIN         
    				  PRINT 'SQLAgent job' + @JobName + ' still running...'
    				  WAITFOR DELAY '00:00:30';
    			  END
         
    			-- Get the Status when job completed     
    			SET @JobHistoryID = (SELECT MAX(job_history_id) 
    					       FROM msdb.dbo.JobActivity_View 
    					       WHERE job_id= @jobID)
    
    			SELECT @status = run_status
    				  FROM msdb.dbo.JobHistory_View
    				  WHERE job_id = @jobID
    				  AND job_history_id = @JobHistoryID
    
    			SELECT @status_message = message
    				  FROM msdb.dbo.JobHistory_View
    				  WHERE job_id = @jobID
    				  AND job_history_id = @JobHistoryID
      
    
    			SET @TimeStamp = GETDATE()
    			PRINT @TimeStamp   
    
    			IF @status = 1
    			   PRINT 'Status output:' + @status_message
    			ELSE
    			 BEGIN
    		        RAISERROR (@status_message, 16, 1)
    			 END
    	   END
        ELSE 
    	   BEGIN
    	       SET @message = 'The Sql Agent job ' + @JobName + ' does not exists.'
    		   RAISERROR (@message, 16, 1)
    	   END
    END
    GO
    


    For it to work, I had to create a JOBS.WIN to call it...

    @SET SERVER=&SERVER#
    @SET DATABASE=&DATABASE#
    @SET STEPNAME=&STEPNAME#
    
    @ECHO Server=%SERVER%
    @ECHO Database=%DATABASE%
    @ECHO StepName=%STEPNAME%
    
    @ECHO Job SUFTransferTablesAutomated to run on server %SERVER%
    
    ! *** Call stored proc to launch the SQL Agent Job ***
    @ECHO SQLCMD.EXE -S %SERVER% -d %DATABASE% -Q "EXEC SurveyInterface.uspStartSqlAgentJob @jobname=SUFTransferTablesAutomated, @stepname=%STEPNAME%" -b -s "|"
    SQLCMD.EXE -S %SERVER% -d %DATABASE% -Q "EXEC SurveyInterface.uspStartSqlAgentJob @jobname=SUFTransferTablesAutomated, @stepname=%STEPNAME%" -b -s "|"
    
    ! ***  Error handling section ***
    @:END
    SET RETCODE=%ERRORLEVEL%
    @ECHO %RETCODE%
    GOTO RETCODE
    @ECHO End of Program
    


    That's the only way I was able to do it. 



    ------------------------------
    Programmer-analyst
    Statistics Canada
    ------------------------------



  • 3.  RE: Executing MS-SQL Agent Job and wait until the job has been finished

    Posted 01-08-2020 12:49 PM
    I'm kinda rusty on this stuff, but I seem to remember we execute SQLServer packages with the EXEC instruction directly from our UC4 SQLServer jobs, and they seem to wait for completion.


    ------------------------------
    Pete
    ------------------------------



  • 4.  RE: Executing MS-SQL Agent Job and wait until the job has been finished

    Posted 01-08-2020 01:22 PM

    Well...  not for us! Not when you have to pass the SQL Agent step as a parameter :-)

    Otherwise, yes it does.



    ------------------------------
    Programmer-analyst
    Statistics Canada
    ------------------------------