检索SQL代理作业的特定错误 [英] Retrieving the an SQL Agent job's specific error

问题描述

我使用 msdb..sp_help_job 来访问作业是成功还是失败，并可以检索一般错误。

但是，我想访问失败步骤的特定错误。我似乎找不到它。它不在这个由MS
http://msdn.microsoft.com/en-us/library/ms187763%28v=SQL.100%29.aspx

运行查询的帐户有限，但具有SQLUserAgent角色，并拥有正在访问的作业。

解决方案

请尝试使用 sp_help_jobhistory（Transact-SQL）

  EXECUTE MSDB.DBO.SP_HELP_JOBHISTORY NULL，'your_job_name_here'，@MODE = N'FULL'
  pre> 
 
 
您需要的信息位于 sysjobhistory（Transact-SQL）。如果你查看SP_HELP_JOBHISTORY的源代码，你会发现它调用sp_help_jobhistory_full，然后使用sysjobhistory：
  USE [msdb] 
 GO 
 / ******对象：StoredProcedure [dbo]。[sp_help_jobhistory_full]脚本日期：03/29/2010 07:58:45 ****** / 
 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER OFF 
 GO 
 ALTER PROCEDURE [dbo]。[sp_help_jobhistory_full] 
 @job_id UNIQUEIDENTIFIER，
 @job_name sysname，
 @step_id INT，
 @sql_message_id INT，
 @sql_severity INT，
 @start_run_date INT，
 @end_run_date INT，
 @start_run_time INT，
 @end_run_time INT，
 @minimum_run_duration INT，
 @run_status INT，
 @minimum_retries INT，
 @oldest_first INT，
 @server sysname，
 @mode VARCHAR 7），
 @order_by INT，
 @distributed_job_history BIT 
 AS 
 IF（@distributed_job_history = 1）
 SELECT null as instance_id，
 sj.job_id ，
 job_name = sj.name，
 null as step_id，
 null as step_name，
 null as sql_message_id，
 null as sql_severity，
 sjh.last_outcome_message作为消息，
 sjh.last_run_outcome as run_status，
 sjh.last_run_date as run_date，
 sjh.last_run_time as run_time，
 sjh.last_run_duration as run_duration，
 null as operator_emailed ，
 null as operator_netsentname，
 null as operator_paged，
 null as retries_attempted，
 sts.server_name as server 
从msdb.dbo.sysjobservers sjh 
 JOIN msdb.dbo.systargetservers sts ON（sts.server_id = sjh.server_id）
 JOIN msdb.dbo.sysjobs_view sj ON（sj.job_id = sjh.job_id）
 WHERE 
（@job_id = sjh.job_id）
 AND（（@start_run_date IS NULL）OR（sjh.last_run_date> = @start_run_date））
 AND（（@end_run_date IS NULL）OR（sjh.last_run_date< = @end_run_date ）
 AND（（@minart_run_duration IS NULL）OR（sjh.last_run_duration> = @minimum_run_duration））
 AND（（@start_run_time IS NULL）OR（sjh.last_run_time> = @start_run_time） 
 AND（（@run_status IS NULL）OR（@run_status = sjh.last_run_outcome））
 AND（（@server IS NULL）OR（sts.server_name = @server））
 ELSE 
 SELECT sjh.instance_id， - 这只是为了排序目的包括
 sj.job_id，
 job_name = sj.name，
 sjh.step_id，
 sjh.step_name ，
 sjh.sql_message_id，
 sjh.sql_severity，
 sjh.message，
 sjh.run_status，
 sjh.run_date，
 sjh.run_time，
 sjh.run_duration，
 operator_emailed = so1.name，
 operator_netsent = so2.name，
 operator_paged = so3.name，
 sjh.retries_attempted，
 sjh .server 
从msdb.dbo.sysjobhistory sjh 
 LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON（sjh.operator_id_emailed = so1.id）
 LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON （sjh.operator_id_netsent = so2.id）
 LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON（sjh.operator_id_paged = so3.id），
 msdb.dbo.sysjobs_view sj 
 WHERE（sj .job_id = sjh.job_id）
 AND（（@job_id IS NULL）OR（@job_id = sjh.job_id））
 AND（（@step_id IS NULL）OR（@step_id = sjh.step_id） ）
 AND（（@sql_message_id IS NULL）OR（@sql_message_id = sjh.sql_message_id））
 AND（（@sql_severity IS NULL）OR（@sql_severity = sjh.sql_severity））
 AND （（@start_run_date IS NULL）OR（sjh.run_date> = @start_run_date））
 AND（（@end_run_date IS NULL）OR（sjh.run_date <= @end_run_date））
 AND @start_run_time IS NULL）OR（sjh.run_time> = @start_run_time））
 AND（（@end_run_time IS NULL）OR（sjh.run_time< = @end_run_time））
 AND（（@minimum_run_duration IS NULL）OR（sjh.run_duration> = @minimum_run_duration））
 AND（（@run_status IS NULL）OR（@run_status = sjh.run_status））
 AND（（@minimum_retries IS NULL） （sjh.retries_attempted> = @minimum_retries））
 AND（（@server IS NULL）OR（sjh.server = @server））
 ORDER BY（sjh.instance_id * @order_by）
  
 
