Programatically Send Alert

Question

Programatically Send Alert

kwbonin

SSC Enthusiast

Points: 109
More actions
October 20, 2003 at 7:38 am

#358495

Hello There.
I have a job with two steps. The first step runs a DTS package that imports data from an external data source. The second step is in place to automatically restore the previous nights backup if there is no data in the system after the package runs. If this second step runs I need the job to send an alert to me so I know to check the source of the import. Any ideas of how I can accomplish this? I can't have the job send me a notice based on whether the step runs for it always runs after the import. Its the code that is in the step that I need to be alerted on if that runs.
Any ideas?

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

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

jpipes SSChampion Points: 12230 More actions · Answer 1

You can use xp_sendmail to alert an operator upon a step completing in the stored procedure in step 2. However, just to warn you, xp_sendmail (and SQLMail in general can be a bit of a pain to get working...):

--

http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=17320&FORUM_ID=5&CAT_ID=1&Topic_Title=xp%5Fsendmail+with+%40query+parameter&Forum_Title=Administration

Bill Burkett Old Hand Points: 328 More actions · Answer 2

Hi kwbonin,

I had a similar issue. I load about 90 source files nightly. However, instead of checking the tables after their are loaded, I check for the existance of the source files and their sizes. If the source files exist and have a non-zero size, I import the data.

I have a stored procedure that I use to load the DTS packages using the SQL Agent. This stored procedure checks the source parameters mentioned above. If the source files pass the parameter checks, then the stored procedure launches the DTS package.

I have the code to do this. It is a little too long to post here. If you want it, send me your email address to bill.burkett@ecolabvc.com

kwbonin SSC Enthusiast Points: 109 More actions · Answer 3

Thanks for the responses. I am working through them as we post and will respond with what works and what doesn't work for my situation. You will be hearing from me soon.

Thanks,

Keith

kwbonin SSC Enthusiast Points: 109 More actions · Answer 4

Thanks for the responses. I am working through them as we post and will respond with what works and what doesn't work for my situation. You will be hearing from me soon.

Thanks,

Keith

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 5

if you have e.g. hp-openview monitoring software, you could have your last step raise an error (user defined errormessage / alert) to the windows eventlog, and hp-openview could pick it up.

How hp-openview processes it, is up to you.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me