PRB: Xp_sendmail Truncates Column Output to 256 Characters (281339)

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)<BR/>
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"SELECT * FROM northwind..test" -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"SELECT * FROM northwind..test" -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.

PRB: Xp_sendmail Truncates Column Output to 256 Characters (281339)

SYMPTOMS

CAUSE

WORKAROUND

MORE INFORMATION