Sending SQL Server Notifications with CDOSYS

Next, as an example we will create a job that will backup the Northwind database, then send a notification via e-mail when it completes successfully.

In Enterprise Manager, open 'Management' à 'SQL Server Agent', and then select and right-click on 'Jobs', and select 'New Job… '.

On the 'General' tab name the job 'Backup Northwind'.

On the 'Steps' tab, click on 'New', enter the information below, and then click OK. (The path should be a path that exists on the SQL Server.)

This is what you should see after you click 'OK'. Notice that the 'On Success' column says 'Goto next step' (this is ok, because we are about to create another step) and the 'On Failure' column says 'Quit with failure' (this is ok, because if the job fails, we don't want it to go to the next step).

Now we'll add the job step that will notify us that the job has completed.
 

Click 'New…' and add the information below (everything between ' --- ').

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

Name:    SendNotification
Database:  Northwind
Command:   EXEC sp_send_cdosysmail
'from@emailaddress.com*', 
'to@emailaddress.com*',
'subject*',
'I've added an example message for you*
Team,

The Northwind database has been backed up successfully.

Thank you,
ORCS Web '
 

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

*fill in the fields with the desired information.

Click on the 'Advanced' tab and change the 'On Success' box to 'Quit the job reporting success' and then click 'OK'.

This is what the job should look like now.

For our purposes, the 'Schedules' and 'Notifications' tabs default settings are fine; we will not need to make any changes. Click 'OK' and your job should appear in the job window.

Now, right-click on the job we just created and click 'Start Job'.

Make sure that the Job step named 'Backup' is selected then click start. When the job complete, you will be sent an email notification that looks like this.

Now, even though email notifications can be setup as a 'job step,' you can also setup an alert based on a SQL Server event that will send an email and include details of the event. Next we will setup an alert that will send an email notification with details.