文章/答案/技术大牛

发布

社区首页 >问答首页 >存储过程导致HTML格式

问存储过程导致HTML格式
EN

Database Administration用户

提问于 2019-10-10 07:53:47

回答 2查看 1.9K关注 0票数 -1

我需要修改下面的脚本，这样我们就必须以sp_validatelogins格式作为邮件接收存储过程的结果。

下面是脚本。

USE [test]
GO

ALTER PROC [dbo].[DBA_REPORT] 
@emailrecipients NVARCHAR(256)  
AS  
BEGIN  

SET NOCOUNT ON

DECLARE @EmailSubject NVARCHAR(128)
       ,@ReportTitle NVARCHAR(128)
       ,@ReportTitle2 NVARCHAR(128)
       ,@ReportTitle3 NVARCHAR(128)
       ,@ReportTitle4 NVARCHAR(128)
       ,@ReportTitle5 NVARCHAR(128)
       ,@ReportTitle6 NVARCHAR(128)
       ,@ReportTitle7 NVARCHAR(128)
       ,@TableHTML NVARCHAR(max)
       ,@TableHTML2 NVARCHAR(max)
       ,@TableHTML3 NVARCHAR(max)
       ,@TableHTML4 NVARCHAR(max)
       ,@TableHTML5 NVARCHAR(max)
       ,@TableHTML6 NVARCHAR(max)
       ,@TableHTML7 NVARCHAR(max)
       ,@Mergedtable NVARCHAR(MAX)
       ,@sql nvarchar(max)

SET NOCOUNT ON

BEGIN
       SELECT  @EmailSubject = 'DBA REPORT'
              ,@ReportTitle =  'Report of jobs that failed in the past 24 hours'
              ,@ReportTitle2 = 'Report of disabled jobs'
              ,@ReportTitle3 = 'Invalid Logins'
              ,@ReportTitle4 = 'Logins having no permissions'
              ,@ReportTitle5 = 'Logins having sysadmin permissions'
              ,@ReportTitle6 = 'Report on Suspect Pages'
              ,@ReportTitle7 = 'Report on databases'

--3rd.create TEMP TABLE to hold results of SP call 
          create table #invalidlogins(
                                       A_SID varchar(128),
                                       A_LOGIN varchar(128))

           insert into #invalidlogins
           exec sp_validatelogins

--select from the table variable Only if the data exists

