主页 / dba / 问题 / 47548
Accepted
Ardalan Shahgholi
Asked: 2013-08-05 22:25:24 +0800 CST

获取“作业属性 -> 通知”页面中存在的信息？

  • 772

我使用 SQL Server 2008 R2，我需要从 msdb 数据库中获取一些属性。

我需要一个查询来获取Job properties -> Notifications页面中存在的信息。

我可以在哪个表中找到这些信息？

sql-server sql-server-2008-r2

3 个回答

  1. 由于信息存储在哪里msdb，部分信息是在最初打开 Job Properties 窗口时获得的，然后当您实际单击 Notifications 页面时会查询到更多信息。SSMS在存储过程上使用INSERT/来获取运算符信息，这非常笨拙……所以不幸的是，在这种情况下使用 Profiler 来捕获查询并不是特别有用。EXECsp_help_

    这是一个完整的查询，它一次性返回页面中的所有信息：

    USE [msdb];
GO

WITH jobStates AS
(
    SELECT 0 AS Level,  'Disabled' AS Description UNION ALL
    SELECT 1,           'On Success' UNION ALL
    SELECT 2,           'On Failure' UNION ALL
    SELECT 3,           'On Completion'
)
SELECT
    j.job_id,
    j.name,
    es.Description AS EmailOnJobState, e.name AS EmailOperatorName, e.email_address AS EmailOperatorEmailAddress,
    ps.Description AS PageOnJobState, p.name AS PageOperatorName, p.pager_address AS PageOperatorPagerAddress,
    nss.Description AS NetSendOnJobState, ns.name AS NetSendOperatorName, ns.netsend_address AS NetSendOperatorNetSendAddress,
    els.Description AS EventLogOnJobState,
    ds.Description AS DeleteJobOnJobState
    FROM [dbo].[sysjobs] j
    INNER JOIN jobStates es ON es.Level = j.notify_level_email
    INNER JOIN jobStates ps ON ps.Level = j.notify_level_page
    INNER JOIN jobStates nss ON nss.Level = j.notify_level_netsend
    INNER JOIN jobStates els ON els.Level = j.notify_level_eventlog
    INNER JOIN jobStates ds ON ds.Level = j.delete_level
    LEFT OUTER JOIN [dbo].[sysoperators] e ON (j.notify_level_email > 0) AND (e.id = j.notify_email_operator_id)
    LEFT OUTER JOIN [dbo].[sysoperators] p ON (j.notify_level_page > 0) AND (p.id = j.notify_page_operator_id)
    LEFT OUTER JOIN [dbo].[sysoperators] ns ON (j.notify_level_netsend > 0) AND (ns.id = j.notify_netsend_operator_id);
    • 4
  2. Best Answer

    dbo.sysjobs 和 dbo.sysoperators 视图为您提供详细信息。一些简单的代码（从第一篇文章中的编码错误中清除。）：

    SELECT j.name AS JobName,
   j.notify_level_email, e.name AS EmailOperator,
   j.notify_level_netsend, n.name AS NetSendOperator,  
   j.notify_level_page, p.name AS PagerOperator,
   j.notify_level_eventlog, 
   j.delete_level
FROM dbo.sysjobs j 
  LEFT JOIN dbo.sysoperators e 
     on j.notify_email_operator_id = e.id
  LEFT JOIN dbo.sysoperators n 
     on j.notify_netsend_operator_id = n.id
  LEFT JOIN dbo.sysoperators p 
     on j.notify_page_operator_id = p.id

    我没有充实通知级别：0 = 无，1 = 成功，2 = 失败，3 = 完成。如果作业在任何情况下都不自动删除，则 delete_level 为 0。

    • 3

  3. 我不太确定您在寻找什么，但是您是否尝试过 sp_help_job 或 sp_helpjobstep？

    USE msdb;
GO

EXEC dbo.sp_help_jobstep
    @job_name = N'JobName_Here',
    @step_id = 1;
GO

    http://technet.microsoft.com/en-us/library/ms178599.aspx

    • 1

相关问题