Sql server 2008 jobs

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
vijay11Author Commented:
Thanks for reply. but my requirement is to do only with vbscript.I know its lot easier with dbmail but i cannot use that.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.