sp_send_dbmail attachements and column whitespace

Question

Post reply

sp_send_dbmail attachements and column whitespace

shaun.mitchell

Old Hand

Points: 373
More actions
April 24, 2009 at 11:59 am

#131517

Hello,
I'm trying to send an email with the results of a query in a CSV file. Everything works fine except that SQL Server seems to use the tables column width (varchar(128)) and adds whitespace to the end of each column.
Here is my code:
EXEC msdb.dbo.sp_send_dbmail
@recipients = @email_to,
@subject = 'DBM,
@query = @query,
@execute_query_database = 'r',
@query_result_separator = ',',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'DBM.csv',
@body_format = 'TEXT';
The query selects three columns. DECIMAL(10,2), VARCHAR(60), VARCHAR(20).
What I end up getting is a CSV file that looks like this:
1.13,Test ,Abcdefg
2.34,Foo ,Bar
I've tried adding RTRIM to the columns in the query but that doesn't remove the whitespace. Are there any tricks to stop SQL server from adding all that whitespace?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Nick-957131 Default port Points: 1498 More actions · Answer 1

Couple things...

1. What are you expecting as output? and

2. Can you post the query?

shaun.mitchell Old Hand Points: 373 More actions · Answer 2

Query:

DECLARE @query NVARCHAR(max)

SET @query = 'set nocount on; SELECT [weight], [name], [stp] FROM myTable; set nocount off;'

I'm exepcting the results with out all the whitespace at the end of each record.

Nick-957131 Default port Points: 1498 More actions · Answer 3

Assuming your query is returning only the two records you note, the CSV file is accurate.

Each record in your CSV is going to be separated by a carriage return. So each new record starts on a new line... the space at the end of your third column is not necessarily whitspace, but the return.

Does that make sense or am I missing it totally?

shaun.mitchell Old Hand Points: 373 More actions · Answer 4

I hear what you are saying and I agree that at the end of each record there is a carriage return but I'm wondering why after each column there is all that whitespace?

Brendan Kerry SSC-Addicted Points: 471 More actions · Answer 5

try adding the parameter:

@query_result_no_padding = 1

I'm trying to do the same thing, managed to get rid of the pesky white space, but something about the encoding(?) is causing Excel to open each line in one cell rather than separate columns.

If I save as text first I can import it into Excel as expected... hmmm...

DTGrayston Valued Member Points: 65 More actions · Answer 6

Old thread but figured I'd add since its taken some digging to unearth this the *undocumented* value suggested below which works!

Was having the same issue of the query padding spaces for a total of 256 char width when using @attach_query_result_as_file. Our query is returning a single email address per line but that's being padded out to 256chars with spaces.

Trying to set @query_result_width doesn't help. If I set this to like 30 - I end up with multiple blank lines with all the padded spaces!

Adding the @query_results_no_padding=1 cures this padding. But I'm not seeing this documented in BOL (SQL 2008).

Brendan Kerry (5/18/2009)
try adding the parameter:
@query_result_no_padding = 1
I'm trying to do the same thing, managed to get rid of the pesky white space, but something about the encoding(?) is causing Excel to open each line in one cell rather than separate columns.
If I save as text first I can import it into Excel as expected... hmmm...