SQL Mail

Most DBAs have a love-hate relationship with SQL Mail. We often rely upon it for reporting of our server's vital status. At the same time, this very tool has the capability of bringing down our server.

SQL Mail, which is really the procedure XP_SendMail, runs in memory with the SQL Server service, like all other extended stored procedures. This means that if something goes wrong with SQL Mail, its Outlook client, or the Exchange Server that they both depend upon, the server could be in big trouble. Quite often, the problem lies not in SQL Server but with the Exchange Server.

Before we go any further, let's clarify that we've never discovered any good reason for opening up SQL Mail to consume incoming mail from outside the server. Ideally, the data flow should be one-way – from the SQL Server to us. We've heard of (but never met) people who use the SQL Mail system to send messaging to their server, in other words, a kind of quasi-MSMQ. However, if you are going to do this, you might as well implement MSMQ for all the trouble it is worth.

In this section, we will concentrate only on outgoing SQL Mail from the SQL Server.

Best Practice Tips for SQL Mail

Let's now look at the best practices to be followed while setting up the Outlook client:

• Make the service account with the same name, as that of the server – when you get mail, you'll know whom it is from. Also, while you are at it make the password for the service account really difficult.

• Whatever you do, don't auto-archive the Outlook client; we've had to completely rebuild profiles where the auto-archive had kicked in and messed things up.

• Don't have the Outlook client save sent items. Exchange administrators are notorious for setting mailbox size limits and you don't want to push these limits.

• Have the Outlook client only send out plain text messages unless you have an overriding reason to do otherwise.

• Have your Exchange administrators do two things:

  • Add your login to the administrators of the service account mailbox

  • Automatically delete items from the Sent Items folder; even if you don't save sent items, some sent mail items will still be placed in the Sent Items folder

• After you set up the profile, close Outlook. Then go in and copy the existing profile. Give the new copy the same name as the service account and the SQL server instance. Then delete the original profile with its cumbersome name. Use this new profile when setting up SQL Mail. We'll talk more about this later.

In our discussion regarding SQL Mail, we will look at:

• XP_SendMail recursion

• Ensuring that our XP_SendMail calls don't fail

• Replacing XP_SendMail with Gert Drapers' XP_SMTP_SendMail procedure

XP_SendMail Recursion

Here, the word ‘recursion’ means that when we use XP_SendMail to send out mail by running a query, we will have the procedure first call XP_SendMail by setting an @sendmail flag on. The @query parameter of the XP_Sendmail command is the procedure itself; however, with the @sendmail flag off.

The nice thing about this is that for quick testing, we can run the procedure with the @sendmail flag off to just run the query. It is a nice set of packaging. This is the way we do it:

     CREATE PROCEDURE ourjob$notification       (@sendmail TINYI NT = 0,        @recipients VARCHAR(255) = NULL       )     AS     -- check to see if we need to keep on going     IF NOT EXISTS (query to count rows to send off and determine if       notification is needed)     RETURN 0     DECLARE @query VARCHAR(8000)     IF @sendmail = 1 -- we're going to send mail     BEGIN       -- check to make sure a good recipient was sent in       IF @recipients IS NULL       ...Handle the error and RETURN -1       -- recursively call itself with a @sendmail flag of 0 to go to the       -- query portion of the procedure       SELECT @query = EXEC ourjob$notification, 0, NULL       Specify the subject and any other message you want to add;       Specify whether the query will be attached or included       -- Call the XP_SENDMAIL proc       EXEC master.dbo.xp_sendmail @recipients, @subject = @subject,         @message = @message, @query = @query, @attachment = 'true',         @width = 2000, @no_output = 'true'     -- make sure that the width is sufficient and @no_output is true     END     ELSE -- @sendmail = 0;     BEGIN       Here you put the query logic to return a single record set     END     GO 

Here, we'll let you fill in your query. Another good trick is to get into variables the maximum lengths, such as @maxlenColName, of your VARCHAR columns in your result sets. Then run an sp_executesql proc to have the final results sent in formatted fashion and run a Left function across the column, Left(column,@maxlen).

By putting the query through sp_executesql, the final output of your attachment will be formatted with the contents of @maxlenColName, instead of the full listed length of the VARCHAR column. This makes for a much prettier attachment.

XP_SendMail Bullet-proofing

