SQL SERVER Audit Log HTML Report

/***********************************************************************/

TRIGGER TO CREATE REPORT DATA

CREATE TRIGGER [dbo].[trgAdminFeesAudit] on [myDatabase].[dbo].[AdminFeesAudit]
FOR UPDATE, INSERT
AS
BEGIN
 /* TRIGGER TO LOG INCORRECT AdminType  */
DECLARE @AdminTypePrev varchar(20) = null;
IF EXISTS(SELECT TOP 1 AdminType FROM deleted) BEGIN
  SELECT @AdminTypePrev = AdminType FROM deleted;
END;
  INSERT INTO AdminFeesAudit
  ( [SystemUser],[HostName],[EventType],[SPID],[SchemaName],[ObjectName],[ObjectType],[AdminTypeID],[AdminTypeNetworkID],[EffectiveDate],[TerminationDate],[AdminType],[AdminTypePrev],[Fee])
  SELECT SUSER_SNAME(), HOST_NAME(), EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)'),
 EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','smallint'),
    EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)'),
    EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'),
    EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)'),
 i.ID, i.AdminTypeNetworkID, i.EffectiveDate, i.TerminationDate, i.AdminType, @AdminTypePrev, CONVERT(money, i.Fee)
  FROM  AdminFees p WITH(nolock)
 INNER JOIN inserted i ON p.ID = i.ID --i.AdminTypeID
END
GO
ALTER TABLE [dbo].[AdminFees] ENABLE TRIGGER [trgAdminFeesAudit]
GO

/***********************************************************************/
REPORT

DECLARE @body NVARCHAR(MAX);
BEGIN TRY 
SET @body = N'<html><body><H3>Audit Log Info</H3><H4> .CORRECT THE INVALID AdminType and Stop the SQL job.</H4><table>'
 + N'<tr><th> Eventdate </th> <th> SystemUser </th> <th> HostName </th> <th> EventType </th> <th> SPID </th> <th> AdminTypeID </th> <th> AdminTypeNetworkID </th> <th> EffectiveDate </th> <th> TerminationDate </th> <th> FeeType </th> <th> FeeTypePrev </th> <th> Fee </th></tr>' 
 + CAST((
  SELECT Convert(Varchar(20),pa.[DateStamp],120) AS td
      ,ISNULL(pa.[systemuser],'') AS td
      ,ISNULL(pa.[hostname],'') AS td
      ,ISNULL(pa.[EventType],'') AS td
      ,ISNULL(pa.[SPID],'') AS td
      ,ISNULL(pa.[AdminTypeID],'') AS td
      ,ISNULL(pa.[AdminTypeNetworkID],'') AS td
      ,ISNULL(pa.[EffectiveDate],'') AS td
      ,ISNULL(pa.[TerminationDate],'') AS td
      ,ISNULL(pa.[FeeType],'') AS td
      ,ISNULL(pa.[FeeTypePrev],'') AS td
      ,ISNULL(pa.[Fee],'') AS td
  FROM myDatabase.dbo.AdminFeesAudit [pa] (NOLOCK) WHERE AdminTypeID = @AdminTypeID
  FOR XML RAW('tr'), ELEMENTS
 ) AS NVARCHAR(MAX)) +'</table></body></html>';
 /* DBAs OPTIONAL CODE HERE - EITHER SEND HERE 1 TIME OR ON FAILURE ERROR OR BOTH */
 /* IF THIS CODE IS ENABLED THEN THERE IS NO NEED TO EMAIL ON FAILURE - JUST TRACK IT AS A FAILING JOB */
  -- EXEC msdb.dbo.sp_send_dbmail
  --@profile_name  = 'ReportingDB'
  --  ,@recipients    = 'JobMonitor@myDomain.com;DataOps@myDomain.com'
  --  ,@subject       = 'Audit Log Info Report'
  --  ,@body          = @body
  --  ,@body_format   = 'HTML';
  --SELECT cast(@body AS NVARCHAR(MAX));
  UPDATE myDatabase.dbo.AdminFeesAudit SET AlertSent = 1 WHERE AdminTypeID = @AdminTypeID and AlertSent = 0;

  SET @body = ('<?xml version="1.0"?><xml>' + @body + '</xml>');
  SELECT cast(@body AS NVARCHAR(MAX));
  THROW 58001, @body, 1;
 END TRY 
 BEGIN CATCH 
   DECLARE @message NVARCHAR(2048) = N'ERROR: Audit Log Alert - INVALID AdminType. ' +  ISNULL(ERROR_MESSAGE(),' NULL ERROR_MESSAGE.');
   THROW 58002, @message, 1;
 END CATCH;

