隨手筆記

訂閱的排程時間設定有些僅能在SSMS訂閱上設定,

透過查詢該筆訂閱資料,

再到SSMS上訂閱右鍵進入設定排程時間,

可設定如每月的最後一天、第一天...諸如此類的.

SELECT c.name,
       sJOB.job_id AS JobID,
       sJOB.name AS JobName,
       sDBP.name AS JobOwner,
       sCAT.name AS JobCategory,
       sJOB.description AS JobDescription,
       CASE sJOB . enabled
         WHEN 1 THEN
          'Yes'
         WHEN 0 THEN
          'No'
       END AS IsEnabled,
       sJOB.date_created AS JobCreatedOn,
       sJOB.date_modified AS JobLastModifiedOn,
       sSVR.name AS OriginatingServerName,
       sJSTP.step_id AS JobStartStepNo,
       sJSTP.step_name AS JobStartStepName,
       CASE
         WHEN sSCH . schedule_uid IS NULL THEN
          'No'
         ELSE
          'Yes'
       END AS IsScheduled,
       sSCH . schedule_uid AS JobScheduleID,
       sSCH . name AS JobScheduleName,
       CASE sJOB . delete_level
         WHEN 0 THEN
          'Never'
         WHEN 1 THEN
          'On Success'
         WHEN 2 THEN
          'On Failure'
         WHEN 3 THEN
          'On Completion'
       END AS JobDeletionCriterion
  FROM dbo. Catalog c
 INNER JOIN dbo.Subscriptions S ON c.ItemID = S.Report_OID
 INNER JOIN dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
 inner join msdb.dbo.sysjobs AS sJOB ON Convert(nvarchar(128),R.ScheduleID) =sJOB.name
  LEFT JOIN msdb.sys.servers AS sSVR ON sJOB.originating_server_id = sSVR.server_id
  LEFT JOIN msdb.dbo.syscategories AS sCAT ON sJOB.category_id = sCAT.category_id
  LEFT JOIN msdb.dbo.sysjobsteps AS sJSTP ON sJOB.job_id = sJSTP.job_id AND sJOB.start_step_id = sJSTP.step_id
  LEFT JOIN msdb.sys.database_principals AS sDBP ON sJOB.owner_sid = sDBP . sid
  LEFT JOIN msdb.dbo.sysjobschedules AS sJOBSCH ON sJOB.job_id = sJOBSCH.job_id
  LEFT JOIN msdb.dbo.sysschedules AS sSCH ON sJOBSCH.schedule_id = sSCH.schedule_id
 ORDER BY JobName