May 24, 2013 at 9:33 am
Hello,
I need to create a report with all jobs that run to populate the tables that failed and then also report all sprocs effected by that ETL run
Jobid Table_Name Stored Procedure
I can get the list of jobids and table names for the report. But how do i pass these table names which failed to load to get the stored procedure.
Any ideas please.
May 24, 2013 at 12:50 pm
These are not my scripts but I have tested and they might give you a start..
This script will give you the last status of a job, whether success or failure which you could combine with the second script that enables you to search for a string inside the job steps.
In my test I looked for EXEC, obviously used to fire a sproc so you could combine the queries to find all job steps containing EXEC where the job appears as failed in the first query perhaps?
--Show Job Statuses, alter query for just failed if necessary
SELECT TOP 20
SJ.name 'JOB Name'
,'Run date : ' +
REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,run_date)),102),'.','-')+' '+
SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),1,2)+':'+
SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),3,2)+':'+
SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),5,2) 'Start Date Time'
,SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),1,2)+':'+
SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),3,2)+':'+
SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),5,2) 'Duration'
,CASE run_status WHEN 1 THEN '1-SUCCESS' WHEN 0 THEN '0-FAILED' ELSE CONVERT(varchar,run_status) END AS 'Status'
,Step_id
,[Message]
,[Server]
FROM MSDB..SysJobHistory SJH
RIGHT JOIN MSDB..SysJobs SJ
ON SJ.Job_Id = SJH.job_id
--WHERE SJ.name LIKE '%<<job name>>%'
--AND Step_ID = 0 --Comments this line if you want to see the status of each step of the job
ORDER BY run_date DESC, run_time DESC, step_ID DESC
Script 2
DECLARE
@SearchString varchar(255),
@IsEnabled bit;
SET @SearchString = 'exec'; -- Enter Search String Here. Leave Blank for All
SET @IsEnabled = 1; -- 0 = Disabled, 1 = Enabled, 2 = All
SELECT
j.Name JobName,
j.Description JobDescription,
js.step_id StepID,
js.step_name StepName,
js.database_name DatabaseName,
js.command StepCommand
FROM
msdb..sysjobs j
INNER JOIN
msdb..sysjobsteps js ON
j.job_id = js.job_id
WHERE
(j.enabled = @IsEnabled OR @IsEnabled = 2) AND
js.command LIKE '%' + @SearchString + '%'
ORDER BY
j.Name,
js.step_id;
'Only he who wanders finds new paths'
May 26, 2013 at 1:53 pm
Sorry if i was not clear. There is table with job and job status , so i can see with single query which jobs failed and with the job ids i can decode which tables are effected with that job. So if that jobid failed to load these tables, i would like to know which sprocs use these tables so i know that if a user calls i can tell that the tables are not loaded for these particular stored procedures.
Thanks
MAdhavi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply