Microsoft KB Archive/281339

= PRB: Xp_sendmail Truncates Column Output to 256 Characters =

Article ID: 281339

Article Last Modified on 11/7/2001

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q281339



SYMPTOMS
If you use the xp_sendmail stored procedure to send a result set to a mail recipient and the result contains a column with more than 256 characters, xp_sendmail truncates the result from the column to 256 characters.

For example, the following code illustrates the behavior: CREATE TABLE northwind..test (col1 SMALLINT, col2 VARCHAR(4000)) go DECLARE @var1 VARCHAR(4000) SET @var1='I am inserting a long string of characters in this column' SELECT datalength(@var1) INSERT INTO test VALUES ( 1, @var1) go EXEC xp_sendmail 'someone@microsoft.com', @query = 'SELECT * FROM northwind..test', @attach_results= 'true' In the resulting e-mail, the results from col2 truncate at 256 characters. This behavior applies to any column that can store more than 256 characters (varchar, char, nvarchar, nchar, text, ntext).



CAUSE
This is a limitation of xp_sendmail because it uses the DB-Library API. If you execute the same query by using ISQL, another DB-Library application, the resulting column output also truncates.

For example, if you execute this on the command line

isql /Sserver1 /Usa /Ppassword -Q&quot;SELECT * FROM northwind..test&quot; -oc:\test.txt

the resulting text file contains the output from col2 (which is varchar[4000]) truncated at 256 characters. However, if you use Osql.exe instead, an ODBC application, the resulting text file contains the correct data length for col2.

The xp_sendmail extended stored procedure uses the DB-Library API and therefore has a maximum of 256 characters per column limitation.



WORKAROUND
To work around this behavior, use the xp_cmdshell extended stored procedure and Osql.exe to execute the query and store the results in a text file. Then, use xp_sendmail to attach that file to an e-mail. For example: exec xp_cmdshell 'osql /Sserver1 /Usa /Ppassword -Q&quot;SELECT * FROM northwind..test&quot; -oc:\test.txt' go

EXEC xp_sendmail @recipients='someone@microsoft.com',@attachments = 'c:\test.txt' The resulting text file and the e-mail have the complete non-truncated output from the varchar(4000) column.

If the query itself is long, you can place the query in an input file because xp_cmdshell has a restriction on the string length it can execute. You can modify the query like this exec xp_cmdshell 'osql /Sserver1 /Usa /P -ic:\inputfile.txt -oc:\test.txt' go

EXEC xp_sendmail @recipients='someone@microsoft.com',@attachments = 'c:\test.txt' where Inputfile.txt contains the query to execute.



MORE INFORMATION
Keywords: kbprb KB281339

-

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

© Microsoft Corporation. All rights reserved.