BUG: Xp_sendmail with Attachment Option Does Not Include All Results in Attachment (317797)

SYMPTOMS

If you run the xp_sendmail extended stored procedure with the @attach_results parameter set to "TRUE", all results may not be in the attachment if:

You call a stored procedure in the @query parameter.

-and-
You use PRINT statements in the stored procedure you call in the @query parameter.

Some of the results may be in the message body instead of the attachment.

WORKAROUND

To work around this behavior, you can use any one of the following methods:

Set the NOCOUNT session parameter to OFF for the stored procedure, and then insert a single PRINT statement to include a line break in the beginning of the body:
```
PRINT '' -- two single quotes, not a double quote
```
The xp_sendmail extended stored procedure includes the PRINT results in the attachment correctly.
Do not call a stored procedure in the @query parameter. Place all the statements directly in the @query parameter instead.

If possible, use SELECT statements instead of PRINT statements. The appearance of the results may look different and dashed lines may be inserted. For example, create a stored procedure in the pubs database and call the stored procedure in the @query parameter of the xp_sendmail extended stored procedure:

USE Pubs
GO

CREATE PROCEDURE dbo.procGetAuthor 
  @mysel NVARCHAR(30)
AS
SET NOCOUNT ON
SELECT '** Date: ' + convert(char(30), getdate(), 109)
SELECT '' As [**********************************************************************]

SELECT au_lname, au_fname
  FROM dbo.authors
  WHERE au_lname like @mysel
GO

EXEC master..xp_sendmail @recipients = 'someone@example.com', 
        @query = 'exec Pubs..procGetAuthor [gr%]',
        @subject = 'SQL Server Report',
        @message = 'The information you requested from Authors table',
        @attach_results = 'TRUE',
        @width = 250

MORE INFORMATION

The problem occurs when SQL Server must send the results of a PRINT statement in a stored procedure as an attachment with xp_sendmail.

Here is an example that uses the Pubs database.

Create a stored procedure with this code:

USE Pubs
GO

CREATE PROCEDURE dbo.procGetAuthor
  @mysel NVARCHAR(30)
AS
PRINT  '** Date: ' + convert(char(30), getdate(), 109)
PRINT  '**********************************************************************'
PRINT  ' '

SELECT au_lname, au_fname
  FROM dbo.authors
  WHERE au_lname like @mysel
GO

Call the stored procedure in the @query parameter of an xp_sendmail statement, and then use the @attach_results option:

EXEC master..xp_sendmail @recipients = 'someone@example.com', 
        @query = 'exec Pubs..procGetAuthor [gr%]',
        @subject = 'SQL Server Report',
        @message = 'The information you requested from Authors table',
        @attach_results = 'TRUE',
        @width = 250

The mail is sent and the first line of the attachment (up to the first line break) is placed in the message body incorrectly instead of in the attachment:

The message body contains:
The information you requested from Authors table** Date: Feb 5 2002 12:34:07:477PM

The attachment contains:

**********************************************************************
 
 au_lname                                 au_fname             
 ---------------------------------------- -------------------- 
 Green                                    Marjorie             
 Greene                                   Morningstar          
 Gringlesby                               Burt