Data Monitor XML & HTML Email

 ***** My Valid Data Monitor **** XML OUT *** HTML EMAIL DELIVERY  *****

IF OBJECT_ID('tempdb..#tCustomerID') IS NOT NULL DROP TABLE #tCustomerID

    select distinct 

        mm.CustomerID 

Into #tCustomerID

    from myDatabase..myTable (nolock) mm 

    left join myDatabase.dbo.myOtherTable (nolock) mo 

on mm.refID = mo.id 

    left join myDatabase.dbo.myOtherTableDetail (nolock) mod 

on mo.id = mod.myOtherTableid 

        and mm.SearchValue = mod.SearchValue 

    where mm.refID is not null 

    and mm.quantity is not null  

    and mod.SearchValue is null

IF Exists (Select 1 From #tCustomerID)

BEGIN 

DECLARE @xml NVARCHAR(MAX)

DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT [CustomerID] AS 'td' FROM #tCustomerID ORDER BY CustomerID

FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3>My Valid Data Monitor</H3>

<table border = 1><tr> <th> CustomerID </th> </tr>'    

SET @body = @body + @xml +'</table></body></html>'

--SELECT @body

  exec msdb.dbo.sp_send_dbmail 

@profile_name  = 'dbmailProfile'

,@recipients    = 'usergroup@mydomain.com'

,@subject       = 'My Valid Data Monitor'

,@body          = @body

,@body_format   = 'HTML'

END

BCP Parameters INPUT: UNCPathAndLogTable - UNC Path And LogTable OF Parameter file

Note:    Parameters INPUT: UNCPathAndLogTable - UNC Path And LogTable OF Parameter file
    RELATED SPROC: spManualRun PROCESSES THE PARAMETERS IN THIS FILE

UPDATED:   06/01/2020 TJC created for Automation


TESTING:   EXECUTE [myDatabase].dbo.[spParamFileCheck]
    @UNCPathAndLogTable=N'\\Server02\FTP\In\InParameters.csv';


RETURN OUTPUT: 0 GOOD RETURN, SSIS JOB CONTINUES
    -1 NEUTRAL RETURN, SSIS JOB STOPS
    ERROR, SSIS JOB STOPS
***************************************************************************************************/
CREATE PROCEDURE dbo.[spParamFileCheck]( @UNCPathAndLogTable varchar(512) = NULL)
AS
BEGIN
 SET NOCOUNT ON;
 declare @cmd varchar(8000), @CID INT = 0, @FileRecordCount int = 0, @CurrentDate Datetime = GETDATE(), @ErrorMessage varchar(max);
 declare @dir table(FullName varchar(512));
 IF (isnull(@UNCPathAndLogTable, '') = '') BEGIN
  SET @UNCPathAndLogTable =  N'\\Server01\FTP\In\InParameters.csv';
 END;
 BEGIN TRY
  SET @cmd='exec xp_cmdshell ''dir '+@UNCPathAndLogTable+' /b /s''';
  INSERT @dir(FullName) EXEC(@cmd);
  SELECT @FileRecordCount = COUNT(*) FROM @dir WHERE FullName is not null;
  IF (@FileRecordCount = 0) BEGIN
   PRINT '-1';
   RETURN -1;
  END;
   /*** LOGGING THE JOB PARAMETERS ***/
   IF (object_id('tempdb..#ParametersFile') is not null) DROP TABLE #ParametersFile;
   CREATE TABLE #ParametersFile (Column1 varchar(50), Column2 varchar(50), Column3 varchar(50), Column4 varchar(50));
   SET @cmd = 'bulk insert #ParametersFile from '''+@UNCPathAndLogTable+''' WITH (FIELDTERMINATOR = '','' )';
   EXEC(@cmd);
 END TRY
 BEGIN CATCH /* CATCH ERROR BUT DO NOT RAISERROR, RETURN -1 AS STOP JOB CODE */
  --INSERT INTO LogTable (CID, FileName, loaddate, SourceType,NumItems,SecondsToLoad,ErrorCode)
  -- VALUES (0, 'InParameters.csv',@CurrentDate, 'FTPsource',@FileRecordCount, 0, -1);
  SELECT @ErrorMessage = 'spParamFileCheck: ' + ERROR_MESSAGE() + ' Error on Line: ' + cast(ERROR_LINE() AS VARCHAR(10));
  PRINT '-1 '  + @ErrorMessage;
  RETURN -1;
 END CATCH;
 BEGIN TRY
  --SELECT TOP 1 * FROM #ParametersFile WITH(NOLOCK) WHERE Column1 IS NOT NULL AND ISNUMERIC(Column4) > 0;
  SELECT @FileRecordCount = COUNT(Column1)
          FROM #ParametersFile WITH(NOLOCK)
          where RTRIM(ISNULL(Column1, '')) <> ''
          and RTRIM(ISNULL(Column2, '')) <> ''
          and RTRIM(ISNULL(Column3, '')) <> ''
          and isnumeric(Column3) > 0
          and isnumeric(Column4) > 0;
  IF (@FileRecordCount > 0) BEGIN
   SELECT TOP 1 @CID = ce.CID
    FROM myDatabase.dbo.ssisInputProcessing AS irc WITH (NOLOCK)
    INNER JOIN myOtherDatabase.dbo.CopeceticItems AS ce WITH (NOLOCK) ON irc.Column1 = ce.Column1
     AND irc.Column1 in (SELECT TOP 1 Column1 FROM #ParametersFile WITH(NOLOCK)
            WHERE Column1 IS NOT NULL AND ISNUMERIC(Column4) > 0);
   IF (ISNULL(@CID, 0) = 0) BEGIN /* REPORT ERROR: INCOMING PARAMETERS FILE HAS INCORRECT DATA TO PROCESS */
    RAISERROR ('spParamFileCheck InParameters.csv was found but contains invalid client data. Column1.',15,1)
   END;
  END ELSE BEGIN  /* REPORT ERROR: INCOMING PARAMETERS FILE HAS NO DATA TO PROCESS */
   RAISERROR ('spParamFileCheck InParameters.csv was found but contains no valid rows to import. missing parameters.',15,1)
  END;
  --INSERT INTO LogTable (CID, FileName, loaddate, SourceType,NumItems,SecondsToLoad,ErrorCode)
  --VALUES (@CID, 'InParameters.csv',@CurrentDate, 'FTPsource',@FileRecordCount, 0, 0);
 END TRY
 BEGIN CATCH
  --INSERT INTO LogTable (CID, FileName, loaddate, SourceType,NumItems,SecondsToLoad,ErrorCode)
  -- VALUES (0, 'InParameters.csv',@CurrentDate, 'FTPsource',@FileRecordCount, 0, -1);
  SELECT @ErrorMessage = 'InParameters Bulk Insert: ' + ERROR_MESSAGE() + ' Line: ' + cast(ERROR_LINE() AS VARCHAR(10));
  PRINT @ErrorMessage;
  RAISERROR (@ErrorMessage,15,1);
  RETURN -1;
 END CATCH
 PRINT '0'
 RETURN 0;
 /* FUTURE: EXPORT THE INCOMING PARAMETERS FILE FROM THE INPUT FOLDER TO THE EXPORT FOLDER */
 BEGIN TRY
  declare @UNCPathAndFileOut varchar(512) = N'\\Server02\Output\InParameters.csv'
  SET @SQL ='bcp "SELECT (Column1, Column2, Column3, CAST(Column4 AS VARCHAR(1)) AS Column4 from #ParametersFile "  queryout ' +@UNCPathAndFileOut+' -t"|" -c -T ';
  EXEC xp_cmdshell @SQL ;
 END TRY
 BEGIN CATCH
  SELECT @ErrorMessage = 'Cmdshell Export: ' + ERROR_MESSAGE() + ' Error on Line: ' + cast(ERROR_LINE() AS VARCHAR(10));
  print @ErrorMessage;
  RAISERROR (@ErrorMessage,15,1);
  RETURN;
 END CATCH

SQL UNIQUE AND RANDOM TRANSACTION NAMES

--UNIQUE AND RANDOM TRANSACTION NAMES (FOR HIGH VOLUME TRANSACTIONS PER SECOND)

DECLARE @randomString VARCHAR(255) = CONVERT(varchar(255), NEWID());

SELECT @randomString = replace(replace(replace(convert(varchar, getdate(), 120),':',''),'-',''),' ','') + RIGHT(@randomString,10);

PRINT ''+ CONVERT(VARCHAR,LEN(@randomString)) + ' CHARS / OF MAXIMUM 32 CHARS FOR LENGTH OF TRANSACTION NAMES'

SELECT @randomString

BEGIN TRANSACTION  @randomString

SELECT replace(replace(replace(convert(varchar, getdate(), 120),':',''),'-',''),' ','');

SELECT CONVERT(VARCHAR,getdate(),112) + LEFT(REPLACE(CONVERT(VARCHAR,getdate(),114),':',''),6);

SELECT REPLACE(SUBSTRING(CONVERT(VARCHAR(33),SYSDATETIMEOFFSET(),126), 1, 8), '-', '') +
SUBSTRING(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(33), SYSDATETIMEOFFSET(), 126),'T',''),'.',''),':',''),9,DATALENGTH(CONVERT(VARCHAR(33), SYSDATETIMEOFFSET(), 126)))

COMMIT TRANSACTION  @randomString

OUTPUT

-- 24 CHARS / OF MAXIMUM 32 CHARS FOR LENGTH OF TRANSACTION NAMES
-- 202005141039592610925CAA  <- THIS IS THE ONE
-- 20200514103959
-- 20200514103959
-- 202005141039598866412-0500

SQL KILLBILL What Is Running SnapShot ?

KUNG FU SQL - SNAPSHOT OF SITUATION ON ANY SQL SERVER

--kill 111
              SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
              SELECT
                       @@servername as 'SrvrName'
                     ,GETDATE() as 'SnapShotTime'
                     ,StartTime          = er.start_time
                     ,SPID                = er.session_id
                     ,BlkBy              = er.blocking_session_id     
                      ,ElapsedMS          = er.total_elapsed_time
                     ,CPU                = er.cpu_time
                     ,IOReads            = er.logical_reads + er.reads
                     ,IOWrites           = er.writes    
                      ,Executions         = ec.execution_count 
                      ,CommandType        = er.command        
                      ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,qt.dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) 
                      ,SQLStatement       =
                           SUBSTRING
                           (
                                  qt.text,
                                  er.statement_start_offset/2,
                                  (CASE WHEN er.statement_end_offset = -1
                                         THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                                         ELSE er.statement_end_offset
                                         END - er.statement_start_offset)/2
                           )       
                      ,STATUS             = ses.STATUS
                     ,[Login]            = ses.login_name
                     ,Host               = ses.host_name
                     ,DBName             = DB_Name(er.database_id)
                     ,LastWaitType       = er.last_wait_type
                     ,Protocol           = con.net_transport
                     ,transaction_isolation =
                           CASE ses.transaction_isolation_level
                                  WHEN 0 THEN 'Unspecified'
                                  WHEN 1 THEN 'Read Uncommitted'
                                  WHEN 2 THEN 'Read Committed'
                                  WHEN 3 THEN 'Repeatable'
                                  WHEN 4 THEN 'Serializable'
                                  WHEN 5 THEN 'Snapshot'
                           END
                     ,ConnectionWrites   = con.num_writes
                     ,ConnectionReads    = con.num_reads
                     ,ClientAddress      = con.client_net_address
                     ,Authentication     = con.auth_scheme
                     ,QueryPlan          = CONVERT(nvarchar(MAX),qp.query_plan)
                     ,PlanHandle         = er.plan_handle
              --INTO myDatabase.dbo.dmv_reqs_20200514
              FROM sys.dm_exec_requests er
              LEFT JOIN sys.dm_exec_sessions ses
              ON ses.session_id = er.session_id
              LEFT JOIN sys.dm_exec_connections con
              ON con.session_id = ses.session_id
              CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
              OUTER APPLY
              (
                     SELECT execution_count = MAX(cp.usecounts)
                     FROM sys.dm_exec_cached_plans cp
                     WHERE cp.plan_handle = er.plan_handle
              ) ec
              CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
              WHERE er.session_id <> @@SPID
              ORDER BY
                     er.blocking_session_id DESC,
                     er.logical_reads + er.reads DESC,
                     er.session_id OPTION (RECOMPILE);
GO

SQL code snippets to save the ol joints

As we type in SSMS all day long and type the same things over and over, and in case you did not know about the snippet mgr or know how to exploit it…

You can build a library of “Surround With” snippets to save you a BUNCH of keystrokes every day.  Below are the instructions along with an attached xml code sample.

1. Save the attached to a folder, say: /snippets/
2. Create a folder under snippets, say: /snippets/My Snippets/
3. So now you have a .snippet file and a folder in: /snippets/
4. Go to SSMS and hit: CTRL+K,CTRL+B (hold the CTRL key down and hit K, then B
5. Click: ‘Add…’
6. Find and select your “My Snippets” folder on your OS
7. Back at the Code Snippets Manager, Click: ‘Import…’
8. Navigate to your root /snippets/ folder and select the .snippet file that you downloaded from this email
9. Select to add this file to your “My Snippets” container (check the box on the right) and click: Finish
10. Now you are ready to execute the “Surround With” command you added…
11. Either run the command to start off a query, OR, highlight a table name to query.  Hit: CTRL+K,S (hit K then S while holding down CTRL)
12. Just use your arrow keys and the ENTER to find and select your new command within your “My Snippets” container so you don’t have to leave the keyboard
13. Below was the result of me highlighting the fully qualified table name and executing the snippet

SSIS Script Task Sample Write Log

  public void Main()
  {
   bool errorOccurred = false; //in event all logging methods fail
   Variables lockedVariables = null;
   string fileName = "";
   string fileData = "";
   try
   {
    lockedVariables = null;
    Dts.VariableDispenser.LockOneForRead("OutputDirPath", ref lockedVariables);
    Dts.VariableDispenser.LockOneForRead("OutputData", ref lockedVariables);
    Dts.VariableDispenser.GetVariables(ref lockedVariables);
    fileName = (string)lockedVariables["User::OutputDirPath"].Value;
    if (fileName == null || fileName.Length == 0)
    {
     fileName = @"C:\myLogFileResults.log";
    }else{
     fileName = fileName.Replace(".log", string.Concat("_", DateTime.Now.ToString("yyyyMMddHHmmss"), ".log"));
     // fileName format ex: \\sharedrive\FTP\logs\myLogFileResults_20200429011136.log
    }
    fileData = (string)lockedVariables["User::OutputData"].Value;
    if (fileData == null || fileData.Length == 0 || fileData.ToLower().Contains("system.object"))
    {
     fileData = "OutputData value is empty. Nothing to report.";
    }
    using (StreamWriter w = File.AppendText(fileName))
    {
     Log(fileData, w);
    }
    using (StreamReader r = File.OpenText(fileName))
    {
     DumpLog(r);
    }
    Dts.TaskResult = (int)ScriptResults.Success;
   }
   catch (Exception ex)
   {
    errorOccurred = true; // possible network logging issue. try to save to event log.
    using (EventLog eventLog = new EventLog("Application"))
    {
     eventLog.Source = "Application";
     eventLog.WriteEntry(string.Concat("ssis pkg output log issue. ", "\r\n", ex.Message.ToString()), EventLogEntryType.Error, 101, 1);
    }
   }
   finally
   {
    if (lockedVariables != null) lockedVariables.Unlock();
    if (errorOccurred) // fire event to store in SQL DTS log
    {
     Dts.Events.FireError(18, "ssis pkg output log issue.", "An Exception was caught during logging write and output data flush. Check the server event application log for details.", "", 0);
     Dts.TaskResult = (int)ScriptResults.Failure;
    }
   }
  }
  public static void Log(string logMessage, TextWriter w)
  {
   w.Write("\r\nLog Entry : ");
   w.WriteLine($"{DateTime.Now.ToLongTimeString()} {DateTime.Now.ToLongDateString()}");
   w.WriteLine("  :");
   w.WriteLine($"  :{logMessage}");
   w.WriteLine("-------------------------------");
  }
  public static void DumpLog(StreamReader r)
  {
   string line;
   while ((line = r.ReadLine()) != null)
   {
    Console.WriteLine(line);
   }
  }

CREATE COMMA SEPERATED LIST OF VALUES TO SEARCH BY CHARINDEX

---CREATE COMMA SEPERATED LIST OF VALUES TO SEARCH BY CHARINDEX

DECLARE @CSVIDs varchar(max)
SELECT DISTINCT @CSVIDs = STUFF((SELECT DISTINCT text_id /*EntityNames*/ +',' FROM #c FOR XML PATH ('')) ,1,0,'') -- (xwaid)

--USAGE OF CSV STUFFED
IF (object_id('tempdb..#SearchResultList') is not null) DROP TABLE#SearchResultList;

SELECT DISTINCT en.ID, en.OPID, en.EntityNames, en.WAID
 INTO #SearchResultList
from myDatabase.dbo.EntityNames en
where
 charindex(ce.OPID,@CSVIDs) > 0 

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
/************************************************************************/