How to Execute a Stored Procedure in a SQL Agent Job

How to start SQL Server job from a stored procedure?

You can execute the stored procedure sp_start_job in your stored procedure.

See here: http://msdn.microsoft.com/en-us/library/ms186757.aspx

Executing SQL Server Agent Job from a stored procedure and returning job result

You can run the query:

EXEC msdb.dbo.sp_help_jobhistory 
    @job_name = N'MonthlyData'

It'll return a column run_status. Statuses are:

 0 - Failed
 1 - Succeeded
 2 - Retry
 3 - Canceled         

More info on MSDN

EDIT: You might want to to poll your job and make sure it's executed. You can get this information from sp_help_job procedure. When this procedure returns status of 4 it means the job is idle.
Then it's safe to check for it's run status.

You can poll using following code:

DECLARE @job_status INT
SELECT @job_status = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;','exec msdb.dbo.sp_help_job @job_name = ''NightlyBackups''')

WHILE @job_status <> 4
BEGIN
    WAITFOR DELAY '00:00:03'
    SELECT @job_status = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;','exec msdb.dbo.sp_help_job @job_name = ''NightlyBackups''')
END

EXEC msdb.dbo.sp_help_jobhistory 
    @job_name = N'NightlyBackups' ;
GO

This code will check for the status, wait for 3 seconds and try again. Once we get status of 4 we know the job is done and it's safe to check for the job history.

Stored procedure runs when exec but doesn't run in agent job even though it's successful

I've added

    IF @@ROWCOUNT = 0
    PRINT 'Something went wrong!'
ELSE PRINT 'Rows were updated...'

On every Insert statement and can now easily view the output in the agent job history as following:

    Rows were updated... 
[SQLSTATE 01000] (Message 0)  Rows were updated... 
[SQLSTATE 01000] (Message 0)  Rows were updated... 
[SQLSTATE 01000] (Message 0).  The step succeeded.

Thanks @sepupic for the help

Stored procedure works when exec, fails in agent job

TSQL job steps run under the credentials of the Job owner. Here it is executing as NT AUTHORITY\SYSTEM. Update the job owner as sa or another user with right credentials.

Or, If possible or run the stored procedure under a specific credential as

CREATE PROCEDURE <procedure name>
EXECUTE AS OWNER 

or

CREATE PROCEDURE <procedure name>
EXECUTE AS 'specificUser'

Read more on Execute AS

---

Related Topics