SQL SERVER EMAIL HTML REPORT

if(@tst > 0 )
begin
 --write the email text
 declare @etxt varchar(max)
 set @etxt=''
 set @etxt=@etxt+'<html>
 <style type="text/css">
 body {font-family:Helvetica;font-size:11pt;}
 th {background-color:Navy;color:white;font-weight:bold;font-size:12pt;}
 th.grid {width=100px;border:1px solid silver;font-weight:normal;font-size:10pt;}
 td.grid {border:1px solid silver;font-size:10pt;}
 </style>
 <table width="900" border="0">
 <tr>
  <th align="left" style="width:900px;">My Company Reporting Services</th>
 </tr>
 <tr>
 <td>
  <span style="font-weight:bold;font-size:14pt;">Daily My Data Reports</span><br />
  Please direct any questions or concerns to our help desk (help@myDomain.com).<br />
  Thank you.</i>
  <br />
  <br />
  <b>Please click the link(s) below to view current My data loaded to the system:</b><br />
  <br />
   <a href="https://reporting.mydomain.com/ReportViewerHTML5.aspx?ReportFile=/Entity/My%20Data.trdx&EntityID=01&ID=1982973293">Report Title 1</a>
  <br />
  <a href="https://reporting.mydomain.com/ReportViewerHTML5.aspx?ReportFile=/Entity/My%20Data.trdx&EntityID=01&ID=1942447321">Report Title 2</a>
  <br />
  <a href="https://reporting.mydomain.com/ReportViewerHTML5.aspx?ReportFile=/Entity/My%20Data.trdx&EntityID=01&ID=1386904886">Report Title 3</a>
  <br />
  <a href="https://reporting.mydomain.com/ReportViewerHTML5.aspx?ReportFile=/Entity/My%20Data.trdx&EntityID=01&ID=1982004289">Report Title 4</a>
  <br />
  <br />
  <b>Reporting Workflow record counts:</b><br />
  <a href="https://reporting.mydomain.com/ReportViewerHTML5.aspx?ReportFile=/Billing/ReportWFData.trdx&lookback=30">Reporting Workflow</a>
  <br />
  <b>Reporting Fallouts:</b><br />
  <a href="https://reporting.mydomain.com/ReportViewerHTML5.aspx?ReportFile=/Billing/ReportingFallouts.trdx&showArchived=N">Reporting Fallouts</a>
 </td>
 </tr>
 <tr>
 <td style="border-top:1px solid black;text-align:right;">
  <span style="font-weight:bold;font-size:10pt;">My Company Name,&nbsp</span>
  <span style="font-style:italic;font-size:10pt;">Maximizing Value in all we do</span>
 </td>
 </tr>
 </table>
 </html>'
 set @etxt = replace(@etxt,'~BR~','<br />')
 --send the email
 declare @to varchar(2000)
 set @to=@who
 exec msdb.dbo.sp_send_dbmail
 @profile_name='ReportingDB'
 ,@recipients=@to
 ,@copy_recipients='datasystems@mydomain.com'
 ,@subject=@msg
 ,@body_format='HTML'
 ,@body=@etxt
end

SQL SERVER DDL Job Configuration

