February 16, 2009 at 5:07 pm
I'm having challenges understanding the @query_result_width parameter of sp_send_dbmail stored procedure. I would like the script to send an Excel csv attachment. My script is as follows:
--Put subject line in variable to make dynamic:
declare @Subject as varchar(100)
set @Subject = 'My file for ' + convert(varchar(12), getdate(), 101)
EXEC msdb.dbo.sp_send_dbmail
@copy_recipients='myEmail@email.com',
@body='Here''s the file...',
@subject=@Subject,
@profile_name='myEmailServer',
@query='exec myStoredProcedure',
@execute_query_database='myDatabase',
@query_result_header=0,
@query_result_separator=',',
@exclude_query_output=1,
@attach_query_result_as_file=1,
@query_attachment_filename='myFile.csv'
Some of the columns are returning with a lot of extra spaces after the data. I've tried using the @query_result_width parameter with various amounts, but it isn't removing the extra spaces. The stored procedure is calling a query similar to this:
select distinct ID,
NAME = replace(replace(company_name, ',', '","'),'"', '""'), --format commas & quotes differently.
ADDRESS = replace(replace(address_1, ',', '","'),'"', '""'), --format commas & quotes differently.
left(postal_code,5) as ZIP
from customer (nolock)
The extra spaces aren't showing up for the ID or ZIP columns. It seems to only show for the NAME & ADDRESS columns that are using the replace option. I've tried taking out the replace option out & it still puts the extra spaces in. I've tried rtrim on the columns & no go.
Anyone have any ideas on what I'm doing wrong? :crazy:
February 17, 2009 at 6:26 am
Are you getting the trailing spaces in the columns on every row or just some of the rows?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 17, 2009 at 9:15 am
I'm getting the trailing spaces in the columns on every row. When doing the query from within Management Studio & doing a File > Save Results As, the data looks fine (no spaces). When the query is executed & sent via DB Mail, the Excel file has the spaces.
February 17, 2009 at 10:06 am
I think this is what you need( from the sp_send_dbmail BOL entry):
[ @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
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 17, 2009 at 10:54 am
That was it! :w00t: Thank you SO MUCH!!!!!
February 18, 2009 at 11:43 am
I have another question about this post. I need to have the file save as a UTF08 character format. Is this possible?
February 18, 2009 at 1:30 pm
I don't see anything in BOL about being able to change the character format.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply