如题.
在网上查了很多关于取得作业状态的帖子。--查看Job是否在運行
Declare @Job_ID as UNIQUEIDENTIFIERselect @Job_ID =Job_ID from msdb.dbo.sysjobs where name = 'Jobname'Exec master..sp_MSget_jobstate @Job_ID
--返回值为 1 - 正在运行
-- 4 - 表示完成(成功或失败)
但是这个值怎么赋给一个变量呢??
求指教!
在网上查了很多关于取得作业状态的帖子。--查看Job是否在運行
Declare @Job_ID as UNIQUEIDENTIFIERselect @Job_ID =Job_ID from msdb.dbo.sysjobs where name = 'Jobname'Exec master..sp_MSget_jobstate @Job_ID
--返回值为 1 - 正在运行
-- 4 - 表示完成(成功或失败)
但是这个值怎么赋给一个变量呢??
求指教!
Declare @Job_ID as UNIQUEIDENTIFIERset @Job_ID = (select Job_ID from msdb.dbo.sysjobs where name = 'Jobname')Exec master..sp_MSget_jobstate @Job_ID
try
看来只能重写sp_MSget_jobstate了,下面是代码。
--ジョブの状態を取得
DECLARE @job_id uniqueidentifier, @job_state int
set @job_id = (select job_id from msdb..sysjobs where name = 'testJob01') DECLARE @is_sysadmin INT
DECLARE @job_owner sysname
DECLARE @job_id_as_char VARCHAR(36) SET NOCOUNT ON
CREATE TABLE #xp_results (
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) if (@job_id IS NULL)
BEGIN
SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id)
RAISERROR(14262, -1, -1, '@job_id', @job_id_as_char)
END -- Capture job execution information (for local jobs only since that's all SQLServerAgent caches)
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = suser_sname(suser_sid())
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner -- Select the job state of the job in question
SELECT @job_state = job_state FROM #xp_results WHERE @job_id = job_id PRINT @job_state DROP TABLE #xp_results
select
@Job_ID=a.Job_ID
from msdb.dbo.sysjobhistory a
inner join msdb.dbo.sysjobs b on a.job_id=b.job_id
where b.name='Jobname'