sqlsql-serversp-send-dbmail

Sending a query results in csv file - file name


I am working on code that will send a results of my query via email. Mostly it works fine but I have a question: Is there any way that I could include a current date in the file name? Instead of having:

@query_attachment_filename = 'report.csv'

I would like to have something like:

report_getdate()\_.csv

I'm using SQL Server Management Studio 2016 Related topic: How to send a query result in CSV format?

My query looks like this and it works well. Just need to change the name of the file

Any help appreciated. Thank you

DECLARE @AccountSite VARCHAR(255)
DECLARE @Query VARCHAR(MAX)
SET @AccountSite = '[sep=   ' + CHAR(13) + CHAR(10) + 'AccountSite]'
SET @Query = 'SELECT
       [AccountSite] ' + @AccountSite + '
      ,[Account Name]
      ,[Title]
      ,[Status]
      ,[Opened Date]
      ,[Closed Date]
      ,[Geo]
      ,[Country]
      ,[Region]
      ,[Marketing Name]
      ,[Case Number]
  FROM [Reporting].[dbo].[Rep]'

EXEC msdb.dbo.sp_send_dbmail  
     @profile_name = 'BI Server'
    ,@recipients = 'xyz@xyz.com'
    ,@query = @Query
    ,@subject = 'Report'
    ,@body = 'Attached is the latest extract of the report'
    ,@query_attachment_filename = 'report.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 = 1
    ,@query_result_width = 32767;

Solution

  • You need to define the report name before calling the SP

    declare @attachment varchar(200) = CONCAT('report_',convert(varchar(25),getdate() ,102),'.csv');
    

    After this, you can replace:

    ,@query_attachment_filename = 'report.csv'
    

    by something like:

    ,@query_attachment_filename = @attachment
    

    The 102 will format the date as YYYY.MM.DD, for other formats see: docs

    NOTE: Some format will contain illegal characters for filenames, like i.e. 101, which formats as mm/dd/yyyy. But the / is not valid within a filename under Windows.