I am using msdb..sp_help_job to access whether a job succeeded or failed and can retrieve a general error. 

But, I want to access the specific error for the step that failed. I cannot seem to find it. It is not in this list of helpful stored procedures provided by MS
http://msdn.microsoft.com/en-us/library/ms187763%28v=SQL.100%29.aspx

The account running the query is limited but does have the SQLUserAgent role and owns the Jobs that it is accessing.
解决方案
try using sp_help_jobhistory (Transact-SQL)
EXECUTE MSDB.DBO.SP_HELP_JOBHISTORY NULL,'your_job_name_here', @MODE = N'FULL'
the info you want is in sysjobhistory (Transact-SQL). If you view SP_HELP_JOBHISTORY's source code you will find that it calls sp_help_jobhistory_full, which then uses sysjobhistory:
USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[sp_help_jobhistory_full]    Script Date: 03/29/2010 07:58:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_help_jobhistory_full]
               @job_id               UNIQUEIDENTIFIER,
               @job_name             sysname,
               @step_id              INT,
               @sql_message_id       INT,
               @sql_severity         INT,
               @start_run_date       INT,
               @end_run_date         INT,
               @start_run_time       INT,
               @end_run_time         INT,
               @minimum_run_duration INT,
               @run_status           INT,
               @minimum_retries      INT,
               @oldest_first         INT,
               @server               sysname,
               @mode                 VARCHAR(7),
               @order_by             INT,
               @distributed_job_history BIT
AS
IF(@distributed_job_history = 1)
  SELECT null as instance_id, 
     sj.job_id,
     job_name = sj.name,
     null as step_id,
     null as step_name,
     null as sql_message_id,
     null as sql_severity,
     sjh.last_outcome_message as message,
     sjh.last_run_outcome as run_status,
     sjh.last_run_date as run_date,
     sjh.last_run_time as run_time,
    sjh.last_run_duration as run_duration,
     null as operator_emailed,
     null as operator_netsentname,
     null as operator_paged,
     null as retries_attempted,
     sts.server_name as server
  FROM msdb.dbo.sysjobservers                sjh
  JOIN msdb.dbo.systargetservers sts ON (sts.server_id = sjh.server_id)
  JOIN msdb.dbo.sysjobs_view     sj  ON(sj.job_id = sjh.job_id)
  WHERE 
  (@job_id = sjh.job_id)
  AND ((@start_run_date       IS NULL) OR (sjh.last_run_date >= @start_run_date))
  AND ((@end_run_date         IS NULL) OR (sjh.last_run_date <= @end_run_date))
  AND ((@start_run_time       IS NULL) OR (sjh.last_run_time >= @start_run_time))
  AND ((@minimum_run_duration IS NULL) OR (sjh.last_run_duration >= @minimum_run_duration))
  AND ((@run_status           IS NULL) OR (@run_status = sjh.last_run_outcome))
  AND ((@server               IS NULL) OR (sts.server_name = @server))
ELSE
  SELECT sjh.instance_id, -- This is included just for ordering purposes
     sj.job_id,
     job_name = sj.name,
     sjh.step_id,
     sjh.step_name,
     sjh.sql_message_id,
     sjh.sql_severity,
     sjh.message,
     sjh.run_status,
     sjh.run_date,
     sjh.run_time,
     sjh.run_duration,
     operator_emailed = so1.name,
     operator_netsent = so2.name,
     operator_paged = so3.name,
     sjh.retries_attempted,
     sjh.server
  FROM msdb.dbo.sysjobhistory                sjh
     LEFT OUTER JOIN msdb.dbo.sysoperators so1  ON (sjh.operator_id_emailed = so1.id)
     LEFT OUTER JOIN msdb.dbo.sysoperators so2  ON (sjh.operator_id_netsent = so2.id)
     LEFT OUTER JOIN msdb.dbo.sysoperators so3  ON (sjh.operator_id_paged = so3.id),
     msdb.dbo.sysjobs_view sj
  WHERE (sj.job_id = sjh.job_id)
  AND ((@job_id               IS NULL) OR (@job_id = sjh.job_id))
  AND ((@step_id              IS NULL) OR (@step_id = sjh.step_id))
  AND ((@sql_message_id       IS NULL) OR (@sql_message_id = sjh.sql_message_id))
  AND ((@sql_severity         IS NULL) OR (@sql_severity = sjh.sql_severity))
  AND ((@start_run_date       IS NULL) OR (sjh.run_date >= @start_run_date))
  AND ((@end_run_date         IS NULL) OR (sjh.run_date <= @end_run_date))
  AND ((@start_run_time       IS NULL) OR (sjh.run_time >= @start_run_time))
  AND ((@end_run_time         IS NULL) OR (sjh.run_time <= @end_run_time))
  AND ((@minimum_run_duration IS NULL) OR (sjh.run_duration >= @minimum_run_duration))
  AND ((@run_status           IS NULL) OR (@run_status = sjh.run_status))
  AND ((@minimum_retries      IS NULL) OR (sjh.retries_attempted >= @minimum_retries))
  AND ((@server               IS NULL) OR (sjh.server = @server))
  ORDER BY (sjh.instance_id * @order_by)


                        
这篇关于检索SQL代理作业的特定错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！