SQL Server作业历史记录2008 R2

Question

我在SQL Server R2中安排了许多作业。每个作业可能包含几个步骤。

我想要每个工作下的每个步骤的最新资料，最新状态，最新结构，最新消息，最新资料。

我有这个查询与我工作良好。

唯一的问题是，它不允许我转换 lastundatetime 字符串到DateTime

SELECT 
[sJOB].[job_id] AS [JobID],
[sJOB].[name] AS [JobName],
[sJOBS].step_name AS [StepName],
CASE 
    WHEN [sJOBS].[last_run_date] IS NULL OR [sJOBS].[last_run_time] IS NULL THEN NULL
    ELSE 
            CAST(CAST([sJOBS].[last_run_date] AS VARCHAR(8))
            + ' ' 
            + STUFF(
                STUFF(RIGHT('000000' + CAST([sJOBS].[last_run_time] AS VARCHAR(6)),  6)
                    , 3, 0, ':')
                , 6, 0, ':') AS DATETIME)
  END AS [LastRunDateTime],
 CASE [sJOBH].[run_status]
    WHEN 0 THEN 'Failed'
    WHEN 1 THEN 'Succeeded'
    WHEN 2 THEN 'Retry'
    WHEN 3 THEN 'Canceled'
    WHEN 4 THEN 'Running' -- In Progress
  END AS [LastRunStatus],
  STUFF(
        STUFF(RIGHT('000000' + CAST([sJOBS].[last_run_duration] AS VARCHAR(6)),  6)
            , 3, 0, ':')
        , 6, 0, ':') 
    AS [LastRunDuration (HH:MM:SS)],
  [sJOBH].[message] AS [LastRunStatusMessage],
  CASE [sJOBSCH].[NextRunDate]
    WHEN 0 THEN NULL
    ELSE CAST(
            CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
            + ' ' 
            + STUFF(
                STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                    , 3, 0, ':')
                , 6, 0, ':')
            AS DATETIME)
  END AS [NextRunDateTime]
FROM 
(SELECT * FROM [msdb].[dbo].[sysjobs] WHERE enabled = 1) AS [sJOB]
LEFT JOIN sysjobsteps [sJOBS]
        ON([sJOB].[job_id] = [sJOBS].[job_id])
LEFT JOIN (
            SELECT
                [job_id],
                MIN([next_run_date]) AS [NextRunDate],
                MIN([next_run_time]) AS [NextRunTime]
            FROM [msdb].[dbo].[sysjobschedules]
            GROUP BY [job_id]
        ) AS [sJOBSCH]
    ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN (
            SELECT 
                [job_id],
                [run_date],
                [run_time],
                [run_status],
                [run_duration],
                [message],
                ROW_NUMBER() OVER (
                                        PARTITION BY [job_id] 
                                        ORDER BY [run_date] DESC, [run_time] DESC
                  ) AS RowNumber
            FROM [msdb].[dbo].[sysjobhistory]
            WHERE [step_id] = 0
        ) AS [sJOBH]
    ON [sJOB].[job_id] = [sJOBH].[job_id]
    AND [sJOBH].[RowNumber] = 1
ORDER BY [JobName]

CAST()函数在lastundatetime给我以下错误

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

谁能告诉我怎么了?

Answer 1

不能保证您有完全相同的问题，但在我测试的系统上，我得到了相同的错误，因为一些作业步骤将0作为last_run_date的值。

我修复了这个与以下修改您的查询。也许这对你也有用。

...
CASE 
    WHEN [sJOBS].[last_run_date] IS NULL OR [sJOBS].[last_run_time] IS NULL THEN NULL
    WHEN [sJOBS].[last_run_date] = 0 THEN NULL --<< line added here <<
    ELSE 
            CAST(CAST([sJOBS].[last_run_date] AS VARCHAR(8))
            + ' ' 
            + STUFF(
                STUFF(RIGHT('000000' + CAST([sJOBS].[last_run_time] AS VARCHAR(6)),  6)
                    , 3, 0, ':')
                , 6, 0, ':') AS DATETIME)
  END AS [LastRunDateTime],
...