DDL_Daily_Report is a job with 2 steps
Step 1: Run
EXEC [MaintenanceDB].[dbo].[spDDLLog_Info_Email]
----------------
Step 2: Failure
declare @Job_ID uniqueidentifier;
select @Job_ID = job_id from msdb..sysjobs where job_id = $(ESCAPE_NONE(JOBID));
exec usp_GetJobFailureData_Main @Job_ID, 'Alerts@myDomain.com', 'help@myDomain.com', 'HTML'
/*-------------------------------------------------------------------------------------------*/


BEGIN TRY
EXEC myDatabase.dbo.ProcessingSwitchJob
END TRY
BEGIN CATCH
  DECLARE @ErrorMessage varchar(max) = = 'Exception occurred in proc myDatabase.dbo.ProcessingSwitchJob. Exception detail: '

  SELECT @ErrorMessage = @ErrorMessage + ''+ ERROR_MESSAGE() + '';

  EXEC msdb.dbo.sp_send_dbmail
  @profile_name='ReportingMail',
  @recipients='systems@domain.com',
  @subject='Processing Switch Exception',
  @body=@ErrorMessage,
  @importance='High'
            
        RETURN;
END CATCH

/*-------------------------------------------------------------------------------------------*/
Declare @ErrorMessage varchar(500);

BEGIN CATCH
  exec msdb.dbo.sp_send_dbmail
  @profile_name='ReportingMail'
  ,@recipients='systems@domain.com'
  ,@subject='ERROR PROD01 job: Processing Load Step 2 Switch Fail'
  ,@body='Scheduled Processing Load !  Please check system.'
  
  SELECT @ErrorMessage=ERROR_MESSAGE() ;
  RAISERROR( @ErrorMessage,16,1 );
END CATCH
SPECIAL NOTES: Please set the following appropriately so that the job logs the error and then continues to the next step.
on job success, goto next step
on job failure, goto next step

SQL SERVER TRY CATCH BLOCK SAMPLES

SQL SERVER TRY CATCH BLOCK SAMPLES

/************************************************************************/
IF @Err = 1
BEGIN
 exec msdb.dbo.sp_send_dbmail
 @profile_name='ReportingMsgs'
 ,@recipients='Alerts@mydomain.com;DataOperations@mydomain.com'
 ,@subject='Error occurred in the SuperAwesome SQL job run'
 ,@body_format='HTML'
 ,@body=@ErrMsg
END
/************************************************************************/
BEGIN CATCH
          SELECT @ErrorMessage = ERROR_MESSAGE();
          RAISERROR (@ErrorMessage, 16, 1);
          RETURN
END CATCH
/************************************************************************/
BEGIN CATCH
  SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();
  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState );
  RETURN
END CATCH
/************************************************************************/
BEGIN CATCH
  RAISERROR('Unable to add new client', 16, 1)
        RETURN
END CATCH
/************************************************************************/
begin catch
  declare @msg varchar(max) = 'A DM Failed. Error Message: ' + error_message()
  exec SUPPORT.dbo.ReportErrorEmail
   'DM Failure'
   , @msg
   ,'Alerts@mydomain.com;DataOperations@mydomain.com'
end catch
/************************************************************************/

BEGIN CATCH
  DECLARE @error2 varchar(max)
  DECLARE @msg2 varchar(max)

  SELECT @error2 = ERROR_MESSAGE();

  SET @msg2 = 'Exception occurred in proc, '+ @@servername + '.'+db_name() +'.dbo.BillingForEntity, when processing BillingEntity ' + @BillingEntity
     + '. Exception detail: ' + @error2
  EXEC msdb.dbo.sp_send_dbmail
  @profile_name='ReportingMsgs',
  @recipients='Alerts@mydomain.com;DataOperations@mydomain.com',
  @subject='Billing Invoice Create Exception',
  @body=@msg2,
  @importance='High'
            
        RETURN;
END CATCH
/************************************************************************/
BEGIN CATCH
     if(ERROR_NUMBER()=242) or (ERROR_Message() like '%date%')
  begin

   select 'Line '+@noRecordsChar +':Invalid Invoice Date detected.This is not editable cell.';
  end
  else
  begin
        Select 'Error Occured WHile Validating The Payment Records.';
  end

END CATCH
/************************************************************************/