What is the number one reason for Send Mail failures? It happens when the SQL Server is up and running a MAPI connection to an Exchange Server. Then, unbeknown to the SQL Server, the Exchange Server goes away for whatever reason. A piece of mail is sent with the XP_SendMail procedure; so far, it's not a problem. However, when the second piece of mail is sent to the nonexistent Exchange Server, most probably we will have a full scale MAPI failure, which can often only be cleared by a restart of the SQL Server.

The key word in the previous paragraph is "unbeknown". If we could examine the availability of the Exchange server before firing the XP_SendMail procedure, it would remove the uncertainty. With the undocumented xp_test_mapi_profile system stored procedure, we can precisely do this. Microsoft uses this procedure to test Send Mail in the SQL Enterprise Manager GUI.

This xp_test_mapi_profile system stored procedure takes a single input parameter, sysname of type NVARCHAR(128), which is the profile name of the Outlook client for the SQL Server service account. By default, this profile name is somewhat cumbersome. However, if you had followed our last best practice tip, your profile's name is the same name as the server. So, just run the following script snippet prior to running xp_sendmail:

     DECLARE @rc int, @profilename sysname     SET @profilename = @@servername     -- If you profile name isn't your server name, you need to adjust     EXEC @rc = master.dbo.xp_test_mapi_profile @profilename     IF @rc = 0       Run XP_SENDMAIL ELSE --- you have an error condition with your MAPI Server       Try running a Net Send to one of your DBA's with XP_CMDSHELL 

Breaking the Exchange Umbilical Cord – XP_SMTP_SendMail

All DBAs owe an eternal debt of gratitude to Gert Drapers. While Gert didn't write this under the auspices of Microsoft as an officially supported procedure, we think that it has the equivalence of functionality that you would find in the SQL Server resource kit. In fact, we are told that many SQL Servers inside Microsoft already use this stored procedure.

You can find XP_SMTP_SendMail for free download at http://sqldev.net/xp/xpsmtp.htm. Follow the instructions provided on this site for downloading and installing it.

