-1

I require the changes in the below script so that we have to receive the results of the stored procedure sp_validatelogins as mail in HTML format.

The below is the script .

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
Randi Vertongen
  • 16,593
  • 4
  • 36
  • 64
Teja
  • 11
  • 1
  • 8

2 Answers2

2

I found some years ago a query that can transform any query into 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;
Racer SQL
  • 7,546
  • 16
  • 77
  • 140
0

If exec sp_validatelogins does not return a resultset, the #invalidlogins will be empty.

As a result, setting @EmailBody to values in the #invalidlogins table will also set @EmailBody to NULL. The same is true for @TableHTML

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

This might be why you are receiving empty mails.

You could resolve this by inserting the 'No Invalid Logins' value instead of selecting it:

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

Result for @EmailBody 

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

Result for @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>

Or you could not send an alert if the procedure does not return anything.

Edit 2

If there is no data i should not receive any mail but i'm receiving empty mail so what i need to change to not receive any mail if no data exist

Then you should specify

IF EXISTS (SELECT * FROM #invalidlogins)  BEGIN

--Run all following statements & send the mail


END

such as

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
Randi Vertongen
  • 16,593
  • 4
  • 36
  • 64