Microsoft KB Archive/317797

= BUG: Xp_sendmail with Attachment Option Does Not Include All Results in Attachment =

Article ID: 317797

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q317797



BUG #: 353742 (SHILOH_BUGS)



SYMPTOMS
If you run the xp_sendmail extended stored procedure with the @attach_results parameter set to &quot;TRUE&quot;, 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.



CAUSE
The xp_sendmail extended stored procedure does not correctly handle the output from the PRINT statements in the stored procedure.



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 



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



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 </li>  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 </li> 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

</li>  The attachment contains: <pre class="fixed_text">********************************************************************** au_lname                                au_fname Green                                   Marjorie Greene                                  Morningstar Gringlesby                              Burt                 </li></ul> </li></ol>

Additional query words: xp_sendmail procedure PRINT attachment @query @attach_results

Keywords: kbbug kbpending KB317797

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.