To implement it on our servers, we first turn on the SMTP service for our SQL Server and then add the 127.0.0.1 IP address in the allowed relay addresses. It should be the only address. Then we went into the HOSTS file (it's located in the \System32\Drivers\Etc directory) and added a line for a host called STARHOST with the 127.0.0.1 address.

In XP_SMTP_SendMail procedure, STARHOST is the default relay agent. This means that the procedure will look for an SMTP server to relay the mail through. With the 127.0.0.1 address, it will always find itself. Even if it doesn't have network access, the mail will queue up as a .EML file.

Although it doesn't have all the functionality of XP_SendMail, XP_SMTP_SendMail is quite promising. Most noticeably, at the time of this writing, query functionality had not been made available within the product.

However, we've written a wrapper procedure around the XP_SMTP_SendMail. The wrapper has all the same input parameters, as the existing SQL 2000 XP_SendMail. In order to run a query, it writes a query script file out to C:\Temp with a DATETIME name. Then it uses ISQL to run the script back against itself and places the .OUT file with the same DATETIME name back in C:\Temp. As long as everything else works, the two files are later cleaned up. If something doesn't work, the files will remain undeleted for later analysis.

On some servers, particularly those outside the firewall and in contact with a MAPI server, we have ended up renaming the existing XP_SendMail and instead called our wrapper stored procedure as XP_SendMail. Next, we turn off SQL Agent Mail and add failure mail steps to all our jobs and success mail steps if we want to be notified of the successful completion of a job. We also write alert jobs that correspond to each of the alerts that we want to get mail about.

Below is our wrapper script for XP_SMTP_SENDMAIL:

     CREATE PROCEDURE xp_sendmail       (@recipients VARCHAR(8000) = NULL,        @message VARCHAR(8000) = NULL,        @query VARCHAR(8000) = NULL,        @attachments VARCHAR(8000) = NULL,        @copy_recipients VARCHAR( 8000) = NULL,        @blind_copy_recipients VARCHAR(8000) = NULL,        @subject VARCHAR(8000) = NULL,        @type VARCHAR(255) = NULL,        @attach_results VARCHAR(5) = 'FALSE',        @no_output VARCHAR (5) = 'FALSE',        @no_header VARCHAR(5) = 'FALSE',        @width INT = 80,        @separator CHAR(1) = ' ',        @echo_error VARCHAR(5) = 'FALSE',        @set_user VARCHAR(255) = NULL,        @dbuse SYSNAME = 'MASTER'       )     /*     Purpose: Replaces xp_sendmail with a same name wrapper around     xp_smtp_sendmail procedure; use when Exchange is not availble.     Version: 1.0     Author: C.Hawkins*/     AS     SET NOCOUNT ON 

These are the declarations for out script:

     DECLARE @cmd VARCHAR(4000),       @PRIORITY NVARCHAR(10),       @SERVER NVARCHAR(4000),       @RC INT,       @messagefile VARCHAR(8000),       @outfile VARCHAR(255),       @infile VARCHAR(255),       @marker VARCHAR(255) 

We have to make sure that we have to have a recipient for the mail:

     IF @recipients IS NULL     BEGIN       RAISERROR('No Recipient was given.',16,-1)       RETURN -1     END     -- Check to see if there is a query to run     IF @query IS NOT NULL     BEGIN -- @query IS NOT NULL       -- set up an ISQL outfile; form of outfile is       -- ISQLYYYYMMDDHHMISSMMM.OUT       SELECT @marker = 'c:\temp\ ISQL'+REPLACE(convert(varchar(32),         GETDATE(),102),'.',") +         REPLACE(CONVERT(VARCHAR(32),GETDATE(),114),':',")       SELECT @infile = @marker + '.sql'       SELECT @outfile = @marker + '.out'       -- write the query out to the infile because it makes it easier to       -- deal with single quotes       SELECT @cmd = 'echo '+@query+ ' >'+@infile       EXEC @RC = master.dbo.xp_cmdshell @cmd,no_output       IF @RC <> 0       BEGIN         RAISERROR('Error writing @infile for xp_sendmail',16,-1)         RETURN -2       END -- @query IS NOT NULL 

Then we may need to specify the ISQL path; here, we hardcode it for simplicity and then execute the command line:

       -- build the ISQL command line       SELECT @cmd = '"C:\Program Files\ Microsoft SQL Server\80 \Tools\ Binn\                      isql.exe" -E -n -b -d '+@dbuse+' -t 600 -h'+                      CASE WHEN @no_header = 'TRUE' THEN '-1' ELSE ' 0' END                      + ' -w'+CONVERT(VARCHAR(10),@width)+ CASE WHEN                      LTRIM(@separator) <> '' THEN ' -s '+@separator ELSE                      " END + CASE WHEN @echo_error = 'TRUE' THEN ' -e'                      ELSE " END + ' -i '+ @infile+ ' -o '+@outfile       -- Execute the ISQL command line       EXEC @RC = master.dbo.xp_cmdshell @cmd,no_output       IF @RC <> 0       BEGIN         RAISERROR('Error running ISQL for @query',16,-1)         RETURN -3       END 

If attach_results is false, the output file will be the message file:

       IF @attach_results = 'FALSE'       BEGIN         SELECT @messagefile = @outfile         IF @attachments IS NULL         BEGIN           SELECT @attachments = ''         END       END       -- The output file will be one of the attachments       ELSE       BEGIN         SELECT @messagefile = ''         IF @attachments IS NULL         BEGIN           SELECT @attachments = @outfile         END         ELSE         BEGIN           SELECT @attachments = @attachments + ';' + @outfile         END       END     END 

Then, we ensure that we have got the other necessary details right:

     IF @message IS NULL SELECT @message = ''     IF @query IS NULL SELECT @query = ''     IF @attachments IS NULL SELECT @attachments = ''     IF @copy_recipients IS NULL SELECT @copy_recipients = ''     IF @blind_copy_recipients IS NULL SELECT @blind_copy_recipients = ''     IF @subject IS NULL SELECT @subject = '' 

Finally, we send the mail:

     EXEC @rc = master.dbo.xp_smtp_sendmail     @FROM = @@servername,     @TO = @recipients,     @CC = @copy_recipients,     @BCC = @blind_copy_recipients,     @priority = NORMAL,     @subject = @subject,     @message = @message,     @messagefile = @messagefile,     @type = 'text/plain',     @attachments = @attachments     IF @RC <> 0     BEGIN       RAISERROR('Error sending mail for xp_smtp_sendmail',16,-1)       RETURN -4     END     -- temporary: set @marker to NULL to save the files; comment out in     -- production.     -- select @marker = NULL     -- clean up the temp files from @query     IF @marker IS NOT NULL     BEGIN       SELECT @cmd = 'del '+@marker+'.* /Q'       EXEC master.dbo.xp_cmdshell @cmd,no_output     END     GO