Modify Jobs to Use SQL Mail
Before a server reaches a state that limits its operability, you should perform backups—not only on all user databases but also on system databases. Whether you back up these databases through a maintenance plan or manually, you can configure SQL Mail to notify an operator of the backup's success or failure. However, configuring databases to notify operators about a backup's success is overly aggressive. The DBA can become accustomed to seeing successful backup emails and overlook a failure message. Email that arrives only upon failure receives the necessary attention.

After you configure SQL Server to use email, you can easily modify backup jobs and maintenance plans to use SQL Mail. One way to modify a maintenance plan is through the Maintenance Plan Wizard. To access the Maintenance Plan Wizard from Enterprise Manager, select a server, then expand the Databases folder. In the Database Information window, select the database you want to use the wizard for and select New Maintenance Plan. Or, to modify an existing plan, in Enterprise Manager select the server you want to modify a plan for and select the Management folder, then select the Database Maintenance Plans to view the server's existing plans. Double-click the plan name and select the Reporting tab. On the Reporting tab, you can add an operator to receive an email message about any of the wizard's operations. To add new operators, select New.

The steps to run a backup are similar with or without a maintenance plan. Without a maintenance plan, right-click the Backup icon and select Backup a Database to set up the job. To run a scheduled backup by using Enterprise Manager, select the Management folder, expand the SQL Server Agent, and click the Jobs option. You should see the scheduled backup job you're looking for. To edit the job, double-click the job name, select the Notifications tab (which Figure 3 shows), click E-mail operator, then select the operator (e.g., dba) that you want to receive the email. To choose the relative time to send the email, select When the job fails, When the job succeeds, or When the job has completed.

SQL Mail and Stored Procedures
The SQL Mail function has more applications than just administration. You can also use the xp_sendmail extended stored procedure in triggers, stored procedures, and batch commands to incorporate the mail function into code. The xp_sendmail extended stored procedure is in the master database, but with the proper syntax, you can call it from any database. The proper syntax for calling xp_sendmail from another database is

master.dbo.xp_sendmail

followed by any of the input parameters in Table 1 (a list of these parameters is also available in SQL Server Books Online—BOL).

You should be aware of the following rules when using the xp_sendmail extended stored procedure:

  • You must configure SQL Mail sessions before you use xp_sendmail.
  • Only one user can execute xp_sendmail at a time. If you execute long-running queries, be aware that other connections must wait until the first connection has completed processing.
  • Xp_sendmail queries are subject to locks and blocks just like any other query.
  • Xp_sendmail's Execute permissions default to members of sysadmin, but you can grant these permissions to other users.

Using xp_sendmail from SQL Server with the proper planning about when and who should receive these email messages can simplify administration and troubleshooting.

The xp_sendmail extended stored procedure makes life easier for developers and DBAs. For example, let's say the Get-Big Health Club's salespeople must get only basic information from new clients. Other departments gather more in-depth information about accounting or prior health status. But how do other departments know when they need to gather this information about a new client? One solution is to set up a job that executes every night and searches for any missing accounting or health information. The job then sends the results to the appropriate departments for follow-up. A report could notify these departments of new clients every 24 hours to ensure that the data is complete.

Another possible solution is to set up a trigger on the table into which the salespeople enter the customer information. The trigger could then recognize any new records in the table regardless of the source, then send email immediately to the appropriate department. One thing to keep in mind if you use xp_sendmail in a trigger is that it can't refer to the logical inserted and deleted tables. Instead, you might need to refer to a datetime field or search for missing data.

Xp_sendmail can notify a specific operator if an automated task fails. For example, suppose the accounting department runs reports at night because the Get-Big Health club is open 24 * 7 but the majority of database traffic is during daylight hours. Some tables won't change every day. But because the club populates these tables every night, the report programs might not be able to tell that a failure occurred. You could add a conditional branch in the stored procedure to populate the table if the @@error system variable returns anything but 0. Then, the stored procedure could send an email message to the operator responsible for troubleshooting the reports, as the following example shows:

IF @@error <>0
  BEGIN
    Master..xp_sendmail @recipients =
	  "responsible@get-big.com",
      @message = "Report of new clients failed.",
      @subject = "Failure"
END

Such a solution could let the reporting department know in the morning that the data population failed and needs attention.

Prev. page     1 [2] 3     next page



You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

When our Exchange Server is rebooted for any reason, I have stop and start SQL Agent on all of my servers. Is there anyway to avoid this.

Richard Peterkin- June 16, 2004

 
 

ADS BY GOOGLE