想要执行sp_send_dbmail并以excel格式发送结果 [英] Want to execute sp_send_dbmail and send results in excel format

问题描述

我想执行sp_send_dbmail，并以excel格式通过电子邮件发送结果.我已经尝试过.csv和.txt，但是结果没有按整齐的列组织.

I want to execute sp_send_dbmail and have results emailed in excel format. I have tried .csv and .txt but the results are not organized in neat columns.

这是我的代码

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail_profile',
@recipients = '[e-mail]',
@subject = 'Test',
@query = N'EXEC DatabaseTest.dbo.test',
@attach_query_result_as_file = 1,
@query_result_width = 150,
@query_result_header= 1,
@query_attachment_filename = 'test.txt',
@importance = 'High',
@query_result_no_padding = 1

也尝试过

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail_profile',
@recipients = '[e-mail]',
@subject = 'Test',
@query = N'EXEC DatabaseTest.dbo.test',
@attach_query_result_as_file = 1,
@query_result_width = 150,
@query_result_header= 1,
@query_attachment_filename = 'test.csv',
@importance = 'High',
@query_result_no_padding = 1

我希望我的结果采用整洁的格式.我的某些参数设置不正确吗?还是有一种方法可以将附件作为.xls或.xlsx文件?我可能解释不正确，我可以回答任何问题

I would like my results to be in a clean organized format. Do I have some of the parameters set incorrectly? Or is there a way to have attachment as .xls or .xlsx file? I may be explaining incorrectly, I can answer any questions

这是我从.csv中获得的信息

Here is what I get back from .csv

EventDate FirstName LastName Department DeptCode OpCode Start Finish ElapsedTime Units UPH

---

2017-05-30 My Name Flat LL PP 07:00:00 07:15:00 00:15:00 700 2800
2017-05-31 His Name Maintenance FK PR 08:00:00 08:30:00 00:30:00 100 200
2017-05-30 Her Name Hang SD OC 12:32:00 14:31:00 01:59:00 23 12

当我有@query_attachment_file ='test.xlsx'时，附件与.csv提供的附件相同.

When I have @query_attachment_file = 'test.xlsx' the attachment is identical to what the .csv provides.

我想看到附件

2017-05-30  My   Name   Flat        LL  PP  07:00:00    07:15:00    00:15:00    700 2800
2017-05-31  His  Name   Maintenance FK  PR  08:00:00    08:30:00    00:30:00    100 200
2017-05-30  Her  Name   Hang        SD  OC  12:32:00    14:31:00    01:59:00    23  12

存储过程.dbo.test看起来像这样

Stored Procedure .dbo.test looks like this

DECLARE 
@Now DATETIME,
@EndReportDate DATETIME,
@StartReportDate DATETIME,
@StartTime VARCHAR(16) ,
@EndTime VARCHAR(16)

SET @Now = GETDATE()
SET @EndReportDate = DATEADD(dd, DATEDIFF(dd, 0, @Now), -1)
SET @StartReportDate = DATEADD(dd, DATEDIFF(dd, 0, @Now), -9)
SET @StartTime = '01:00:00AM'
SET @EndTime = '11:59:00PM' 

SELECT 
TimeLog.EventDate AS 'EventDate'
 ,AssociateInfo.FirstName 'FirstName'
 ,AssociateInfo.LastName 'LastName'
 ,AssociateInfo.Department 'Department'
 ,TimeLog.DeptCode 'DeptCode'
 ,TimeLog.OpCode 'OpCode'
 ,TimeLog.StartTime 'Start'
 ,TimeLog.FinishTime 'Finish'
 ,ElapsedTime = convert(time(0),dateadd(second,datediff(second,StartTime,FinishTime),0)) 
,TimeLog.Units
,UPH = cast(isnull(Units / nullif(datediff(minute,StartTime,FinishTime)*1.0,0),0.0) *60 as decimal(10,0))
FROM dbo.TimeLog INNER JOIN dbo.AssociateInfo WITH (NOLOCK)
ON AssociateInfo.ID = TimeLog.ID

WHERE EventDate BETWEEN @StartReportDate + ' ' + @StartTime
                   AND     @EndReportDate + ' ' + @EndTime

ORDER BY  UPH DESC  

推荐答案

将此TAB用作结果分隔符.

try this TAB as the result separator.:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail_profile',
@recipients = '[e-mail]',
@subject = 'Test',
@query = N'EXEC DatabaseTest.dbo.test',
@attach_query_result_as_file = 1,
@query_result_width = 150,
@query_result_header= 1,
@query_attachment_filename = 'test.txt',
@importance = 'High',
@query_result_no_padding = 1,
@query_result_separator = '     '

这篇关于想要执行sp_send_dbmail并以excel格式发送结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！