Remove extra spaces in sp_send_dbmail

Question

I have the following table:

CREATE TABLE [dbo].[MyTable](
    [Day_ID] [nvarchar](50) NULL,
    [SAS] [nvarchar](50) NULL,
    [STAMP] [datetime] NULL DEFAULT (getdate())
)

which contains this data:

'2017_12_06_01','Red'
'2017_12_06_02','Yellow'
'2017_12_06_03','Green'
'2017_12_06_04','Blue'

I also have a SP which read all the data from MyTable and send it in the body of an e-mail message.

SET NOCOUNT ON;

    EXEC msdb.dbo.sp_send_dbmail 
        @profile_name = 'MyMailProfile'
        ,@recipients = 'me@account.com'
        ,@subject = 'This is the object'
        ,@body = 'This is the body:'
        ,@body_format = 'TEXT'
        ,@query = 'SET NOCOUNT ON select [Day_ID],[SAS] from MyTable SET NOCOUNT OFF'
        ,@attach_query_result_as_file = 0
        ,@query_result_separator = '|'
        ,@exclude_query_output = 1
        ,@query_result_no_padding = 0
        ,@query_result_header = 0
        ,@append_query_error=1

Everything works but my problem is that in the body the results appear like these:

2017_12_06_01                                   |Red                                                  
2017_12_06_02                                   |Yellow                                               
2017_12_06_03                                   |Green                                                
2017_12_06_04                                   |Blue

In other words SQL Server know that the columns could be 50 characters long so they fill the space that doesn't contain characters with spaces. This is very boring if you consider that happen also if you write numeric values into a column, for example, NUMERIC. I've tried with LTRIM and RTRIM but nothing changed.

I am using SQL Server 2005.

score 5 · Accepted Answer · answered Dec 06 '17 at 08:16

The parameter @query_result_no_padding should allow you to do this.

Change it from:

@query_result_no_padding = 0

to

@query_result_no_padding = 1

[ @query_result_no_padding ] @query_result_no_padding The type is bit. The default is 0. When you set to 1, the query results are not padded, possibly reducing the file size.If you set @query_result_no_padding to 1 and you set the @query_result_width parameter, the @query_result_no_padding parameter overwrites the @query_result_width parameter. + In this case no error occurs. If you set the @query_result_no_padding to 1 and you set the @query_no_truncate parameter, an error is raised.

Source: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql

Perfect! Thank you very much! – Nicolaesse Dec 06 '17 at 08:54 — Nicolaesse, Dec 06 '17 at 08:54

score 2 · Answer 2 · answered Dec 06 '17 at 08:13

You need to run two queries - first to compute the actual lengths, the second to retrieve the data for the email. Obviously, this can have a high cost.

Something like:

declare @maxDay int
declare @maxSAS int

select @maxDay = MAX(LEN(RTRIM(Day_ID))),@maxSAS = MAX(LEN(RTRIM(SAS))) from MyTable

declare @sql varchar(max)
set @sql = 'SET NOCOUNT ON select CONVERT(nvarchar(' + CONVERT(varchar(10),@maxDay) +
           '),[Day_ID]) as Day_ID,CONVERT(nvarchar(' + CONVERT(varchar(10),@maxSAS) +
           '),[SAS]) as SAS from MyTable SET NOCOUNT OFF'

And then use @sql in you sp_send_dbmail call.

I've tried with LTRIM and RTRIM but nothing changed.

It wouldn't. Each column in a result set has a single fixed type. The input to your e.g. RTRIM() calls is an nvarchar(50). Without knowing the contents of all rows, what possible data type can SQL Server derive for the output from that RTRIM() using expression? It can only still be nvarchar(50) and so that's still the type of the column in the result set. For all the server nows, there could be a row containing 50 non-whitespace characters and the result still needs to show that.

Remove extra spaces in sp_send_dbmail

2 Answers2