gpt4 book ai didi

sql - 想要执行sp_send_dbmail并以excel格式发送结果

转载 作者:行者123 更新时间:2023-12-02 16:39:16 25 4
gpt4 key购买 nike

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

这是我的代码

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail_profile',
@recipients = 'myaddress@me.com',
@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 = 'myaddress@me.com',
@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 文件?我可能解释不正确,我可以回答任何问题

这是我从 .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 提供的内容相同。

我想看到这样的附件

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看起来像这样

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

最佳答案

尝试使用此选项卡作为结果分隔符:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail_profile',
@recipients = 'myaddress@me.com',
@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 = ' '

关于sql - 想要执行sp_send_dbmail并以excel格式发送结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/44315299/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com