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
------------------------------
Original Message:
Sent: 10-28-2019 10:43 AM
From: Mylene Chalut
Subject: Executing MS-SQL Agent Job and wait until the job has been finished
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
------------------------------