GrahamR99
asked on
Query SQL DTS sechduled Job History
Is there a way of querying the MSDB database on SQL server for the history of a DTS sechduled Job.
What I would like to do is to query the database and get back if the job was suggestful or unSuggestful and how long it took to run the job.
I relise you can do this within steps in a dts job, but if the server is rebooted the dts job will not do this.
Can any one tell me which tables I need to query and the columns within that table.
Thanxs for Reading
What I would like to do is to query the database and get back if the job was suggestful or unSuggestful and how long it took to run the job.
I relise you can do this within steps in a dts job, but if the server is rebooted the dts job will not do this.
Can any one tell me which tables I need to query and the columns within that table.
Thanxs for Reading
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or you can run
exec msdb..sp_help_job
to use more complicated job logic built in MS procedures (for last job run).
exec msdb..sp_help_job
to use more complicated job logic built in MS procedures (for last job run).
create view viJobs as
select
B.job_id,originating_serve
,owner_sid,notify_level_ev
,notify_level_page,notify_
,delete_level,date_created
,instance_id,A.step_id
,A.step_name AS Hist_step_name
,sql_message_id,sql_severi
,case run_status when 1 then 'OK' when 0 then 'KO' end run_status
,run_date,run_time
,run_duration
,operator_id_emailed,opera
,A.server AS Hist_server
,C.step_name,subsystem,com
,flags,additional_paramete
,on_success_step_id,on_fai
,C.server
,database_name
,database_user_name,retry_
,last_run_outcome,last_run
from sysjobhistory A
join sysjobs B on A.job_id=B.job_id
join sysjobsteps C on A.job_id=C.job_id and A.step_id=C.step_id
GO
select max(name) name,run_date,run_time,MIN
from viJobs
group by job_id,run_date,run_time