sp_send_dbmail not sending multiple rows from the query in the message

Question

I am switching some code from xp_sendmail, but am not getting the results I expect from sp_send_dbmail. The query I am trying is below, and the message body in the email only contains one line:

My first header entry

Did Microsoft really design this to only return answers from single row queries (e.g. COUNT(*) or TOP 1)? Is there a way to get the entire set of rows as the text?

DECLARE @tLog TABLE (Sequence INT IDENTITY(1,1), [Rank] TINYINT DEFAULT 1, Line VARCHAR(8000))
INSERT INTO @tLog ([Line]) VALUES ('My first log entry')
INSERT INTO @tLog ([Line]) VALUES ('My first second entry')
/* ... processing ... */
INSERT INTO @tLog ([Rank], [Line]) VALUES (0, 'My first header entry')
/* Global Temp for visibility in sp_send_dbmail */
SELECT Sequence, [Rank], Line 
INTO ##tResult1
FROM @tLog 
ORDER BY [Rank], Sequence
/* to validate table content only */
SELECT Sequence, [Rank], Line 
FROM ##tResult1
ORDER BY [Rank], Sequence
EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'MyProfile',
    @recipients='me@myaddress.net',
    @subject = 'test',
    @body_format = 'text',
    @body = '',
    @query_result_header = 0,
    @query_no_truncate = 1,
    @query = 'SELECT Line FROM ##tResult1 ORDER BY [Rank], Sequence'

DROP TABLE ##tResult1

score 0 · Answer 1 · answered Sep 18 '12 at 04:25

When looking at the message body further, the rows are there, but the answer is filling the column to its defined length, not the length of its content. So in this case, the second row is 8000 characters down (several lines).

The answer is to somehow shorten the columns with the width parameter, and shorter columns. I've made several tries using RTRIM(...) wrapping the Line column in the query's select statement, but it still pads the column to the defined size.

score 0 · Answer 2 · answered Sep 18 '12 at 06:47

0

Since an RTRIM() may return its input value unchanged, the result of RTRIM() is the same size as its input - a varchar(8000) in this case.

So when the system is trying to format the result set into an email, it consults the metadata, sees that it's varchar(8000) and then formats all of the results according to that size.

I'd suggest including the results as an attachment, rather than directly in the body, using @attach_query_result_as_file=1.

answered Sep 18 '12 at 06:47

Damien_The_Unbeliever

234,701
27
340
448

the idea is to not have this result appear in an attachment. In Outlook, reading the log stream requires extra clicks to read the entries from the table. In fact, the table is designed with Rank, so I can INSERT summary rows at the end of the table, which appear at the top when the query results appear in the message. It's all about speed. Thanks for the info on RTRIM(), which I didn't know. I think you gave me the catalyst for the solution I found (see below). – John J Schultz Sep 18 '12 at 13:44
The only way I can get this to reasonably work is to add the parameter query_result_width = 32767, which is longer than the max length of 8,000 for the column. Also, I prepended 'SET NOCOUNT ON; ' to the value in the @query argument, to eliminate the (#) rows returned in the answer. The problem is that I can get a message that is 52K with only three short lines in the body. Even changing the query argument to this does not help: 'SET NOCOUNT ON; SELECT [Line] = RTRIM([Line]) FROM ##tResult1 ORDER BY [Rank], Sequence'. This is poor behavior: I am going to open a ticket with Microsoft. – John J Schultz Sep 18 '12 at 14:04
@JohnJSchultz - it's not really a bug - the "shape" of the query is that it consists of lines, each of which is up to 8000 characters long, and it's trying to accommodate that. If you know that *all* of the lines are significantly shorter, why don't you change the table definition? – Damien_The_Unbeliever Sep 18 '12 at 14:17
LOL: Occam's Razor strikes again. If I change the definition of the Line column from VARCHAR(8000) to VARCHAR(MAX), it works just fine with no padding. I still need to specify the argument query_result_width with 8000 or more, to accommodate the lines over 256 without line breaks. There is no RTRIM(...) needed when MAX is used. – John J Schultz Sep 18 '12 at 14:32

sp_send_dbmail not sending multiple rows from the query in the message

2 Answers2