Can DatabaseMail attach a formatted Excel file?

  • I have the following SP that will email me an attachment of the results of a specific query:

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'PROFILE',

    @recipients = 'MY@EMAILADDRESS',

    @query = 'EXEC USP_SP',

    @subject = 'DATA',

    @attach_query_result_as_file = 1,

    @query_result_separator ='' ,

    @query_result_no_padding = 1,

    @query_result_header =0,

    @query_attachment_filename = 'ACCESS.XLS';

    When I open the attachment obviously each data element is separated by a space but all elements are in 1 column. Is there a way to separate each element into its own column?

    Thanks in advance for your help.

    Adam

  • Have you tried playing with the @query_result_separator? Perhaps make it a space or comma instead of an empty string?

    The Redneck DBA

  • Because you are naming the file with an "XLS" extension, Excel thinks it is a native file and is not looking for a delimiter.

    I would use "," for a delimiter and use a "CSV" extension on the file, then Excel will recognize it as a delimited file and because it is a csv, should open it in the correct manner.

  • Jack Corbett (2/29/2008)


    Because you are naming the file with an "XLS" extension, Excel thinks it is a native file and is not looking for a delimiter.

    I would use "," for a delimiter and use a "CSV" extension on the file, then Excel will recognize it as a delimited file and because it is a csv, should open it in the correct manner.

    Thanks Jack. I did what you said and the file is delimited by commas but still all in the same column. I can open it in Excel but I have to go through the process of telling Excel to separate the comma delimited values.

    I'd really like this to be good to go when you open the attachment file.

    Thanks, Adam.

  • Jason Shadonix (2/29/2008)


    Have you tried playing with the @query_result_separator? Perhaps make it a space or comma instead of an empty string?

    I have, and the data rows are still squashed into one column but delimited by a space or other characters (I tried using a "|" but no good either).

    Thanks,

    Adam.

  • I have had similar problems with attaching an Excel (*.cvs) file and having the columns separated correctly.

    We have received a solution that works for us. Use @query_result_separator = ' ' (this is a TAB between the ' ' )). It has worked for all of the problematic emails that we have had.

    I hope it works for you as well.

    thanks,

    Ted

  • great! the tab between the ' ' works perfectly.

    I had long since given up on this thread. Thanks for the solution.

    Adam.

    Ted Walsh (5/27/2008)


    I have had similar problems with attaching an Excel (*.cvs) file and having the columns separated correctly.

    We have received a solution that works for us. Use @query_result_separator = ' ' (this is a TAB between the ' ' )). It has worked for all of the problematic emails that we have had.

    I hope it works for you as well.

    thanks,

    Ted

  • This worked for me too!

  • Me too, thanks!

  • TAB for csv worked perfectly. Thanks!

  • Hi

    As per above suggestions, I tried to use tab as a query separator but still all the query data is coming in the same column..Is there something that I doing wrong??

    EXEC msdb.dbo.sp_send_dbmail

    @recipients=N'cv@xx.co.uk',

    @body= 'Please find attached',

    @subject = 'Test Email from SQL Server',

    @profile_name ='DBMailProfile' ,

    @query = 'exec uat.dbo.csp_Report 1,1',

    @attach_query_result_as_file = 1 ,

    @query_result_separator = ' ',

    @query_result_no_padding = 1,

    @query_result_header =0,

    @query_attachment_filename = 'report.csv';

    Im quite stuck here as all query data is coming as one row without column separation

    Thanks

  • charu.verma (12/1/2009)


    Hi

    As per above suggestions, I tried to use tab as a query separator but still all the query data is coming in the same column..Is there something that I doing wrong??

    EXEC msdb.dbo.sp_send_dbmail

    @recipients=N'cv@xx.co.uk',

    @body= 'Please find attached',

    @subject = 'Test Email from SQL Server',

    @profile_name ='DBMailProfile' ,

    @query = 'exec uat.dbo.csp_Report 1,1',

    @attach_query_result_as_file = 1 ,

    @query_result_separator = ' ',

    @query_result_no_padding = 1,

    @query_result_header =0,

    @query_attachment_filename = 'report.csv';

    Im quite stuck here as all query data is coming as one row without column separation

    Thanks

    Did you make sure that there is a tab space between the quotes?

    When I do it, there is a marked space like ' '

  • Its a 100% tab & not a spcae between quotes..still all data is coming in one column separated by a space,whereas I want each column of the result query to come in a separate column of the excel

  • Maybe this will work?

    @query_result_separator = char(9)

    "When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
    "Robert A. Heinlein"

  • Thanks a lot, TAB resolved my issue!

Viewing 15 posts - 1 through 15 (of 24 total)

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