Database Mail: @query_result_width parameter

  • dba92081

    SSC Eights!

    Points: 934

    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:

  • Jack Corbett

    SSC Guru

    Points: 184371

    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

  • dba92081

    SSC Eights!

    Points: 934

    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.

  • Jack Corbett

    SSC Guru

    Points: 184371

    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

  • dba92081

    SSC Eights!

    Points: 934

    That was it! :w00t: Thank you SO MUCH!!!!!

  • dba92081

    SSC Eights!

    Points: 934

    I have another question about this post. I need to have the file save as a UTF08 character format. Is this possible?

  • Jack Corbett

    SSC Guru

    Points: 184371

    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 7 (of 7 total)

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