Menu
How can I create a stored procedure to start a SQL Server job?
501
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. Expand SQL Server Agent. Right-click Job Activity Monitor and click View Job Activity. In the Job Activity Monitor, you can view details about each job that is defined for this server.
Expand SQL Server Agent, expand Jobs, right-click the job that you want to schedule, and click Properties. Select the Schedules page, and then click Pick. Select the schedule that you want to attach, and then click OK.
Expand the SQL Server Agent and right click on Jobs and click on New Job… In General tab, Enter job name, owner, category and description. In Steps tab, click New and enter step name, select Type as Transact-SQL script (T-SQL) and select database and put EXEC procedure name in command area.
-- Create SQL Server Agent job start stored procedure with input parameter
CREATE PROC uspStartMyJob @MyJobName sysname
AS
DECLARE @ReturnCode tinyint -- 0 (success) or 1 (failure)
EXEC @ReturnCode=msdb.dbo.sp_start_job @job_name=@MyJobName;
RETURN (@ReturnCode)
GO
OR with no parameter:
-- Create stored procedure to start SQL Server Agent job
CREATE PROC StartMyMonthlyInventoryJob
AS
EXEC msdb.dbo.sp_start_job N'Monthly Inventory Processing';
GO
-- Execute t-sql stored procedure
EXEC StartMyMonthlyInventoryJob
EDIT FYI: You can use this PRIOR to starting IF you don't want to start the job IF it is running currently, work this in your stored proc:
-- Get run status of a job
-- version for SQL Server 2008 T-SQL - Running = 1 = currently executing
-- use YOUR guid here
DECLARE @job_id uniqueidentifier = '5d00732-69E0-2937-8238-40F54CF36BB1'
EXEC master.dbo.xp_sqlagent_enum_jobs 1, sa, @job_id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
