AUTOMATING ADMINISTRATION (Page 2)

Testing SQLMail

  • Once configured, you can test SQLMail be sending an email from Microsoft SQL Server 2000 to one of your Exchange Accounts
  • One way of doing this is to use the extended stored procedure xp_sendmail

xp_sendmail

This extended stored procedure sends a message and a query result set attachment to the specified recipients.

Syntax

xp_sendmail {[@recipients =] 'recipients [;...n]'}
[,[@message =] 'message']
[,[@query =] 'query']
[,[@attachments =] attachments]
[,[@copy_recipients =] 'copy_recipients [;...n]'
[,[@blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@subject =] 'subject']
[,[@type =] 'type']
[,[@attach_results =] 'attach_value']
[,[@no_output =] 'output_value']
[,[@no_header =] 'header_value']
[,[@width =] width]
[,[@separator =] 'separator']
[,[@echo_error =] 'echo_value']
[,[@set_user =] 'user']
[,[@dbuse =] 'database']

unnamed-file-224 AUTOMATING ADMINISTRATION
       (Page 2)

Example 1

xp_sendmail "UserName", "Hello"

If this works, you might wish to test the ability to send SQL results from SQL Server and process requested data from an email using sp_processmail. You should now be able to send email messages from any trigger or stored procedure to any valid email account.

Example 2

You can also send the results of an sql statement to someone using the @query = parameter.

xp_sendmail "Fred", @query = "sp_who"

Sending email to several users

In Microsoft Exchange you can create a new distribution list which includes as many mailboxes as you like. You can then use the distribution list as a mail address to send to.

For example:

xp_sendmail 'Users', 'The system will close down at 4:30pm today.
Please log off before this time.'

Set Up Operators in Microsoft SQL Server

Once SQLMail has been set up correctly, you should then register operators in the MSDB database. By doing this you can have Microsoft SQL Server automatically email operators when certain events occur.

To create a new operator

  • Find and expand SQLServerAgent and locate the Operators folder
  • Right-click and choose New Operator

The following dialogue box is displayed.

unnamed-file-226 AUTOMATING ADMINISTRATION
       (Page 2)

  • Fill in the details and click Test to check the email link works
  • Click OK