SQL SEVER 2008中的数据记录然后通过邮件的的方式或者短信的方式进行发送到人的手机里面或者邮寄里面? 40w2123556]

发布时间:2014-1-1 0:09:46
来源:分享查询网

怎么出发SQL SEVER 2008中的数据记录然后通过邮件的的方式或者短信的方式进行发送到人的手机里面或者邮寄里面?求全解!越详细越好!

参考 发送邮件 短信平台也是类似

过程参考http://www.sqlstudy.com/sql_article.php?id=2008072403

但是表中的数据怎么触发邮件这个问题是主要的大侠!那个邮寄的我会!表什么条件会出发邮寄系统然后通过邮寄的方式或者手机短信的方式发到到我的邮箱里面看操作都都对表操作了什么?

目前我是发到139邮箱,移动收到邮件之后有短信通知,

2008里面的吗?表和邮件服务是怎么连接通过什么服务啊?

用html格式返回作业运行情况,里面的内容可以放你查询的语句: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' ;

返回顶部
查看电脑版