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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy