January 11, 2008 at 2:41 am
Hi Guys,
Please could you shed some light?
I am using a cursor to access my Jobs, and print into a workable format/report
I require it to be run and return results for a specific Time/Hour, and need to include
A space between the Job Names, as there are a lot of Steps in each Job,
I there any way to include spaces after each job name.
Thanks
January 11, 2008 at 3:53 am
Could you post your code please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2008 at 6:26 am
Hi There,
here is the code.
Thanks
use msdb
go
declare
@job_idvarchar (100),
@namevarchar(100),
@Step_IDvarchar(25),
@Start_Step_IDvarchar(25),
@Step_Namevarchar(100),
@Last_Run_Durationvarchar(25),
@Last_Run_Datevarchar(25),
@Last_Run_Timevarchar(25),
@Next_Run_Datevarchar(25),
@Next_Run_Timevarchar(25),
@Supportvarchar(25),
@messagevarchar(8000)
declare ND_Cur cursor for
select distinct j.job_id,
j.name as Name,
j.start_step_id as Start_Step_ID,
jst.step_id as Step_ID,
jst.step_name as Step_Name,
jst.last_run_duration as Last_Run_Duration,
convert(varchar(10),jst.last_run_date, 20)[Last_Run_Date],
case len(jst.last_run_time)
when 5 then
'0'+ substring(convert(varchar,jst.last_run_time),1,1) +':'+
substring(convert(varchar,jst.last_run_time),2,2)
when 4 then
'00:' +
substring(convert(varchar,jst.last_run_time),1,2)
when 3 then
'00:0' +
substring(convert(varchar,jst.last_run_time),1,1)
when 1 then
'00:0' +
substring(convert(varchar,jst.last_run_time),1,1)
else substring(convert(varchar,jst.last_run_time),1,2) +':'+
substring(convert(varchar,jst.last_run_time),3,2) end
as [Last_Run_Time],
convert(varchar(10),js.next_run_date, 20) [Next_Run_Date],
case len(js.next_run_time)
when 5 then
'0'+ substring(convert(varchar,js.next_run_time),1,1) +':'+
substring(convert(varchar,js.next_run_time),2,2)
when 4 then
'00:' +
substring(convert(varchar,js.next_run_time),1,2)
when 3 then
'00:0' +
substring(convert(varchar,js.next_run_time),1,1)
when 1 then
'00:0' +
substring(convert(varchar,js.next_run_time),1,1)
else substring(convert(varchar,js.next_run_time),1,2) +':'+
substring(convert(varchar,js.next_run_time),3,2) end
as [Next_Run_Time],
' ' [Support]
from sysjobs j with (NOLOCK)
left join sysjobschedules js
on (j.job_id = js.job_id)
left join sysjobservers jsv
on (j.job_id = jsv.job_id)
left join sysjobsteps jst
on (j.job_id = jst.job_id)
where left(name, 1) like '#'
--and [Next_Run_Time] between getdate() and (getdate() - 0.042)
order by [Next_Run_Time]
open ND_Cur
fetch next from ND_Cur
into @job_id, @name, @Start_Step_ID, @Step_ID, @Step_Name,@Last_Run_Duration,@Last_Run_Date,@Last_Run_Time,@Next_Run_Date,@Next_Run_Time,@Support
while @@FETCH_STATUS = 0
BEGIN
SELECT @message = 'Job:' +@name+ '|' +@Start_Step_ID+ '|' +@Step_ID+ '|' +@Step_Name+ '|' +@Last_Run_Duration+ '|' +@Last_Run_Date+ '|' +@Last_Run_Time+ '|'
+@Next_Run_Date+ '|' +@Next_Run_Time+ '|' +@Support
print @message
--if @name <> @name PRINT ' '
fetch next from ND_Cur
into @job_id, @name, @Start_Step_ID, @Step_ID, @Step_Name,@Last_Run_Duration,@Last_Run_Date,@Last_Run_Time,@Next_Run_Date,@Next_Run_Time,@Support
end
close ND_Cur
deallocate ND_Cur
GO
January 11, 2008 at 6:34 am
If I understand you correctly, you want to have a linefeed after each Job information; if so, then simply use char(10) + char(13).
Paul
January 11, 2008 at 9:35 pm
This would be a good simple example for you to practice set based programming on... convert the cursor to set based.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply