March 19, 2009 at 5:51 am
Two tables:
Jobs
Job_History
They can be linked by JobID.
I have a third table "Failed_Jobs"
I would like a SQL Job Or SSIS package to run daily to update this table if the word "FAILED" appears in the table Job_History (column called Message)
Whats the best way to do this?
March 19, 2009 at 7:01 am
Hi
Maybe something like that?:
DECLARE @jobs TABLE (id INT, name VARCHAR(100))
DECLARE @job_hist TABLE (id INT, job_id INT, curr_date DATETIME, resolution VARCHAR(100))
DECLARE @jobs_failed TABLE (id INT, curr_date DATETIME, resolution VARCHAR(100))
INSERT INTO @jobs VALUES (1, 'Mow the lawn')
INSERT INTO @jobs VALUES (2, 'Take a coffee')
INSERT INTO @job_hist VALUES (1, 1, '20090312', 'Success')
INSERT INTO @job_hist VALUES (2, 1, '20090319', 'Failed')
INSERT INTO @job_hist VALUES (3, 2, '20090313', 'Success')
INSERT INTO @job_hist VALUES (4, 2, '20090314', 'Success')
INSERT INTO @jobs_failed
SELECT j.id, jh.curr_date, jh.resolution
FROM @jobs j
JOIN (SELECT job_id, MAX(curr_date) curr_date
FROM @job_hist
GROUP BY job_id) jh_last ON j.id = jh_last.job_id
JOIN @job_hist jh ON j.id = jh.job_id AND jh_last.curr_date = jh.curr_date
WHERE jh.resolution = 'Failed'
SELECT * FROM @jobs_failed
Greets
Flo
March 19, 2009 at 7:17 am
Thanks Flo,
Can you brake that code down so I can understand it?
I can see your doing an insert in Jobs an Job_hist, this insert is already being odown via my SSIS.
All I want to do is extract data from job_hist where the 'Message' column equals '%Failed%'
March 19, 2009 at 7:22 am
Hi
Almost the same with some comments 😉
-- This is an example for your "jobs" table which will be filled by you
DECLARE @jobs TABLE (id INT, name VARCHAR(100))
-- This is an example for your "job_hist" table which will be filled by your SSIS
DECLARE @job_hist TABLE (id INT, job_id INT, curr_date DATETIME, resolution VARCHAR(100))
-- Some sample jobs
INSERT INTO @jobs VALUES (1, 'Mow the lawn')
INSERT INTO @jobs VALUES (2, 'Take a coffee')
-- Some sample job history.
-- As you see the "Mow the lawn" job failed last time. "Take a coffee" job always works ( sure ;-) )
INSERT INTO @job_hist VALUES (1, 1, '20090312', 'Success')
INSERT INTO @job_hist VALUES (2, 1, '20090319', 'Failed')
INSERT INTO @job_hist VALUES (3, 2, '20090313', 'Success')
INSERT INTO @job_hist VALUES (4, 2, '20090314', 'Success')
-- This is an example for your "jobs_failed" table which whill be filled HERE
DECLARE @jobs_failed TABLE (id INT, curr_date DATETIME, resolution VARCHAR(100))
-- Delete theoretically existing previous failed job information
DELETE FROM @jobs_failed
-- Fill the jobs_failed table
INSERT INTO @jobs_failed
SELECT j.id, jh.curr_date, jh.resolution
FROM @jobs j
JOIN (SELECT job_id, MAX(curr_date) curr_date
FROM @job_hist
GROUP BY job_id) jh_last ON j.id = jh_last.job_id
JOIN @job_hist jh ON j.id = jh.job_id AND jh_last.curr_date = jh.curr_date
WHERE jh.resolution = 'Failed'
-- Show the result
SELECT * FROM @jobs_failed
Greets
Flo
March 19, 2009 at 7:42 am
Thanks buddy, very helpful!!
Would this be a SP?
As I said, the jobs & job_hist tables will be populated by my SSIS so im just intested in exporting the data from the two tables into jobs_failed
Therefore can I just take out the INSERT statements?
Sorry a bit new to this, as you may tell...lol
March 19, 2009 at 8:08 am
Yes, just take the DELETE (to remove previous information) and INSERT part and rename the example tables to your real names.
You can do this within a procedure or as plain SQL Statement.
Greets
Flo
March 19, 2009 at 8:41 am
Thanks, what does the following line do?
JOIN (SELECT job_id, MAX(curr_date) curr_date
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply