请问，如何把时实变化数据的报表，每天以邮件形式发送给某人？谢谢！

Question

就是报表内容实时随数据库内容改变，可以做到吗？

Answer 1

一般数据库都有大量的改动，你这就算搭建好说不定一天之内你邮箱就爆了

Answer 2

呵呵，抱歉 我可能没说清楚，我是想每天某一时刻，只对当时某一些查询结果发送一次邮件，但确实是当时的实时数据。

Answer 3

这是发送作业运行情况的脚本，你改一下吧。需要开启数据库邮件功能。然后放到作业里面定时运行DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @date SMALLDATETIME
SET @date = CONVERT(CHAR(10), GETDATE(), 120)
DECLARE @title NVARCHAR(64)
SELECT  @title = CONVERT(CHAR(10), GETDATE(), 120) + '  Jobs Report '
DECLARE @DBA NVARCHAR(1024)
SET @DBA = '[email protected];[email protected]'--收件人邮箱SET @tableHTML = N'<H1>Jobs Report</H1>' + N'<table border="1">'
    + N'<tr><th>作业名</th><th>最近执行时间</th>'
    + N'<th>最近执行状态</th><th>运行持续时间</th><th>最近运行状态信息</th>'
    + N'<th>下次运行时间</th></tr>'
    + CAST(( SELECT td = [sJOB].[name] ,
                    '' ,
                    td = CASE WHEN ( [sJOBH].[run_date] IS NULL
                                     OR [sJOBH].[run_time] IS NULL
                                   ) THEN NULL
                              ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8))
                                   + ' ' + STUFF(STUFF(RIGHT('000000'
                                                             + CAST([sJOBH].[run_time] AS VARCHAR(6)),
                                                             6), 3, 0, ':'), 6,
                                                 0, ':') AS DATETIME)
                         END ,
                    '' ,
                    td = CASE [sJOBH].[run_status]
                           WHEN 0 THEN N'失败'
                           WHEN 1 THEN N'成功'
                           WHEN 2 THEN N'重试'
                           WHEN 3 THEN N'取消'
                           WHEN 4 THEN N'正在运行' -- In Progress
                         END ,
                    '' ,
                    td = STUFF(STUFF(RIGHT('000000'
                                           + CAST([sJOBH].[run_duration] AS VARCHAR(6)),
                                           6), 3, 0, ':'), 6, 0, ':') ,
                    '' ,
                    td=[sJOBH].[message] ,
                    '' ,
                    td = CASE [sJOBSCH].[NextRunDate]
                           WHEN 0 THEN NULL
                           ELSE CAST(CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                                + ' ' + STUFF(STUFF(RIGHT('000000'
                                                          + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),
                                                          6), 3, 0, ':'), 6, 0,
                                              ':') AS DATETIME)
                         END,''
             FROM   [msdb].[dbo].[sysjobs] AS [sJOB]
                    LEFT JOIN ( SELECT  [job_id] ,
                                        MIN([next_run_date]) AS [NextRunDate] ,
                                        MIN([next_run_time]) AS [NextRunTime]
                                FROM    [msdb].[dbo].[sysjobschedules]
                                GROUP BY [job_id]
                              ) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
                    LEFT JOIN ( SELECT  [job_id] ,
                                        [run_date] ,
                                        [run_time] ,
                                        [run_status] ,
                                        [run_duration] ,
                                        [message] ,
                                        ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber
                                FROM    [msdb].[dbo].[sysjobhistory]
                                WHERE   [step_id] = 0
                              ) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id]
                                              AND [sJOBH].[RowNumber] = 1
             ORDER BY [sJOBH].[run_status] ,[sJOB].[name] 
           FOR
             XML PATH('tr') ,
                 TYPE
           ) AS NVARCHAR(MAX)) + N'</table>'EXEC msdb.dbo.sp_send_dbmail @recipients = @DBA, @subject = @title,
    @body = @tableHTML, @body_format = 'HTML' ;