Sql server 2008 jobs

Posted on 2013-09-25
Medium Priority
Last Modified: 2014-04-14
Hi i have a job with 4 steps in it.one of the step(step 4) is to send email when a step fails.so for all the steps if it fails it will go to step 4 and send out a email.i am sending email using vbscript.every thing is working fine but if the step fails it is not sending out error message.i want to capture the error msg and send it in email.can you pls let me know how to do it.

Question by:vijay11

Expert Comment

ID: 39522860
First, configure DBMail on your server.  Not sure why you need to send e-mail via VB  Script when you can do it much more easily using sp_senddbmail.
Second, if you want to get a notification when something fails - you do not need an extra step.  Set up an operator, and in the notifications tab of your job settings specify "email operator when the job fails".  Then you can specify all your steps to "Quit the job reporting failure" on fail, and the server will notify you of that.

Author Comment

ID: 39522918
Thanks for reply. but my requirement is to do only with vbscript.I know its lot easier with dbmail but i cannot use that.

LVL 27

Accepted Solution

Zberteoc earned 1500 total points
ID: 39529965
You can execute this query to get the job outcome message from the last execution. It is possible that this will not be there yet for you current execution from which you send your email, in that case you will have to check the execution with this query after the job finished and send email if there is no success message:

select  top 1
	msdb..sysjobs j
	inner join msdb..sysjobhistory h
		on h.job_id=j.job_id
	and h.step_name = '(Job outcome)'
order by
	run_date desc,
	run_time desc

Open in new window


Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question