IF EXISTS (SELECT * FROM #invalidlogins) 
 BEGIN    


---Creating  HTML table
              SET @TableHTML3 = '<html><head><style>'
               + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' 
               +'</style></head><body>' + '<div style="margin-top:20px; margin-left:5px; margin-bottom:15px; font-weight:bold; font-size:1.3em; font-family:calibri;">'
               + @ReportTitle3 + '</div>' + '<div style="margin-left:50px; font-family:Calibri;"><table cellpadding=1 cellspacing=1 border=1>' + '<tr bgcolor=#4b6c9e>'
               + '<td align=center><font face="calibri" color=White><b>A_LOGIN</b></font></td></tr>'

              DECLARE @EmailBody3 NVARCHAR(max)

              SELECT @EmailBody3 = (
                           SELECT td =A_SID
                                  ,td =A_LOGIN
                           FROM #invalidlogins
                           FOR XML RAW('tr')
                                  ,ELEMENTS
                           )

              SET @EmailBody3 = REPLACE(@EmailBody3, '<td>', '<td align=center><font face="calibri">')
              SET @EmailBody3 = REPLACE(@EmailBody3, '</td>', '</font></td>')
              SET @EmailBody3 = REPLACE(@EmailBody3, '_x0020_', SPACE(1))
              SET @EmailBody3 = REPLACE(@EmailBody3, '_x003D_', '=')
              SET @EmailBody3 = REPLACE(@EmailBody3, '<TRRow>0</TRRow>', '')

              SET @TABLEHTML3 = @TABLEHTML3 + @EmailBody3 + '</table></div></body></html>'
              SET @TABLEHTML3 = '<div style="color:Black; font-size:11pt; font-family:Calibri; width:100px;">' + @TableHTML3 + '</div>'

              DROP TABLE #invalidlogins


 -- Send the mail in this block as well.
    END

         SET @Mergedtable = @TableHTML+@TableHTML2+@TableHTML3+@TableHTML5+@TableHTML7

       EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLDBA Profile'
                     ,@recipients = @emailrecipients 
                     ,@Body = @Mergedtable
                     ,@subject = @EmailSubject
                     ,@Body_format = 'HTML'



  END















END









GO

t-sql

sql-server

回答 2

Database Administration用户

发布于 2019-10-10 12:55:32

几年前，我发现了一个可以将任何查询转换为HTML的查询：

-- Description: Turns a query into a formatted HTML table. Useful for emails. 
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable] 
(
  @query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
  @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
  @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN   
  SET NOCOUNT ON;

  IF @orderBy IS NULL BEGIN
    SET @orderBy = ''  
  END

  SET @orderBy = REPLACE(@orderBy, '''', '''''');

  DECLARE @realQuery nvarchar(MAX) = '
    DECLARE @headerRow nvarchar(MAX);
    DECLARE @cols nvarchar(MAX);    

    SELECT * INTO #dynSql FROM (' + @query + ') sub;

    SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    

    EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT

    SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>'' 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';

    SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';    
    ';

  EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END
GO
-----------------------------------------------------
USAGE:

DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = N'SELECT * FROM dbo.People', @orderBy = N'ORDER BY FirstName';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Foo',
    @recipients = 'bar@baz.com;',
    @subject = 'HTML email',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;

票数 2

Database Administration用户

发布于 2019-10-10 08:16:42

如果exec sp_validatelogins不返回结果集，则#invalidlogins将为空。

因此，将@EmailBody设置为#invalidlogins表中的值也将@EmailBody设置为NULL。@TableHTML也是如此

     SET @TABLEHTML = @TABLEHTML + @EmailBody + '</table></div></body></html>'

这可能就是你收到空邮件的原因。

您可以通过插入'No Invalid Logins'值而不是选择它来解决这个问题：

IF EXISTS (SELECT * FROM #invalidlogins) 
BEGIN    
SELECT A_LOGIN as Invalid_Logins FROM #Invalidlogins 
END 
ELSE BEGIN
INSERT INTO #invalidlogins(A_LOGIN)
SELECT 'No Invalid Logins' as Invalid_logins 
END

@EmailBody结果

<tr><td align=center><font face="calibri">No Invalid Logins</font></td></tr>

@TableHTML结果

<div style="color:Black; font-size:11pt; font-family:Calibri; width:100px;"><html><head><style>td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} </style></head><body><div style="margin-top:20px; margin-left:5px; margin-bottom:15px; font-weight:bold; font-size:1.3em; font-family:calibri;">bla</div><div style="margin-left:50px; font-family:Calibri;"><table cellpadding=1 cellspacing=1 border=1><tr bgcolor=#4b6c9e><td align=center><font face="calibri" color=White><b>A_LOGIN</b></font></td></tr><tr><td align=center><font face="calibri">No Invalid Logins</font></td></tr></table></div></body></html></div>

或者，如果过程不返回任何内容，则无法发送警报。

编辑2

如果没有数据，我应该不会收到任何邮件，但是我正在接收空邮件，所以如果没有数据，我需要更改为不接收任何邮件。

那么您应该指定

IF EXISTS (SELECT * FROM #invalidlogins)  BEGIN

--Run all following statements & send the mail


END

比如

USE [test]
GO

ALTER PROC [dbo].[DBA_REPORT] 
@emailrecipients NVARCHAR(256)  
AS  
BEGIN  

SET NOCOUNT ON

DECLARE @EmailSubject NVARCHAR(128)
       ,@ReportTitle NVARCHAR(128)
       ,@ReportTitle2 NVARCHAR(128)
       ,@ReportTitle3 NVARCHAR(128)
       ,@ReportTitle4 NVARCHAR(128)
       ,@ReportTitle5 NVARCHAR(128)
       ,@ReportTitle6 NVARCHAR(128)
       ,@ReportTitle7 NVARCHAR(128)
       ,@TableHTML NVARCHAR(max)
       ,@TableHTML2 NVARCHAR(max)
       ,@TableHTML3 NVARCHAR(max)
       ,@TableHTML4 NVARCHAR(max)
       ,@TableHTML5 NVARCHAR(max)
       ,@TableHTML6 NVARCHAR(max)
       ,@TableHTML7 NVARCHAR(max)
       ,@Mergedtable NVARCHAR(MAX)
       ,@sql nvarchar(max)

SET NOCOUNT ON


       SELECT  @EmailSubject = 'DBA REPORT'
              ,@ReportTitle =  'Report of jobs that failed in the past 24 hours'
              ,@ReportTitle2 = 'Report of disabled jobs'
              ,@ReportTitle3 = 'Invalid Logins'
              ,@ReportTitle4 = 'Logins having no permissions'
              ,@ReportTitle5 = 'Logins having sysadmin permissions'
              ,@ReportTitle6 = 'Report on Suspect Pages'
              ,@ReportTitle7 = 'Report on databases'

--3rd.create TEMP TABLE to hold results of SP call 
          create table #invalidlogins(
                                       A_SID varchar(128),
                                       A_LOGIN varchar(128))

           insert into #invalidlogins
           exec sp_validatelogins

--select from the table variable Only if the data exists

IF EXISTS (SELECT * FROM #invalidlogins) 
 BEGIN    


---Creating  HTML table
              SET @TableHTML3 = '<html><head><style>'
               + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' 
               +'</style></head><body>' + '<div style="margin-top:20px; margin-left:5px; margin-bottom:15px; font-weight:bold; font-size:1.3em; font-family:calibri;">'
               + @ReportTitle3 + '</div>' + '<div style="margin-left:50px; font-family:Calibri;"><table cellpadding=1 cellspacing=1 border=1>' + '<tr bgcolor=#4b6c9e>'
               + '<td align=center><font face="calibri" color=White><b>A_LOGIN</b></font></td></tr>'

              DECLARE @EmailBody3 NVARCHAR(max)

              SELECT @EmailBody3 = (
                           SELECT td =A_SID
                                  ,td =A_LOGIN
                           FROM #invalidlogins
                           FOR XML RAW('tr')
                                  ,ELEMENTS
                           )

              SET @EmailBody3 = REPLACE(@EmailBody3, '<td>', '<td align=center><font face="calibri">')
              SET @EmailBody3 = REPLACE(@EmailBody3, '</td>', '</font></td>')
              SET @EmailBody3 = REPLACE(@EmailBody3, '_x0020_', SPACE(1))
              SET @EmailBody3 = REPLACE(@EmailBody3, '_x003D_', '=')
              SET @EmailBody3 = REPLACE(@EmailBody3, '<TRRow>0</TRRow>', '')

              SET @TABLEHTML3 = @TABLEHTML3 + @EmailBody3 + '</table></div></body></html>'
              SET @TABLEHTML3 = '<div style="color:Black; font-size:11pt; font-family:Calibri; width:100px;">' + @TableHTML3 + '</div>'

              DROP TABLE #invalidlogins




         SET @Mergedtable = @TableHTML+@TableHTML2+@TableHTML3+@TableHTML5+@TableHTML7

       EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLDBA Profile'
                     ,@recipients = @emailrecipients 
                     ,@Body = @Mergedtable
                     ,@subject = @EmailSubject
                     ,@Body_format = 'HTML'

     -- Send the mail in this block as well.
    END

END


GO

票数 0

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/250729

复制

相似问题

问存储过程导致HTML格式
EN

回答 2

Database Administration用户

Database Administration用户

编辑2

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问存储过程导致HTML格式EN

回答 2

Database Administration用户

Database Administration用户

编辑2

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问存储过程导致HTML格式
EN