Всем привет, пытаюсь внедрить сторонний скрипт для отчета, вот тело скрипта.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
SET NOCOUNT ON;
-- Determine UTC offset so that all times can easily be converted to UTC.
DECLARE @utc_offset INT;
DECLARE @minutes_to_monitor SMALLINT = 1440
SELECT
@utc_offset = -1 * DATEDIFF(HOUR, GETUTCDATE(), GETDATE());
-- First, collect list of SQL Server agent jobs and update ours as needed.
-- Update our jobs data with any changes since the last update time.
MERGE INTO dbo.sql_server_agent_job AS TARGET
USING (SELECT
sysjobs.job_id AS sql_server_agent_job_id_guid,
sysjobs.name AS sql_server_agent_job_name,
sysjobs.date_created AS job_create_datetime_utc,
sysjobs.date_modified AS job_last_modified_datetime_utc,
sysjobs.enabled AS is_enabled,
0 AS is_deleted,
ISNULL(syscategories.name, '') AS job_category_name
FROM msdb.dbo.sysjobs
LEFT JOIN msdb.dbo.syscategories
ON syscategories.category_id = sysjobs.category_id) AS SOURCE
ON (SOURCE.sql_server_agent_job_id_guid = TARGET.sql_server_agent_job_id_guid)
WHEN NOT MATCHED BY TARGET
THEN INSERT
(sql_server_agent_job_id_guid, sql_server_agent_job_name, job_create_datetime_utc, job_last_modified_datetime_utc,
is_enabled, is_deleted, job_category_name)
VALUES (
SOURCE.sql_server_agent_job_id_guid,
SOURCE.sql_server_agent_job_name,
SOURCE.job_create_datetime_utc,
SOURCE.job_last_modified_datetime_utc,
SOURCE.is_enabled,
SOURCE.is_deleted,
SOURCE.job_category_name)
WHEN MATCHED AND SOURCE.job_last_modified_datetime_utc > TARGET.job_last_modified_datetime_utc
THEN UPDATE
SET sql_server_agent_job_name = SOURCE.sql_server_agent_job_name,
job_create_datetime_utc = SOURCE.job_create_datetime_utc,
job_last_modified_datetime_utc = SOURCE.job_last_modified_datetime_utc,
is_enabled = SOURCE.is_enabled,
is_deleted = SOURCE.is_deleted,
job_category_name = SOURCE.job_category_name;
-- If a job was deleted, then mark it as no longer enabled.
UPDATE sql_server_agent_job
SET is_enabled = 0,
is_deleted = 1
FROM dbo.sql_server_agent_job
LEFT JOIN msdb.dbo.sysjobs
ON sysjobs.Job_Id = sql_server_agent_job.sql_server_agent_job_id_guid
WHERE sysjobs.Job_Id IS NULL;
-- Find all recent job failures and log them in the target log table.
WITH CTE_NORMALIZE_DATETIME_DATA AS (
SELECT
sysjobhistory.job_id AS sql_server_agent_job_id_guid,
CAST(sysjobhistory.run_date AS VARCHAR(MAX)) AS run_date_string,
REPLICATE('0', 6 - LEN(CAST(sysjobhistory.run_time AS VARCHAR(MAX)))) + CAST(sysjobhistory.run_time AS VARCHAR(MAX)) AS run_time_string,
REPLICATE('0', 6 - LEN(CAST(sysjobhistory.run_duration AS VARCHAR(MAX)))) + CAST(sysjobhistory.run_duration AS VARCHAR(MAX)) AS run_duration_string,
sysjobhistory.run_status,
sysjobhistory.message,
sysjobhistory.instance_id
FROM msdb.dbo.sysjobhistory WITH (NOLOCK)
WHERE sysjobhistory.run_status = 0
AND sysjobhistory.step_id = 0),
CTE_GENERATE_DATETIME_DATA AS (
SELECT
CTE_NORMALIZE_DATETIME_DATA.sql_server_agent_job_id_guid,
CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_date_string, 5, 2) + '/' + SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_date_string, 7, 2) + '/' + SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_date_string, 1, 4) AS DATETIME) +
CAST(STUFF(STUFF(CTE_NORMALIZE_DATETIME_DATA.run_time_string, 5, 0, ':'), 3, 0, ':') AS DATETIME) AS job_start_datetime,
CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_duration_string, 1, 2) AS INT) * 3600 +
CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_duration_string, 3, 2) AS INT) * 60 +
CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_duration_string, 5, 2) AS INT) AS job_duration_seconds,
CASE CTE_NORMALIZE_DATETIME_DATA.run_status
WHEN 0 THEN 'Failure'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
ELSE 'Unknown'
END AS job_status,
CTE_NORMALIZE_DATETIME_DATA.message,
CTE_NORMALIZE_DATETIME_DATA.instance_id
FROM CTE_NORMALIZE_DATETIME_DATA)
SELECT
CTE_GENERATE_DATETIME_DATA.sql_server_agent_job_id_guid,
DATEADD(HOUR, @utc_offset, CTE_GENERATE_DATETIME_DATA.job_start_datetime) AS job_start_time_utc,
DATEADD(HOUR, @utc_offset, DATEADD(SECOND, ISNULL(CTE_GENERATE_DATETIME_DATA.job_duration_seconds, 0), CTE_GENERATE_DATETIME_DATA.job_start_datetime)) AS job_failure_time_utc,
ISNULL(CTE_GENERATE_DATETIME_DATA.message, '') AS job_failure_message,
CTE_GENERATE_DATETIME_DATA.instance_id
INTO #job_failure
FROM CTE_GENERATE_DATETIME_DATA
WHERE DATEADD(HOUR, @utc_offset, CTE_GENERATE_DATETIME_DATA.job_start_datetime) > DATEADD(MINUTE, -1 * @minutes_to_monitor, GETUTCDATE());
WITH CTE_NORMALIZE_DATETIME_DATA AS (
SELECT
sysjobhistory.job_id AS sql_server_agent_job_id_guid,
CAST(sysjobhistory.run_date AS VARCHAR(MAX)) AS run_date_string,
REPLICATE('0', 6 - LEN(CAST(sysjobhistory.run_time AS VARCHAR(MAX)))) + CAST(sysjobhistory.run_time AS VARCHAR(MAX)) AS run_time_string,
REPLICATE('0', 6 - LEN(CAST(sysjobhistory.run_duration AS VARCHAR(MAX)))) + CAST(sysjobhistory.run_duration AS VARCHAR(MAX)) AS run_duration_string,
sysjobhistory.run_status,
sysjobhistory.step_id,
sysjobhistory.step_name,
sysjobhistory.message,
sysjobhistory.retries_attempted,
sysjobhistory.sql_severity,
sysjobhistory.sql_message_id,
sysjobhistory.instance_id
FROM msdb.dbo.sysjobhistory WITH (NOLOCK)
WHERE sysjobhistory.run_status = 0
AND sysjobhistory.step_id > 0),
CTE_GENERATE_DATETIME_DATA AS (
SELECT
CTE_NORMALIZE_DATETIME_DATA.sql_server_agent_job_id_guid,
CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_date_string, 5, 2) + '/' + SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_date_string, 7, 2) + '/' + SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_date_string, 1, 4) AS DATETIME) +
CAST(STUFF(STUFF(CTE_NORMALIZE_DATETIME_DATA.run_time_string, 5, 0, ':'), 3, 0, ':') AS DATETIME) AS job_start_datetime,
CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_duration_string, 1, 2) AS INT) * 3600 +
CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_duration_string, 3, 2) AS INT) * 60 +
CAST(SUBSTRING(CTE_NORMALIZE_DATETIME_DATA.run_duration_string, 5, 2) AS INT) AS job_duration_seconds,
CASE CTE_NORMALIZE_DATETIME_DATA.run_status
WHEN 0 THEN 'Failure'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
ELSE 'Unknown'
END AS job_status,
CTE_NORMALIZE_DATETIME_DATA.step_id,
CTE_NORMALIZE_DATETIME_DATA.step_name,
CTE_NORMALIZE_DATETIME_DATA.message,
CTE_NORMALIZE_DATETIME_DATA.retries_attempted,
CTE_NORMALIZE_DATETIME_DATA.sql_severity,
CTE_NORMALIZE_DATETIME_DATA.sql_message_id,
CTE_NORMALIZE_DATETIME_DATA.instance_id
FROM CTE_NORMALIZE_DATETIME_DATA)
SELECT
CTE_GENERATE_DATETIME_DATA.sql_server_agent_job_id_guid,
DATEADD(HOUR, @utc_offset, CTE_GENERATE_DATETIME_DATA.job_start_datetime) AS job_start_time_utc,
DATEADD(HOUR, @utc_offset, DATEADD(SECOND, ISNULL(CTE_GENERATE_DATETIME_DATA.job_duration_seconds, 0), CTE_GENERATE_DATETIME_DATA.job_start_datetime)) AS job_failure_time_utc,
CTE_GENERATE_DATETIME_DATA.step_id AS job_failure_step_number,
ISNULL(CTE_GENERATE_DATETIME_DATA.message, '') AS job_step_failure_message,
CTE_GENERATE_DATETIME_DATA.sql_severity AS job_step_severity,
CTE_GENERATE_DATETIME_DATA.retries_attempted,
CTE_GENERATE_DATETIME_DATA.step_name,
CTE_GENERATE_DATETIME_DATA.sql_message_id,
CTE_GENERATE_DATETIME_DATA.instance_id
INTO #job_step_failure
FROM CTE_GENERATE_DATETIME_DATA
WHERE DATEADD(HOUR, @utc_offset, CTE_GENERATE_DATETIME_DATA.job_start_datetime) > DATEADD(MINUTE, -1 * @minutes_to_monitor, GETUTCDATE());
-- Get jobs that failed due to failed steps.
WITH CTE_FAILURE_STEP AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY job_step_failure.sql_server_agent_job_id_guid, job_step_failure.job_failure_time_utc ORDER BY job_step_failure.job_failure_step_number DESC) AS recent_step_rank
FROM #job_step_failure job_step_failure)
INSERT INTO dbo.sql_server_agent_job_failure
(sql_server_agent_job_id, sql_server_agent_instance_id, job_start_time_utc, job_failure_time_utc, job_failure_step_number, job_failure_step_name,
job_failure_message, job_step_failure_message, job_step_severity, job_step_message_id, retries_attempted, has_email_been_sent_to_operator)
SELECT
sql_server_agent_job.sql_server_agent_job_id,
CTE_FAILURE_STEP.instance_id,
job_failure.job_start_time_utc,
CTE_FAILURE_STEP.job_failure_time_utc,
CTE_FAILURE_STEP.job_failure_step_number,
CTE_FAILURE_STEP.step_name AS job_failure_step_name,
job_failure.job_failure_message,
CTE_FAILURE_STEP.job_step_failure_message,
CTE_FAILURE_STEP.job_step_severity,
CTE_FAILURE_STEP.sql_message_id AS job_step_message_id,
CTE_FAILURE_STEP.retries_attempted,
0 AS has_email_been_sent_to_operator
FROM #job_failure job_failure
INNER JOIN dbo.sql_server_agent_job
ON job_failure.sql_server_agent_job_id_guid = sql_server_agent_job.sql_server_agent_job_id_guid
INNER JOIN CTE_FAILURE_STEP
ON job_failure.sql_server_agent_job_id_guid = CTE_FAILURE_STEP.sql_server_agent_job_id_guid
AND job_failure.job_failure_time_utc = CTE_FAILURE_STEP.job_failure_time_utc
WHERE CTE_FAILURE_STEP.recent_step_rank = 1
AND CTE_FAILURE_STEP.instance_id NOT IN (SELECT sql_server_agent_job_failure.sql_server_agent_instance_id FROM dbo.sql_server_agent_job_failure);
-- Get jobs that failed without any failed steps.
INSERT INTO dbo.sql_server_agent_job_failure
(sql_server_agent_job_id, sql_server_agent_instance_id, job_start_time_utc, job_failure_time_utc, job_failure_step_number, job_failure_step_name,
job_failure_message, job_step_failure_message, job_step_severity, job_step_message_id, retries_attempted, has_email_been_sent_to_operator)
SELECT
sql_server_agent_job.sql_server_agent_job_id,
job_failure.instance_id,
job_failure.job_start_time_utc,
job_failure.job_failure_time_utc,
0 AS job_failure_step_number,
'' AS job_failure_step_name,
job_failure.job_failure_message,
'' AS job_step_failure_message,
-1 AS job_step_severity,
-1 AS job_step_message_id,
0 AS retries_attempted,
0 AS has_email_been_sent_to_operator
FROM #job_failure job_failure
INNER JOIN dbo.sql_server_agent_job
ON job_failure.sql_server_agent_job_id_guid = sql_server_agent_job.sql_server_agent_job_id_guid
WHERE job_failure.instance_id NOT IN (SELECT sql_server_agent_job_failure.sql_server_agent_instance_id FROM dbo.sql_server_agent_job_failure)
AND NOT EXISTS (SELECT * FROM #job_step_failure job_step_failure WHERE job_failure.sql_server_agent_job_id_guid = job_step_failure.sql_server_agent_job_id_guid AND job_failure.job_failure_time_utc = job_step_failure.job_failure_time_utc);
-- Get job steps that failed, but for jobs that succeeded.
WITH CTE_FAILURE_STEP AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY job_step_failure.sql_server_agent_job_id_guid, job_step_failure.job_failure_time_utc ORDER BY job_step_failure.job_failure_step_number DESC) AS recent_step_rank
FROM #job_step_failure job_step_failure)
INSERT INTO dbo.sql_server_agent_job_failure
(sql_server_agent_job_id, sql_server_agent_instance_id, job_start_time_utc, job_failure_time_utc, job_failure_step_number, job_failure_step_name,
job_failure_message, job_step_failure_message, job_step_severity, job_step_message_id, retries_attempted, has_email_been_sent_to_operator)
SELECT
sql_server_agent_job.sql_server_agent_job_id,
CTE_FAILURE_STEP.instance_id,
CTE_FAILURE_STEP.job_start_time_utc,
CTE_FAILURE_STEP.job_failure_time_utc,
CTE_FAILURE_STEP.job_failure_step_number,
CTE_FAILURE_STEP.step_name AS job_failure_step_name,
'' AS job_failure_message,
CTE_FAILURE_STEP.job_step_failure_message,
CTE_FAILURE_STEP.job_step_severity,
CTE_FAILURE_STEP.sql_message_id AS job_step_message_id,
CTE_FAILURE_STEP.retries_attempted,
0 AS has_email_been_sent_to_operator
FROM CTE_FAILURE_STEP
INNER JOIN dbo.sql_server_agent_job
ON CTE_FAILURE_STEP.sql_server_agent_job_id_guid = sql_server_agent_job.sql_server_agent_job_id_guid
LEFT JOIN #job_failure job_failure
ON job_failure.sql_server_agent_job_id_guid = CTE_FAILURE_STEP.sql_server_agent_job_id_guid
AND job_failure.job_failure_time_utc = CTE_FAILURE_STEP.job_failure_time_utc
WHERE CTE_FAILURE_STEP.recent_step_rank = 1
AND job_failure.sql_server_agent_job_id_guid IS NULL
AND CTE_FAILURE_STEP.instance_id NOT IN (SELECT sql_server_agent_job_failure.sql_server_agent_instance_id FROM dbo.sql_server_agent_job_failure);
Получаю ошибку:
авторСообщение 242, уровень 16, состояние 3, строка 52
Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.
авторMicrosoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64)
Mar 14 2020 16:10:35
Copyright (C) 2019 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
