Hi exerts.
I just want to select all jobs that have failed in the past day. Got a little problem with my TSQL code:
select @@SERVERNAME as 'Server', a.name as 'Job Name',
b.run_date as 'Run Date', b.run_status as 'Job Status'
from sysjobs a INNER JOIN sysjobhistory b ON a.job_id = b.job_id
where a.enabled = 1 and b.step_id = 0 and b.run_status = 0 AND DATEDIFF(day, getDate(), b.run_date) <= 1
order by b.run_date;
"Arithmetic overflow error converting expression to data type datetime"
Thanks for any suggestions.
What data type is b.run_date.
Try the following to find some items that might be null or an invalid date
TRY_CONVERT
1 Like
Thanks. b.run_date is integer
So I tried this:
Use msdb;
declare @curdate int;
set @curdate = (select convert(int, getdate()))
select @@SERVERNAME as 'Server', a.name as 'Job Name',
b.run_date as 'Run Date', b.run_status as 'Job Status'
from sysjobs a INNER JOIN sysjobhistory b ON a.job_id = b.job_id
where a.enabled = 1 and b.step_id = 0 and b.run_status = 0 AND DATEDIFF(day, @curdate, b.run_date) <= 1
order by b.run_date
Error: Arithmetic overflow error converting expression to data type datetime.
Now you are just hacking with tje same end result
What are some sample values in that column
use the built in function
select @@SERVERNAME as 'Server',
a.name as 'Job Name',
dbo.agent_datetime(b.run_date, run_time),
b.run_date as 'Run Date', b.run_status as 'Job Status'
from sysjobs a INNER JOIN sysjobhistory b ON a.job_id = b.job_id
where 1 = 1
and a.enabled = 1
and b.step_id = 0
and b.run_status = 0
and DATEDIFF(day, getDate(), dbo.agent_datetime(b.run_date, run_time)) <= 1
order by b.run_date;
might not the best for performance
1 Like
or
select @@SERVERNAME as 'Server',
a.name as 'Job Name',
filterdate,
x.run_date as 'Run Date',
x.run_status as 'Job Status'
from sysjobs a
INNER join (select job_id, run_date,
run_status,
step_id,
dbo.agent_datetime(run_date, run_time) as filterdate
from sysjobhistory) x
on a.job_id = x.job_id
where a.enabled = 1
and x.step_id = 0
and x.run_status = 0
and DATEDIFF(day, getDate(), filterdate) <= 1
order by x.run_date;
1 Like
You should never use functions against a table column in a WHERE (or JOIN) clause. Instead, convert the other value to match the format of the column:
SELECT
@@SERVERNAME as 'Server', j.name as 'Job Name',
jh.run_date as 'Run Date', jh.run_status as 'Job Status'
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE j.enabled = 1 AND jh.step_id = 0 AND jh.run_status = 0 AND
jh.run_date >= CONVERT(varchar(8), DATEADD(DAY, -1, GETDATE()), 112) AND
jh.run_date <= CONVERT(varchar(8), GETDATE(), 112)
ORDER BY jh.run_date;
2 Likes
That looks like a bunch of functions in a WHERE clause to me 
But NOT "against a table column". Functions against literal / constant values don't matter, since they won't prevent index seeks and are only performed once. Functions against a table column are applied for every row that is read, and, as noted, prevent index seeks on indexes on that column.
2 Likes
Here's a comparison:
IF OBJECT_ID('tempdb.dbo.#test_table_column_convert') IS NOT NULL
DROP TABLE #test_table_column_convert;
CREATE TABLE #test_table_column_convert (
run_date int NOT NULL PRIMARY KEY --YYYYMMDD
);
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
SELECT 0 AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
),
cte_tally10K AS (
SELECT 0 AS number UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally100 c1 CROSS JOIN cte_tally100 c2
)
INSERT INTO #test_table_column_convert ( run_date )
SELECT CONVERT(varchar(8), DATEADD(DAY, t.number, '20000101'), 112)
FROM cte_tally10K t
--SELECT TOP (100) * FROM #test_table_column_convert
SET STATISTICS IO ON;
SELECT TOP (1) *
FROM #test_table_column_convert
WHERE CONVERT(varchar(8), run_date) = '20210505' --must scan whole table
SELECT TOP (1) *
FROM #test_table_column_convert
WHERE run_date = CONVERT(varchar(8), GETDATE(), 112) --can do index seek (lookup)
SET STATISTICS IO OFF;
2 Likes
Thanks, these are both good solutions. But yosiasz, yours returns rows for jobs that failed days ago as well as failed today.
ScottPletcher, yours works well, returning only those jobs which failed in the past day.
But as I think more about it, I need to only return jobs which failed on the most recent run which was in the past day. I know \that's a change from my original request.
This will test only the last run of each job within the last week.
SELECT
@@SERVERNAME as 'Server', j.name as 'Job Name',
jh.run_date as 'Run Date', jh.run_status as 'Job Status'
FROM msdb.dbo.sysjobs j
CROSS APPLY (
SELECT TOP (1) jh.*, ROW_NUMBER() OVER(PARTITION BY jh.job_id ORDER BY jh.run_date DESC) AS row_num
FROM msdb.dbo.sysjobhistory jh
WHERE
jh.step_id = 0 AND
jh.run_date >= CONVERT(varchar(8), DATEADD(DAY, -7, GETDATE()), 112) AND
jh.run_date <= CONVERT(varchar(8), GETDATE(), 112)
ORDER BY jh.run_date DESC
) AS jh
WHERE j.enabled = 1 AND jh.run_status = 0
ORDER BY j.name, jh.run_date;
1 Like
Thanks, ScottPletcher. That looks promising, but it doesn't return any rows. I forced a job to fail just now and it didn't return any rows. I changed the -7 to various values but no luck. Thanks
I was trying different things, that query is a bad mix of options. Try this instead, and adjust the dates as needed:
SELECT
@@SERVERNAME as 'Server', j.name as 'Job Name',
jh.run_date as 'Run Date', jh.run_status as 'Job Status'
FROM msdb.dbo.sysjobs j
LEFT OUTER JOIN (
SELECT ROW_NUMBER() OVER(PARTITION BY jh.job_id ORDER BY jh.run_date DESC) AS row_num, jh.*
FROM msdb.dbo.sysjobhistory jh
WHERE
jh.step_id = 0 AND
jh.run_date >= CONVERT(varchar(8), DATEADD(DAY, -7, GETDATE()), 112) AND
jh.run_date <= CONVERT(varchar(8), GETDATE() + 1, 112)
--ORDER BY jh.run_date DESC
) AS jh ON jh.job_id = j.job_id
WHERE j.enabled = 1 AND jh.run_status = 0
ORDER BY j.name, jh.run_date;