将报告发送到电子邮件(HTML)

Question

我有两张临时桌子的程序

CREATE TABLE #temp       
  ( databasename VARCHAR(100),  
    objectname VARCHAR(100), 
    index_id INT,
    indexname  VARCHAR(100), 
    avg_fragmentation_percent FLOAT,
    indextype VARCHAR(100) 
  )       


CREATE TABLE #temp2       
( 
    databasename VARCHAR(100), 
    objectname VARCHAR(100),
    index_id INT,
    indexname VARCHAR(100), 
    avg_fragmentation_percent FLOAT,
    indextype VARCHAR(100)   
)

INSERT INTO #temp (databasename, 
objectname,index_id, 
indexname,
avg_fragmentation_percent,
indextype)
EXEC master.sys.Sp_msforeachdb
' USE [?] SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as 
 ObjectName,
a.index_id, 
b.name as IndexName, 
avg_fragmentation_in_percent, 
index_type_desc
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a 
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id 
WHERE b.index_id <> 0 and avg_fragmentation_in_percent <>0'

INSERT INTO #temp2 (databasename, objectname,index_id, 
indexname,avg_fragmentation_percent,indextype)
EXEC master.sys.Sp_msforeachdb
' USE [?] SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as 
 ObjectName,
a.index_id, 
b.name as IndexName, 
avg_fragmentation_in_percent, 
index_type_desc
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a 
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id 
WHERE b.index_id <> 0 and avg_fragmentation_in_percent <>0'

并希望将程序的结果发送到电子邮件中。问题是这个查询工作正常，但是当我执行这个查询时，它不会找到临时表。

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>ID</th><th>Table</th>' +
    N'<th>Index</th><th>Before fragmentation</th><th>After fragmentation</th>' +
    --N'<th>Expected Revenue</th></tr>' +
    CAST ( ( SELECT td = T1.databasename,'',
                    td = T1.objectname, '',
                    td = T1.index_id, '',
                    td = T1.indexname, '',
                    td = T1.indextype,'',
                    td = T1.avg_fragmentation_percent AS 
                         avg_fragmentation_percent_pre_change, '',
                    td = T2.avg_fragmentation_percent AS 
                         avg_fragmentation_percent_post_change
              FROM #temp as T1
              INNER JOIN #temp2 AS T2
              ON T1.databasename = T2.databasename
              AND T1.objectname = T2.objectname
              AND T1.index_id = T2.index_id
              AND T1.indexname = T2.indexname
              AND T1.indextype = T2.indextype
              WHERE T1.databasename not in  ('master',
                                             'model',
                                             'msdb',
                                             'tempdb',
                                             'reportServer',
                                             'ReportServerTempDB')             
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;


EXEC msdb.dbo.sp_send_dbmail @recipients='example@mail.com',
@profile_name='example@mail.com',
    @subject = 'Report',
    @body = @tableHTML,
    @body_format = 'HTML';

Answer 1

插入#temp和#tem2非常好。

我相信你得到了下面的错误。

Msg 156，15级，State 1，行70不正确的语法靠近关键字'AS‘。

此错误是由于代码下面部分的显式混叠造成的。

td = T1.avg_fragmentation_percent AS avg_fragmentation_percent_pre_change, '',
td = T2.avg_fragmentation_percent AS avg_fragmentation_percent_post_change

因为你已经把它命名为'td‘了。就会变成这样

td = T1.avg_fragmentation_percent , '',
td = T2.avg_fragmentation_percent 

使用AS删除显式混叠，并运行代码并查看它是否有效。我做了测试而且成功了。