Microsoft KB Archive/323477

= PRB: Using the Xp_sendmail Stored Procedure with the @query Parameter Returns Trailing Spaces =

PSS ID Number: 323477

Article Last Modified on 8/8/2002

-

The information in this article applies to:


 * Microsoft SQL Server 2000 (all editions)

-



This article was previously published under Q323477



SYMPTOMS
If you use the @query parameter with the extended store procedure, xp_sendmail, the execution of the xp_sendmail stored procedure may return trailing spaces in the query result.

In SQL Server 2000, if you query against a varchar or text column type, the query result may return a total data length that is padded to 8000 characters.



CAUSE
SQL Server 2000 specifically justifies each column so that all data starts at the same position for every row to make sure that the complete result set is displayed.



WORKAROUND
To work around this behavior, use the rtrim function to return a character string after you truncate all the trailing blanks. The following example uses the xp_sendmail stored procedure to send the query as an e-mail attachment. The query selects the four columns and trims off any trailing spaces. exec master..xp_sendmail @recipients = 'EmailAlias', @subject = 'SQL2K Attachment without trailing space', @query = 'select col1_int, rtrim(col2_varchar800025) + rtrim(col3_varchar80008k) + rtrim(col3_varchar80008k) + convert(char(8000), col4_text)from pubs..TestSQLMail ' , @no_header='TRUE', @attach_results='TRUE'



STATUS
This behavior is by design.



MORE INFORMATION
In SQL Server 2000, you experience this behavior when you use the xp_sendmail stored procedure with the @query parameter. The same behavior exists if you run the xp_sendmail stored procedure with @query parameter in the SQL client tool (OSQL.EXE) and capture the output to a text file.

The following summarizes the expected behavior between SQL Server 7.0 and SQL Server 2000:

Steps to Reproduce the Behavior
  Create a file named SetupRepro.sql to contain the following Transact-SQL commands. This script creates a table named TestSqlMail in the sample database named pubs. It also inserts three sample data records. -- Use the sample database pubs, assumed it is installed on server use pubs go -- Take the following statement out of comment if TestSQLMail already exists -- drop table TestSQLMail go -- Create sample table. Four columns are created. create table TestSQLMail( col1_int int, col2_varchar800025 varchar(8000), col3_varchar80008k varchar(8000), col4_text text ) go -- Insert sample record to table. Use replicate function to repeat duplicate -- text value inserted. For example, replicate('12345',5) creates a text string -- 1234512345123451234512345

insert into TestSQLMail values ( 10,  replicate('12345',5),  replicate('9',200)+replicate('8',50)+replicate('7',5)+replicate('6',10), replicate('a', 5000)+ replicate('b',2990)+replicate('c',20)) insert into TestSQLMail values ( 20,  replicate('12345',5),  replicate('9',200)+replicate('8',50)+replicate('7',5)+replicate('6',10), replicate('d', 5000)+ replicate('e',2990)+replicate('f',20)) insert into TestSQLMail values ( 30,  replicate('12345',5),  replicate('9',200)+replicate('8',50)+replicate('7',5)+replicate('6',10), replicate('g', 5000)+ replicate('h',2990)+replicate('i',20)) go   Create a file named 80Repro.cmd in the same directory as the SetupRepro.sql file from step 1. This step creates a command file that uses OSQL to execute the SetupRepro.sql file that you created in step 1, and to send an e-mail by using the xp_sendmail stored procedure with result from the TestSQLMail table.

echo off

Rem %1 - ServerName Rem %2 - Email Alias

if &quot;%1&quot; == &quot;&quot; goto usage if &quot;%2&quot; == &quot;&quot; goto usage

echo Creating Table in pubs... osql -E -S%1 -d pubs -iSetupRepro.sql -oSetupRepro.out

echo Sending Email With Attachments... osql -E -S%1 -d pubs -Q&quot;exec master..xp_sendmail @recipients = '%2', @subject = 'SQL2K With Attachments', @query = 'select col1_int, col2_varchar800025, col3_varchar80008k,  col4_text  from pubs..TestSQLMail ', @no_header='TRUE' , @attach_results='TRUE'&quot;

echo Sending Email NO Attachments.... osql -E -S%1 -d pubs -Q&quot;exec master..xp_sendmail @recipients = '%2', @subject = 'SQL2K No Attachments ', @query = 'select col1_int, col2_varchar800025,  col3_varchar80008k,  col4_text  from pubs..TestSQLMail ', @no_header='TRUE' , @attach_results='FALSE'&quot;

goto Done


 * usage

echo Repro ServerName EmailAlias


 * Done

  From a command prompt, run the following command to run 80Repro.cmd where  is a valid server name that is running SQL Server 2000 and where   is a valid e-mail address: 80Repro.cmd &quot;Server Name&quot; &quot;E-mail address&quot; 

Additional query words: xp_sendmail @query trailing space text column rtrim

Keywords: kbprb KB323477

Technology: kbAudDeveloper kbSQLServ2000 kbSQLServ2000Search kbSQLServSearch

-

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

© 2003 Microsoft Corporation. All rights reserved.