August 7, 2003 at 1:19 pm
I have what should always be a small record set of 8 columns from a query that I run,
I would like to return this result set into one variable. This one variable is then passed to the body section of a lotus notes email.
Creating an output file and attaching the results is not an option.
thanks
John Zacharkan
John Zacharkan
August 7, 2003 at 1:46 pm
What type variable can be passed to your notes mail?
August 7, 2003 at 1:54 pm
It uses extended sprocs, here's an example of the code
DECLARE @EMAIL_ID INT
DECLARE @EMAIL_FROM_1 [varchar](128)
DECLARE @EMAIL_TO_2 [varchar](256)
DECLARE @EMAIL_SUBJECT_5 [varchar](200)
DECLARE @EMAIL_BODY_6 [varchar] (1500)
DECLARE @EMAIL_CC_3 [varchar](256)
DECLARE @EMAIL_BCC_4 [varchar](256)
DECLARE @object int
DECLARE @hr int
set @email_from_1 = 'myfromemail@email.com'
set @EMAIL_TO_2 = 'mytoemail@email.com'
set @EMAIL_SUBJECT_5 = 'YOur Subject here'
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
EXEC @hr = sp_OASetProperty @object, 'From', @EMAIL_FROM_1
EXEC @hr = sp_OASetProperty @object, 'To', @EMAIL_TO_2
EXEC @hr = sp_OASetProperty @object, 'Body', @EMAIL_BODY_6
EXEC @hr = sp_OASetProperty @object, 'Subject', @EMAIL_SUBJECT_5
EXEC @hr = sp_OAMethod @object, 'Send', NULL
IF @hr <> 0
BEGIN
RAISERROR( 'Unable to send email! From=%s, To=%s, Subject = %s.', 16, 1, @EMAIL_FROM_1, @EMAIL_TO_2, @EMAIL_SUBJECT_5)
EXEC sp_OAGetErrorInfo @object, @hr
END
EXEC @hr = sp_OADestroy @object
I'm want to popluate @EMAIL_BODY_6 with a record set of failed jobs and long running jobs derived from syshistory and sysjobs I have a sproc that returns what I need I just need a simple way of email/paging me the result set when the return value <>0.
John Zacharkan
John Zacharkan
August 7, 2003 at 2:00 pm
Here's a copy of the sp that generates the result set
Create procedure test_for_job_failure
as
set nocount on
declare @num_days int
declare @first_day datetime
,@last_day datetime
declare @first_num int
if @num_days is null
set @num_days=30
set @last_day = getdate()
set @first_day = dateadd(dd, -@num_days, @last_day)
select @first_num= cast(year(@first_day) as char(4))
+replicate('0',2-len(month(@first_day)))+ cast(month(@first_day) as varchar(2))
+replicate('0',2-len(day(@first_day)))+ cast(day(@first_day) as varchar(2))
select
h.instance_id,
h.job_id,
j.name,
h.step_id,
h.step_name, --extra
h.sql_message_id,--extra
h.sql_severity,--extra
h.run_status,--extra
'run_date'= cast(h.run_date as varchar(8)),
'run_time'= replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),
'run_datetime' = left(cast(h.run_date as varchar(8)),4)+'/'
+substring(cast(h.run_date as varchar(8)),5,2)+'/'
+right(cast(h.run_date as varchar(8)),2)+' '
+left(replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),2)+':'
+substring(replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),3,2)+':'
+right(replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),2),
run_duration = cast(h.run_duration as varchar(20)),
run_duration_conv = case
when (len(cast(h.run_duration as varchar(20))) < 3)
then cast(h.run_duration as varchar(6))
WHEN (len(cast(h.run_duration as varchar(20))) = 3)
then LEFT(cast(h.run_duration as varchar(6)),1) * 60 --min
+ RIGHT(cast(h.run_duration as varchar(6)),2) --sec
WHEN (len(cast(h.run_duration as varchar(20))) = 4)
then LEFT(cast(h.run_duration as varchar(6)),2) * 60 --min
+ RIGHT(cast(h.run_duration as varchar(6)),2) --sec
WHEN (len(cast(h.run_duration as varchar(20))) >= 5)
then (Left(cast(h.run_duration as varchar(20)),len(h.run_duration)-4)) * 3600 --hour
+(substring(cast(h.run_duration as varchar(20)) , len(h.run_duration)-3, 2)) * 60--min
+ Right(cast(h.run_duration as varchar(20)) , 2)--sec
end,
h.retries_attempted,
h.server
into #temp_jobhistory
from msdb..sysjobhistory h, msdb..sysjobs j
where h.job_id=j.job_id
and h.run_date >= @first_num
and h.step_id=0
and j.enabled = 1
select j.job_id
,j.name
,'Sampling'=(select count(*) from #temp_jobhistory h where h.job_id=j.job_id)
,'fromRunDate' = (select min(run_date) from #temp_jobhistory h where h.job_id=j.job_id)
,'run_duration_max'=(select max(run_duration_conv) from #temp_jobhistory h where h.job_id=j.job_id)
,'run_duration_min'=(select min(run_duration_conv) from #temp_jobhistory h where h.job_id=j.job_id)
,'run_duration_avg'=(select avg(run_duration_conv) from #temp_jobhistory h where h.job_id=j.job_id)
,'Last_RunDate'=(select max(run_datetime) from #temp_jobhistory h where h.job_id=j.job_id)
,'Last_RunStatus'= null --(select run_status from #temp_jobhistory h where h.job_id=j.job_id)
,'Last_RunDuration'=null
into #temp_runhistory
from msdb..sysjobs j
where j.enabled = 1
update #temp_runhistory
set Last_RunStatus = j.run_status
,Last_RunDuration=j.run_duration_conv
from #temp_jobhistory j
where #temp_runhistory.job_id=j.job_id
and #temp_runhistory.Last_RunDate=j.run_datetime
and j.run_datetime=(select max(run_datetime) from #temp_jobhistory j1
where j1.job_id=#temp_runhistory.job_id)
if exists (select Last_runstatus from #temp_runhistory where Last_RunStatus in (0,2,3)
or (Last_Runduration -10 > run_duration_avg) )
begin
select name as 'Job Name',
Last_RunDate,
'Last_RunStatus'=case Last_RunStatus
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
end,
'Last_RunDuration'= cast(Last_RunDuration/3600 as varchar(10))
+':'+replicate('0',2-len((Last_RunDuration % 3600)/60))+cast((Last_RunDuration % 3600)/60 as varchar(2))
+':'+replicate('0',2-len((Last_RunDuration % 3600) %60))+cast((Last_RunDuration % 3600)%60 as varchar(2)),
'Avg Duration (hh:mm:ss)' = cast(run_duration_avg/3600 as varchar(10))
+':'+replicate('0',2-len((run_duration_avg % 3600)/60))+cast((run_duration_avg % 3600)/60 as varchar(2))
+':'+replicate('0',2-len((run_duration_avg % 3600) %60))+cast((run_duration_avg % 3600)%60 as varchar(2)),
'Max Duration (hh:mm:ss)' = cast(run_duration_max/3600 as varchar(10))
+':'+replicate('0',2-len((run_duration_max % 3600)/60))+cast((run_duration_max % 3600)/60 as varchar(2))
+':'+replicate('0',2-len((run_duration_max % 3600) %60))+cast((run_duration_max % 3600)%60 as varchar(2)),
'Min Duration (hh:mm:ss)' = cast(run_duration_min/3600 as varchar(10))
+':'+replicate('0',2-len((run_duration_min % 3600)/60))+cast((run_duration_min % 3600)/60 as varchar(2))
+':'+replicate('0',2-len((run_duration_min % 3600) %60))+cast((run_duration_min % 3600)%60 as varchar(2)),
fromRunDate as 'From Date'
,Sampling
from #temp_runhistory where Last_RunStatus in (0,2,3) or (Last_Runduration -10 > run_duration_avg)
end
drop table #temp_runhistory
drop table #temp_jobhistory
John Zacharkan
John Zacharkan
August 8, 2003 at 12:19 am
Hi John,
for the Body of an email is nothing more than a text, why can't you simply collect the information about failed jobs, loop through them, concatenate into a say varchar(xxx) and assign to the mails' body value?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 8, 2003 at 7:45 am
Definitely a solution, and I have thought of it. I was looking to replicate what is done with SQL-Mail. Somehow it captures a select anything, multi record set without having to concatenate the data. But your solution does work.
Zach
quote:
Hi John,for the Body of an email is nothing more than a text, why can't you simply collect the information about failed jobs, loop through them, concatenate into a say varchar(xxx) and assign to the mails' body value?
Cheers,
Frank
John Zacharkan
John Zacharkan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply