在CSV文件 - 文件名中发送查询结果

发布于 2025-01-17 11:16:08 字数 1510 浏览 3 评论 0 原文

我正在处理代码，该代码将通过电子邮件发送我的查询结果。通常它可以正常工作，但是我有一个问题：有什么办法可以在文件名中包含当前日期？而不是：

@query_attachment_filename = 'report.csv'

我想拥有类似的东西：

report_getdate()\_.csv

我正在使用SQL Server Management Studio 2016 相关主题：如何以CSV格式发送CSV格式？<<< /a>

我的查询看起来像这样，而且效果很好。只需更改文件的名称，

任何帮助即可。谢谢

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;

原文

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;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

提笔落墨 2025-01-24 11:16:08

您需要在调用 SP 之前定义报告名称

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

。在此之后，您可以将：替换

,@query_attachment_filename = 'report.csv'

为：

,@query_attachment_filename = @attachment

102 会将日期格式设置为 YYYY.MM.DD，例如其他格式请参阅：docs

注意：某些格式的文件名会包含非法字符，例如 ie 101，其格式为 mm/dd/yyyy。但 / 在 Windows 下的文件名中无效。

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.