带附件的数据库电子邮件(excel 文件/pdf 文件)? [英] database email with attachement(excel file / pdf file)?

问题描述

我必须从数据库向我的邮件 ID 发送一封带有附件的邮件.

I've to send a mail from database to my mail-id with attachment.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = '[e-mail]',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

从这段代码我得到了txt文件中的结果.但我想在pdf文件或excel文件中获取查询结果

from this code i'm getting result in txt file. but i want to get query result in pdf file or excel file

任何知道的人请与我分享.

anybody known means please share with me.

提前谢谢！！！:)

推荐答案

这会将您的查询结果附加为 csv，如果您喜欢这种格式，只需将其更改为 filename.xls.我认为 csv 效果更好，使用 excel 您可能会收到一条警告，指出该文件的格式与扩展名指定的格式不同.

This will attach your query result as a csv, just change it to filename.xls if you prefer that format. I think csv works better, with excel you might get a warning saying the file is in a different format than specified by the extension.

我还使用 CHAR(9) 或 TAB 作为查询结果分隔符，您可以将其更改为适合您的任何内容.

Also I used CHAR(9) or TAB for the query result separator, you can change that to whatever works for you.

我还在您的查询中添加了 SET NOCOUNT ON，如果您没有，您将在 excel 文件的末尾得到(1000 行受影响)(无论您的查询返回多少行).

I also added SET NOCOUNT ON to your query, if you don't have that you'll get (1000 rows affected) (whatever number of rows your query returns) at the end of your excel file.

DECLARE
    @tab char(1) = CHAR(9)

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = '[e-mail]',
    @query = 'SET NOCOUNT ON 
              SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
              WHERE DueDate > ''2004-04-30''
              AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1,
    @query_attachment_filename='filename.csv',
    @query_result_separator=@tab,
    @query_result_no_padding=1

这篇关于带附件的数据库电子邮件(excel 文件/pdf 文件)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！