sql 中怎么把数据弄成报表进行邮件发送
怎么样把一个表的数据弄成表格的形式，
发送给另外一个表中放有邮件地址的邮箱
[最优解释]
1.配置数据库邮件
2.调用数据库邮件 (取出收件人地址，组织表数据<table><tr><td>....</tr></table>做为邮件内容，邮件标题随意)
[其他解释]
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 = 'XXX@XXX.com;XXX@139.com'
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' ;
[其他解释]
配置完了，试发送，如果标题是中文的话，以？？？？、显示的，这个要怎么解决
[其他解释]
use msdb
Go
declare @strHtml NVARCHAR(MAX) ;
set @strHtml='<table border=1 >'+
'<tr><td>idno</td><td>name</td><td>deptnm</td></tr>'+
CAST( (select td=idno,'',
td=deptnm,'',
td=deptcd,''
from db2.dbo.wlx
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) )+
N'</table>' ;
Exec dbo.sp_send_dbmail @profile_name='MailForHr',
@recipients='youxian@gmail.com;ceshi@163.com',
@subject=N'测试',
@body=@strHtml,
@body_format='HTML'
Go
这样可以发邮件，如果 @recipients不写死，
有另外的表中记录一些邮箱，得怎么弄才能给这些邮箱发邮件
[其他解释]
这个懂了，但如果@recipients 是要从一个表中读的，要怎么写?
[其他解释]
我没考虑那么多，我是直接把这个放到作业里面，每天发送到我自己的邮箱。这个是把SQLServer每天作业的运行情况发送一个邮件而已。这样我不用每天登录很多服务器，只需要看邮件就知道服务器是否有问题了。
[其他解释]
恩，我现在是要根据不同的人发不现的内容，人对应的邮件在一个表中，要从表中读取
[其他解释]
那可能要搞几个组，发送给不同的组，而不要发送给个人，不然改动很大
[其他解释]
DECLARE @UserId varchar(150) ;
DECLARE @Sentmail varchar(500) ;
set @Sentmail='';
DECLARE My_Cursor CURSOR
FOR (SELECT mailnm FROM webshow.dbo.mail)
OPEN My_Cursor;
FETCH NEXT FROM My_Cursor INTO @UserId;
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @UserId;
select @Sentmail=@Sentmail+';'+@UserId;
FETCH NEXT FROM My_Cursor INTO @UserId;
END
CLOSE My_Cursor;
DEALLOCATE My_Cursor;
declare @strHtml NVARCHAR(MAX) ;
set @strHtml='<table border=1 >'+
'<tr><td>idno</td><td>name</td><td>deptnm</td></tr>'+
CAST( (select td=idno,'',
td=deptnm,'',
td=deptcd,''
from db2.dbo.wlx
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) )+
N'</table>' ;
Exec dbo.sp_send_dbmail @profile_name='MailForHr',
@recipients=@Sentmail,
@subject=N'测试',
@body=@strHtml,
@body_format='HTML'
Go
给表中对应邮箱发邮件