通过C#调用
public object B2COutboundDownFile()
{
var obj = new object();
try
{
var jobConnection = new SqlConnection(JobDBConString);
ServerConnection conn = new ServerConnection(jobConnection); //Create SQL server conn, Windows Authentication
Server server = new Server(conn); //Connect SQL Server
var job = server.JobServer.Jobs["Sustainable_B2C_OutBound"];//Get the specified job
var JobStatus = job.CurrentRunStatus.ToString();
//var dataTime = job.LastRunDate.ToString();
if (JobStatus == "Idle")
{
obj = new
{
code = 0,
msg = "Run Start"
};
job.Start();
}
else
{
//该job正在运行中
obj = new
{
code = 1,
msg = "The job is executing,Please try again later!"
};
}
jobConnection.Close();
}
catch (Exception ex)
{
obj = new
{
code = 500,
msg = "Run failed"
};
log.Error("调用Sql Job" + ex);
}
return obj;
}
通过SQL调用
启动JOB 存储过程:(test为JOB名称)
EXEC msdb.dbo.sp_start_job N'test'
停止JOB 存储过程:(test为JOB名称)
EXECmsdb.[dbo].[sp_stop_job] N'test'
获取JOB当前的执行状态:
declare @JobID UNIQUEIDENTIFIER
select@JobID=job_id frommsdb.[dbo].[sysjobs] where[Name]='test'
EXECmsdb.[sys].[sp_MSget_jobstate] @JobID
状态码 | 状态描述 |
---|---|
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