用SQL SERVER 发送HTML邮件 怎么设置table中的边框
set @strHtml=N'<h3>'+@mday+N'参考表</h3>'+'<table style=border-collapse:collapse; width:100%;font-size:18px;>'+
N'<tr style=border-bottom:#000 solid 2px;><th style=background:#cee4fc;border:#ccc solid 1px; >部门</th><th style=background:#cee4fc;border:#ccc solid 1px; >人数</th><th style=background:#cee4fc;border-right:#000 solid 2px;>平均</th><th style=background:#cee4fc>20hr↓</th><th style=background:#cee4fc>20~36hr</th><th style=background:#cee4fc>36~45hr</th><th style=background:#cee4fc>45~55hr</th><th style=background:#cee4fc>55~65hr</th><th style=background:#cee4fc>65~80hr</th><th style=background:#cee4fc>80~100hr</th><th style=background:#cee4fc>100hr↑</th></tr>'+
CAST( (select
case when len(buildid)>4 then 'font-size:14px;border:#ccc solid 1px;text-align:right;' else 'font-size:14px;border:#ccc solid 1px;color:blue;font-weight:bold;' end as "td/@style",
td=deptnm,'',
case when len(buildid)>4 then 'font-size:14px;border:#ccc solid 1px;' else 'font-size:14px;border:#ccc solid 1px;color:blue;font-weight:bold;' end as "td/@style",
td=numshow,'',
case when len(buildid)>4 then 'font-size:14px;border-right:#000 solid 2px;border-bottom:#ccc solid 1px;' else 'font-size:14px;border-right:#000 solid 2px;color:blue;font-weight:bold;border-bottom:#ccc solid 1px;' end as "td/@style",
td=avgshow,'',
case when len(buildid)>4 then 'font-size:14px;border:#ccc solid 1px;' else 'font-size:14px;border:#ccc solid 1px;color:blue;font-weight:bold;' end as "td/@style",
case when ot20>0 then ot20 else null end ,
case when len(buildid)>4 then 'font-size:14px;border:#ccc solid 1px;' else 'font-size:14px;border:#ccc solid 1px;color:blue;font-weight:bold;' end as "td/@style",
case when ot36>0 then ot36 else null end ,
case when len(buildid)>4 then 'font-size:14px;border:#ccc solid 1px;' else 'font-size:14px;border:#ccc solid 1px;color:blue;font-weight:bold;' end as "td/@style",
case when ot45>0 then ot45 else null end ,
case when len(buildid)>4 then 'font-size:14px;border:#ccc solid 1px;text-align:' else 'font-size:14px;border:#ccc solid 1px;color:blue;font-weight:bold;' end as "td/@style",
case when ot55>0 then ot55 else null end ,
case when len(buildid)>4 then 'font-size:14px;border:#ccc solid 1px;' else 'font-size:14px;border:#ccc solid 1px;color:blue;font-weight:bold;' end as "td/@style",
case when ot65>0 then ot65 else null end ,
case when len(buildid)>4 then 'font-size:14px;border:#ccc solid 1px;' else 'font-size:14px;border:#ccc solid 1px;color:blue;font-weight:bold;' end as "td/@style",
case when ot80>0 then ot80 else null end ,
case when len(buildid)>4 then 'font-size:14px;border:#ccc solid 1px;' else 'font-size:14px;border:#ccc solid 1px;color:blue;font-weight:bold;' end as "td/@style",
case when ot100>0 then ot100 else null end ,
case when len(buildid)>4 then 'font-size:14px;border:#ccc solid 1px;' else 'font-size:14px;border:#ccc solid 1px;color:blue;font-weight:bold;' end as "td/@style",
case when ot200>0 then ot200 else null end
from tb_test
order by buildid
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) )+
N'</table>' ;
Exec dbo.sp_send_dbmail @profile_name='MailForHr',
@recipients='test@163.com',
@subject=N'进度管理',
@body=@strHtml,
@body_format='HTML'
Go
想在表头部门\人数那行的底线加粗,但无论 怎么设置style=border-bottom:#000 solid 2px;都不起作用. 而且无论怎么设置 表头style=background:#cee4fc;border:#ccc solid 1px; ,背景色会变,但是边框不会变,没有边框,
得怎么写才能设置边框呢?
[解决办法]
这个是我部署在服务器上,每天把作业的执行情况,以邮件方式,并在邮件里面制成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 = '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' ;