Sending a query results in csv file - file name

Question

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 = '[email protected]'
    ,@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;

Just define your filename as a variable first and use that instead of a string literal. — Stu
– Stu, Commented Mar 29, 2022 at 8:26
The file name can be what ever you want it to be (provided it's a valid file name), this is why there's a parameter for it: @query_attachment_filename. You are explicitly providing the value 'report.csv' so that's the name the file gets. Just supply different value, like you are with @query. — Thom A
– Thom A ♦, Commented Mar 29, 2022 at 8:31
Aside... you're sending a file with a .csv extension but sp_send_dbmail cannot generate a CSV file in any accepted sense of the definition, particularly with respect to RFC4180 Common Format and MIME Type for Comma-Separated Values (CSV) Files. Try sending data containing ,, " or linebreak characters and see how badly it fails. Consider external tools capable generating proper CSV files like SSRS (at least since SQL Server 2014 anyways) and PowerShell. — AlwaysLearning
– AlwaysLearning, Commented Apr 5, 2022 at 10:45

Luuk · Accepted Answer · 2022-03-29 09:07:50Z

0

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.

edited Mar 29, 2022 at 9:07

answered Mar 29, 2022 at 8:47

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Luuk Over a year ago

I did edit my answer, based on the comment from @DaleK

Bartosz K Over a year ago

I`ts working. Thank you so much guys!

Collectives™ on Stack Overflow

Sending a query results in csv file - file name

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related