Email Notification for each steps in MS sql server

Question

Hi Everybody,

i have triggered a job with multiple steps in sql server Agent

is it possible to create email notification for each steps in the job in MS sql server Agent if the steps fail/success ?

Answer 1

I can only think of two ways to do this.

Method A
Set up a step after each existing step in the job to run sp_send_dbmail to your chosen address. Little clumsy, less complicated.

Method B

1. Seperate all your steps into their own jobs, using the existing email notification options built in. At the end of each step, update a table with a step completed indication
2. At the start of each step after step , check the step completed table to see if this job should run if the prior step worked, or quit since the prior step did not finish.

Method A is simpler from a perspective of keeping job history in one place.

Answer 2

is it possible to create email notification for each steps in the job in MS sql server Agent if the steps fail/success ?

Not with build-in functions.
You can add additional steps to send email using sp_send_dbmail (Transact-SQL)

Answer 3

Hi @Nizam Muhammed ,

You can use sp_send_dbmail, for each step set a notification

https://blog.sqlbackupandftp.com/how-to-set-up-sql-server-email-job-notifications#:~:text=To%20set%20up%20an%20email%20notification%20for%20an,that%20is%20when%20you%20want%20to%20be%20alerted.

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.