SQL Mail

One of the rarely used utilities tucked away in the SQL Server toolbox is SQL Mail. SQL Mail is a tightly integrated piece of SQL Server that allows you to send mail. The utility allows you to:

  • Send the results of a query or stored procedure using the extended stored procedure xp_send_mail

  • Send string messages

  • Send a message to an alphanumeric e-mail pager

  • Navigate and read mail using a combination of stored procedures

I’ve spent many wasted days trying to learn how to configure SQL Mail. I was frustrated to find that there wasn’t any documentation available for this feature. This is one of the main reasons that this feature goes underutilized. For this reason, I decided to add this section to this book to discuss how to configure SQL Mail and some of the problems you may run into while using it.

Configuring SQL Mail

For SQL Mail to work, you must have an e-mail client, such as Outlook, installed on the SQL Server. You must also configure SQL Mail to use MAPI. This is the main requirement that prevents most DBAs from using this tool. There are as many ways to configure SQL Mail as there are e-mail programs available. For this discussion, we’ll review how to configure SQL Mail using Microsoft Outlook 2000 for e-mail. Another common configuration is to connect to a Microsoft Exchange Server with MAPI to send mail. You can also configure Lotus Notes, as long as you have the MAPI client installed.

To install SQL Mail, log on to Windows with the account that starts the MSSQLServer service. If you have configured the MSSQLServer service to start with the System account, the startup account must be changed to a domain account. Your Windows security profile may by default prevent the account that starts SQL Server from logging on interactively. If this is the case, you’ll need to change this temporarily while you configure SQL Mail. You will have to install a mail program such as Microsoft Outlook and open the program to configure it.

If this is your first time opening Microsoft Outlook 2000, the E-Mail Service Options will appear, which allow you to select Internet Only, or Corporate Or Workgroup. Select the Corporate Or Workgroup option. If you have already configured the mail support and need to reconfigure it, you can open the Outlook options (Tools | Options) and select Reconfigure Mail Support under the Mail Delivery tab.

After you select the type of mail system you want, Outlook asks if you would like to use Microsoft Exchange Server or Internet E-mail. For this example, I’ve chosen Internet E-mail.

Note 

Don’t forget that you’ll have to purchase a license for Outlook. Many site licenses don’t cover servers.

In the next screen, click Select Mail Account and configure the options as you would normally for your mail server. Select a location for the mail files and you’re done. Before you log off, try to send a message manually to ensure that you have proper connectivity.

The SQL Mail configuration itself is much easier. Ensure that you are starting the MSSQLServer service with the account you used to create your mail profile. In Enterprise Manager, right-click SQL Mail in the Support Services group, and choose Properties. From the drop-down box, select the profile that you added earlier. The default profile name that Outlook uses is MS Exchange Settings. You can click Test to have SQL Server start and stop the MAPI profile. After SQL Mail is configured, you can start it manually by using xp_startmail and stop it with xp_stopmail. By default, SQL Mail is started automatically when SQL Server starts.

start sidebar
In the Trenches

A common problem that is reported on newsgroups is that mail never leaves the outbox of the MAPI client. Once the user logs in locally and opens the mail client, the mail is sent. This is a problem with one of the MAPI .dll files, not SQL Mail. The mapi32.dll that is included in Outlook 2000 has fixed this problem. In addition, version 5.5.1960.0 of mapi32.dll will function properly if you have an older version of Outlook.

end sidebar

Note 

If you don’t see any profiles in the drop-down box, or no drop-down box exists at all, the profile has not been configured for the account you use to start the MSSQLServer service.

Tip 

Some programs may set up a MAPI profile on your server and set it to default. You can avoid some problems by keeping the MAPI profile that you use for SQL Mail as the default profile. You can check the profile after it’s created in the Control Panel, under the Mail applet.

xp_sendmail

Once you have SQL Mail configured, you can use extended stored procedures to send and receive messages. The xp_sendmail extended stored procedure allows you to send messages through T-SQL. The following query uses the xp_sendmail stored procedure to send the results of an sp_who query to two administrators through e-mail:

EXEC xp_sendmail @recipients = 'bknight@sqlservercentral.com;admin@sqlservercentral.com', @query = 'sp_who', @subject = 'SQL Server Connections', @message = 'Attached the result of the sp_who query.', @attach_results = 'TRUE', @width = 250

The @attach_results parameter attaches the results of the query to the message. Table 4-1 describes the full list of parameters you can use with xp_sendmail.

Table 4-1: xp_sendmail Parameters

Parameter

Purpose

@@recipients

E-mail addresses to send the message

@@message

Message to send

@@query

Results of this query will be sent in e-mail

@@attachments

Files to attach to message

@@copy_recipients

E-mail addresses to send a copy of the message

@@blind_copy_recipients

E-mail addresses to blind copy the message

@@subject

Subject of the e-mail message

@@type

Type of e-mail message

@@attach_results

When set to true, will attach the results of the query to the message

@@no_output

Doesn't send output to the client

@@no_header

Turns off column headers in your query

@@width

Column width of results

@@separator

Separator for the columns

@@echo_error

Output any errors to message

@@set_user

User that the query will use

@@dbuse

Database used for query

The @message parameter can be a maximum of 7,990 characters since it’s stored as a varchar field. You can use the @query parameter to work around this problem. The following query shows how you can copy the longer message into a temporary table with a text field, and then select it using the @query parameter:

CREATE TABLE ##workaround (tmpcolumn text) INSERT ##workaround values ('Your message here.') DECLARE @cmd varchar(56) SET @cmd = 'SELECT tmpcolumn FROM ##workaround' EXEC master.dbo.xp_sendmail 'bknight@sqlservercentral.com', @query = @cmd, @no_header= 'TRUE' DROP TABLE ##workaround

When sending a large message through xp_sendmail, you may encounter an exception error and SQL Mail will crash (Q166014). Other causes of the problem may be:

  • A row returned by @query is larger than 2000 bytes and is not attached to the message.

  • The query uses a @width parameter larger than 2000.

The requesting client receives the following message when this exception is raised:

Msg 35909, Level 18, State 1 EXCEPTION error encountered in MAPI extended procedures, exception error code = 0xC0000005

The SQL Server error log shows the following:

ods Error : 18009, Severity: 18, State: 1 ods EXCEPTION error encountered in MAPI extended procedures,       exception error code = 0xC0000005 ods Stopped SQL Mail session.

You can fix the problem by attaching the results of your @query using the @attach_results=true parameter, or by setting the @width parameter to a number less than 2000. Also, make sure you’re only running one MAPI program on your server.

Other Mailer Options

You can also use other types of COM mailing programs that don’t depend on MAPI profiles. For example, you can use Microsoft Collaborative Data Objects (CDO), which ships free with Windows NT Option Pack 4 and with Windows 2000. You can use alternative mailers as jobs or in stored procedures using the OLE Automation stored procedures (sp_oa*.*). The following VBScript code can be used in a job to send an SMTP message:

Set M = CreateObject("CDONTS.NewMail") M.To = "user@anydomain.com" M.From = "bknight@sqlservercentral.com" M.Subject = "Type the title of your message here" M.body = "Type your message here." M.Send Set M = nothing

I’ll discuss using ActiveX in jobs in the section “Jobs,” later in this chapter.

Tip 

Many DBAs and network administrators balk at the idea of installing Outlook on a database server. You can also use a very lightweight free extended stored procedure called xp_smtp_ sendmail. This extended stored procedure can be downloaded at http://www.sqldev.net/. I’ve found it a nice quick way to send mail through any SMTP server without MAPI.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net