存储过程中通过SQL调用JOB

众所周知，SQL Server Agent中可以调用JOB，并且设置Schedule，但除了在在Agent中调用之外，微软还提供了其他几种调用方式：

• 通过C#调用
• 通过SQL调用

这里我们详细介绍下通过SQL 调用JOB。

1. 首先所有JOB的都存储在系统数据库MSDB中：

2. 启动JOB 存储过程：（test为JOB名称）

        EXEC msdb.dbo.sp_start_job N'test'

 3. 停止JOB 存储过程：（test为JOB名称）

           EXECmsdb.[dbo].[sp_stop_job] N'test'

 

4. 获取JOB当前的执行状态：

 

declare @JobID UNIQUEIDENTIFIER

select@JobID=job_id frommsdb.[dbo].[sysjobs]  where[Name]='test'

EXECmsdb.[sys].[sp_MSget_jobstate] @JobID 

通过执行以上脚本可以获取Test Job的执行状态码：

状态码	状态描述
1	正在运行中
4	当前空闲状态

 

另：因为一般上获取执行状态的目的是用来接收这个执行状态，并且用于其他的逻辑的判断（比如：当当前状态正在运行中的时候，继续等待等，或者不要往下执行等），因此一般需要有变量来接收返回的执行状态码。所以可以将步骤四中的存储的核心代码提炼出来，如下文：

declare @JobID UNIQUEIDENTIFIER

select@JobID=job_id frommsdb.[dbo].[sysjobs]  where[Name]='test'

    DECLARE@is_sysadmin INT

    DECLARE@job_owner   sysname

    SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

    SELECT @job_owner = suser_sname(suser_sid())        

    CREATETABLE#xp_results (job_id                UNIQUEIDENTIFIERNOTNULL,

                            last_run_date         INT              NOTNULL,

                            last_run_time         INT              NOTNULL,

                            next_run_date         INT              NOTNULL,

                            next_run_time         INT              NOTNULL,

                            next_run_schedule_id  INT              NOTNULL,

                            requested_to_run      INT              NOTNULL,-- BOOL

                            request_source        INT              NOTNULL,

                            request_source_id     sysname          collatedatabase_default null,

                            running               INT              NOTNULL,-- BOOL

                            current_step          INT              NOTNULL,

                            current_retry_attempt INT              NOTNULL,

                            job_state             INT              NOTNULL)

    INSERTINTO#xp_results

    EXECUTEmaster.dbo.xp_sqlagent_enum_jobs @is_sysadmin,@job_owner

    -- Select the job state of the job in question

    SELECT  job_state FROM#xp_results WHERE@JobID=job_id

        droptable#xp_results