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… ‘.

screen shot 2

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.)

screen shot 3

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).

screen shot 4

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.

screen shot 5

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

screen shot 6

This is what the job should look like now.

screen shot 7

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.

screen shot 8

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

screen shot 9

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.

screen shot 10

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.

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |