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;
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.