Passing SQL Job Run time to Subject/Body of email notification

Question

Passing SQL Job Run time to Subject/Body of email notification

GLLugo

Old Hand

Points: 397
More actions
June 5, 2009 at 11:01 am

#131635

I'm wondering if anyone knows of an easy way to pass the completion time of a sql job (after completion) to the subject or body of an email notification?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

llugo (6/5/2009)
I'm wondering if anyone knows of an easy way to pass the completion time of a sql job (after completion) to the subject or body of an email notification?

The easy way is to use an operator and send notification to said operator on job completion.

The moderately easy way is to write a script to query job completion time and use database mail if you're looking for granular information. Depends on what your goals are.

GLLugo Old Hand Points: 397 More actions · Answer 2

We are already doing that with the alert/operator function. I am definitely looking for a more granular approach/solution. I know i could run a query against the msdb for the job based on id, and completion time. I'm just curious if I would have to somehow pass that information to the body of the email as a parameter so it can dump it into the appropriate portion of the notification.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

llugo (6/5/2009)
We are already doing that with the alert/operator function. I am definitely looking for a more granular approach/solution. I know i could run a query against the msdb for the job based on id, and completion time. I'm just curious if I would have to somehow pass that information to the body of the email as a parameter so it can dump it into the appropriate portion of the notification.

You sure could ... just like any other procedure out there ... What is it exactly you're looking for? You looking for assistance with how to query the jobs or how to send a database email?

GLLugo Old Hand Points: 397 More actions · Answer 4

I already know what to query in order to get the information I need (completion time for the related job)

however using the code below (standard email notification) I am assuming that I would need to create a variable "@comptime"

declare it and then create the select statement I want to return the completion time and pass it into the body? Am I way off?

USE MASTER

declare @from varchar(100)

,@to varchar(500)

,@sub varchar(500)

,@body varchar(500)

select @from=@@servername

,@to='xxxx@xxx.com;xxxxx@xxxx.com'

--,@sub='Success - Weekend Maintenance'

exec master.dbo.sp_send_cdosysmail @From=@from

,@To=@to

,@Subject=@sub

,@Body=@sub

go

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

Indeed, just populate your variable appropriately ... but I do not really remember how cdosysmails works as I haven't used it in many years. IIRC it's similar to SQLMail in by that, well it stinks. I remember there being issues with using variable stored values.

Are you running SQL2000? I personally like xp_smtpsendmail much more, but if you're 2005 you should be using database mail.