March 24, 2009 at 6:38 am
Hi Pals,
Need help in figuring out the problem.
I created a job monitoring table and monitoring stored procedure
which would give more meaningfull info about Success and Failed jobs.
What i am doing is, once i execute any job no matter it is successfully executed i.e run_status = 1 or failed i.e run_status = 0 in sysjobhistory table , i am going to log an entry into the RUNNING_JOBS monitoring table by manually invoking the usp_monitorjobs() stored which would picks the data from the JOB Related System tables
msdb..syscategories
msdb..sysjobsteps
msdb..sysjobhistory
msdb..sysjobs
Here is the point where i am getting an issue here, the logic which was written below is working fine.
I cannot see any log entries in RUNNING_JOBS Table , if the job is created dynamically. But if i have created a job from the management studio and execute that job than i could see a record in my RUNNING JOBS table. What could be the reason behind this.
Am Also pasting an example for a job which is created dynmaically created while executing a stored procedure.
Any comments would be greatly appreciated!!
-- Meta Data Table
Create table RUNNING_JOBS (
id int identity (1,1) Primary key,
job_id uniqueidentifier,
job_step_id int,
job_nm sysname,
step_nm varchar(128),
last_run_date datetime,
duration int,
servername varchar(50),
category_name varchar(100)
)
----------------------------------------------------------------
-- Script to create Job Monitoring Stored Procedure
----------------------------------------------------------------
CREATE PROCEDURE dbo.usp_monitorjobs
as
BEGIN
declare @servername varchar (30)
SELECT @servername = @@servername
DECLARE @ActiveJobs table (
tid int identity(1,1),
job_id uniqueidentifier,
job_step_id int,
job_nm varchar(128),
step_nm varchar(128),
last_run_date datetime,
duration int,
category_name varchar(100))
insert into @ActiveJobs
( job_id ,
job_step_id,
job_nm,
step_nm,
last_run_date,
duration,
category_name
)
select sj.job_id,
sjs.step_id,
sj.name,
sjs.step_name,
CAST
( Left(cast(run_date as varchar(8)),4) + '/' + substring(cast(run_date as varchar(8)), 5,2) + '/' +
Right(cast(run_date as varchar(8)), 2) + ' ' +
cast( ((run_time/10000) %100) as varchar ) + ':' + cast( ((run_time/100) %100) as varchar ) + ':' +
cast( (run_time %100) as varchar ) as datetime),
( run_duration % 100 )+ -- seconds
(((run_duration/100) % 100 ) * 60) + -- minutes in seconds
(((run_duration/10000) % 100 ) * 3600) duration, -- hours in seconds
(select name from msdb..syscategories where category_id = sj.category_id ) category_name
from msdb..sysjobs sj inner join msdb..sysjobsteps sjs on sjs.job_id = sj.job_id
inner join msdb..sysjobhistory sjh on sjh.job_id = sjs.job_id and sjh.step_id = sjs.step_id
where sjh.run_status IN (0,1) -- run_status 0-Failed, 1-Success
insert into RUNNING_JOBS
( job_id ,
job_step_id,
job_nm,
step_nm,
last_run_date,
duration,
servername,
category_name )
select job_id ,
job_step_id,
job_nm,
step_nm,
last_run_date,
duration,
@servername,
category_name
from @ActiveJobs
END
go
Once these table and stored procedure is created. i am going to manually call the stored procedure which would create a job dynamically by assigning a job name.
Here is the code.
ALTER procedure [dbo].[usp_StartDataLoads001]
as
Begin
declare @jid uniqueidentifier
declare @cmd varchar(4000)
SET @cmd = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "E:\demo1.dtsx" '
print @cmd
declare @jname varchar(128)
set @jname = cast(newid() as char(36)) -- Here is the place where we are assigning a jobname dynamically
-- Create job
exec msdb.dbo.sp_add_job
@job_name = @jname,
@enabled = 1,
@category_name = ' ',
@delete_level = 1,
@job_id = @jid OUTPUT
exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name = ' '
exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = 'ExecutePackage0001',
@subsystem = 'CMDEXEC',
@proxy_name = ' ',
@command = @cmd
-- Start job
exec msdb.dbo.sp_start_job
@job_id = @jid
End
go
/* Basically am just calling an SSIS PACKAGE inside my job */
-- Execute the job
EXEC [usp_StartDataLoads001]
Case 1
Strange thing is, If i execute this stored procedure which inturns creates a job and call a SSIS package and assume if the package has run successfully,
and invoke the job monitoring stored procedure i.e
EXEC usp_monitorjobs
i cannot see any entry/record in RUNNING_JOBS table. Why is that so. Any Thoughts?
Case 2
But if the package failed as a result the job has failed, then if execute the monitoring stored procedure
EXEC usp_monitorjobs
I am able to see a entry inside my RUNNING_JOBS table.
Case 3
But if I create the job using Management Studio i.e GUI, then irrespective of package success/failure , i can
see record entries in my RUNNING_JOBS table.
I dont know why is it so?
Does the job name makes any difference?
Pl help me out in figuring out.
Thanks in advance!
March 24, 2009 at 7:31 am
we do something similar except we have a sp that goes out to every sql server and looks in msdb for anything that failed in the last 24 hours and then sends a report.
PM me an i'll email it to you. too big to post here
March 26, 2009 at 2:34 am
Hi Guys,
I will make my requirement more narrow.
Is there will be an entry for dynamically created job inside my stored procedure ? If anyone can answer this question. This would solve my procedure. But if , the dynamically job fails , then i can clearly see an entry the SQL Server Agent system tables. But i also need the status for Package Success flag.
Please help me out.
CREATE procedure [dbo].[usp_StartDataLoads001]
as
Begin
declare @jid uniqueidentifier
declare @cmd varchar(4000)
SET @cmd = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "E:\demo1.dtsx" '
print @cmd
/* Here is the place where 36-char job name is dynamically created */
declare @jname varchar(128)
set @jname = cast(newid() as char(36))
SELECT 'Jobname : '+@jname
-- Create job
exec msdb.dbo.sp_add_job
@job_name = @jname,
@enabled = 1,
@category_name = 'SRM',
@delete_level = 1,
@job_id = @jid OUTPUT
exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name = 'ED-DESE-ID-TRAI'
exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = 'ExecutePackage0001',
@subsystem = 'CMDEXEC',
@proxy_name = 'ESP_proxy',
@command = @cmd
-- Start job
exec msdb.dbo.sp_start_job
@job_id = @jid
End
-- Execute the stored to load data into coredatamirror
usp_StartDataLoads001
Jobname : 474543AA-D43D-46CE-B0AE-064F4B95FA75
-- Query to find the Job executions Success - Failure
-- Unable to see the entry for Dynamically created jobs
-- only when there is a failure in the Job an Enrty has been made
-- to MSDB Database
select sj.job_id,
sjs.step_id,
sj.name,
sjs.step_name,
(select name from msdb..syscategories where category_id = sj.category_id ) category_name
from msdb..sysjobs sj inner join msdb..sysjobsteps sjs on sjs.job_id = sj.job_id
inner join msdb..sysjobhistory sjh on sjh.job_id = sjs.job_id and sjh.step_id = sjs.step_id
where sjh.run_status IN (0,1) -- run_status 0-Failed, 1-Success
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply