none
Using sp_send_dbmail to send the query results in the excel sheet

    Question

  • I am using below code to send the query results in the excel sheet using sp_send_dbmail but the excel format is not good.How to fix the excel format.

    EXEC msdb.dbo.sp_send_dbmail
       @recipients     = 'xxxxxx@xxxxxx.com', 
       @subject        = 'Reports',
       @query = ' SELECT ID,Indicator,EffecDate,ProvNumber,ProvName,ErrorDescription FROM IntegrationDb.dbo.[Report]',
       @attach_query_result_as_file = 1,
       @query_attachment_filename= 'Report.xlsx'
       

    Friday, June 28, 2013 6:10 PM

Answers

  • Hi Deepu982,

    As far as I know, we need to generate the Excel manually and then send it as a mail attachment, for example, we can use SSIS to generate the Excel file. Additionally, we can send the result as “.csv” file with the codes on the following link. 

    How To: Use msdb.dbo.sp_send_dbmail to Email CSV Files
    http://audaxx.net/2012/03/29/how-to-use-msdb-dbo-sp_send_dbmail-to-send-csv-files/

    Allen Li
    TechNet Community Support

    Sunday, June 30, 2013 8:34 AM
    Moderator

All replies

  • Hi Deepu982,

    As far as I know, we need to generate the Excel manually and then send it as a mail attachment, for example, we can use SSIS to generate the Excel file. Additionally, we can send the result as “.csv” file with the codes on the following link. 

    How To: Use msdb.dbo.sp_send_dbmail to Email CSV Files
    http://audaxx.net/2012/03/29/how-to-use-msdb-dbo-sp_send_dbmail-to-send-csv-files/

    Allen Li
    TechNet Community Support

    Sunday, June 30, 2013 8:34 AM
    Moderator
  • Or create a SSRS report based the query and schedule it to EXCEL

    Yos can use this url in report which will export it to excel. http://Server/ReportServer?/Foldername/ReportName&rs:Format=excel 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, June 30, 2013 9:10 AM
    Answerer
  • Hi,

    If you use parameter @query_result_separator  as tab, you'll get proper column formatting in output csv. Also you can consider using @query_result_no_padding= 0, so that you will get 'uncompressed' columns in csv. 

    More bout sp_send_dbmail parameters is here -

    http://technet.microsoft.com/en-us/library/ms190307.aspx

    -Nishad


    Thanks, Nishad

    • Proposed as answer by Dragon_ossie Wednesday, June 25, 2014 12:43 PM
    • Unproposed as answer by Dragon_ossie Wednesday, June 25, 2014 12:43 PM
    Wednesday, March 19, 2014 5:38 AM
  • @query='
    SET NOCOUNT ON;
    select ''sep=;''
    select ''Col1'',''Col2'',''Col3'',''Col3''

    select CONVERT(NVARCHAR,Col1),ISNULL(Col2, ''''),Col3,Col4
    FROM ...
    SET NOCOUNT OFF;
    ',

    --Additional settings
    @query_attachment_filename = '*.csv',
    @query_result_separator = ';',
    @attach_query_result_as_file = 1,
    @query_result_no_padding= 1,
    @exclude_query_output =1,
    @append_query_error = 0,
    @query_result_header =0;
    Wednesday, June 25, 2014 12:44 PM
  • Very clever indeed.

    The intention is to send an attachment with the results and you state @exclude_query_output =1.

    Brilliant!

    Tuesday, September 09, 2014 3:49 PM