Error Handling in T-SQL & Email Notification

-
This is something I have found useful when you need to capture the exact error message while running the stored procedure in MSSQL database and send automated emails with exact error details to operation guys or to a automated incident management tool which can create ticket for the failure with the email content 

 We should design the stored procedure in try catch (TRY...CATCH) method and pass the below script in the catch block. 

 USE TESTDB
GO
CREATE PROC TEST_SP
AS
BEGIN TRY

 --- ACTUAL CODE

 END TRY

BEGIN CATCH
PRINT DB_NAME() +'.'+ OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) + ' LOAD - FAILED'

DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
DECLARE @LDDATE VARCHAR(40) =CONVERT(VARCHAR(20),GETDATE(),112);
DECLARE @BDY NVARCHAR(MAX) = 'ServerName:'+ @@SERVERNAME + CHAR(10) + 'ErrorMessage: '+@ErrorMessage + CHAR(10) + 'ErrorSeverity: '+CAST(@ErrorSeverity AS VARCHAR(20)) + CHAR(10) +'ErrorState: '+CAST(@ErrorState AS VARCHAR(20));
DECLARE @SUB NVARCHAR(MAX) = @@SERVERNAME + ' : ' + 'FAILURE : ' + DB_NAME() +'.'+ OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) + ' : ' + @LDDATE 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name='email_profile_name',
@recipients='operation_emailaddress@domain.com',
@subject=@SUB,
@body =  @BDY;
THROW
END CATCH 

The above code will send out email notification with exact error message which sql server returns while execution.

 Tip: You can see I have used THROW(link) statement inside my catch block. In general when we use try and catch block the overall execution of procedure will be always successful even if the procedures fails, this the default behavior of try catch block use case. if you wish to show the status of the procedure as failure use THROW statement inside your catch block.

Prerequisites for using this approach

  1. Microsoft SQL Server on premise database installation
  2. Email Profile created in MS SQL server  Database Mail Profile

Disclaimer: All information shared above are based on real use cases while working on a typical data projects. If there is some information which is missing or there is any scope of improvement in the above content I always welcome such suggestions.
Location: Bengaluru, Karnataka, India

Comments

Post a Comment

Popular posts from this blog

Microsoft Power Automate -- Timeout Limitation of 120 Sec in SQL Actions -- Solution

-
Image
Problem : If you have used Microsoft Power Automate for any of your process automation task and if it includes any SQL operations you might have came across a limitation within any sql action for timeout of 120 seconds. So that means any sql operation that is part of your process automation has to complete within 120 seconds. Ah isn't this strange? there might be some sql operation which would take more than 120 seconds like creating a backup of existing tables on request or any big DML task which would run for hours together. So in existing sql task available in Power Automate this can't be achieved. Below is Microsoft link for current limitation  https://docs.microsoft.com/en-us/power-automate/limits-and-config Solution : Well we have got many other actions triggers available in power automate to trigger or automate a process, I am going to use DevOps action to create a release and in Azure DevOps will keep the SQL scripts which needs longer execution time. This will simu...
Read more

How to parse XML response from REST API in Azure Data Factory

-
Image
Parse XML response from REST API in Azure Data Factory Well Azure ecosystem is evolving day by day but there are still many features that are missing or in the pipeline of getting them in the latest release, One such feature is parsing of XML response from API in Azure Data Factory which is yet not available in ADF as of today.  These days all latest REST API response type is JSON, but there are many legacy API which are still using XML as response type. As a Data/ETL Engineer you might have come across such scenarios in On Premises system where we could have used C#,SSIS, Informatica or similar ETL or programming methodologies to retrieve data a XML response type API and load them into target application/system. Now as we have moved PAAS & SAAS system where each service or application is hosted in cloud we would need to do similar activity of reading xml response from API and process it. Lately ADF accepts XML file as a source file type and using copy activity we can load the ...
Read more

Power BI Usage Metrics with the help of Power Automate - PBI Analytics

-
Image
Power BI Usage Metrics Analytics   Metrics play vital role in organization as it gives real-time and forecasted analysis for business players to take decision. These decision help the organization to grow and prosper in the market. Most of these metrics are build using BI tools available in market such as Power BI, Tableau, Spotfire ..etc. Today I am going to take Power BI as an example and discuss further on it. Typically any  organization   will have 100's of reports created for various analytical analysis which business is looking for and they will be adding 100 more as and when new request comes. And there will many analyst and business stakeholders how will be using them proactively and some rarely.  What about user analytics of these users and report analytics of all the reports that are being used? These analytics will help the IT team to priorities the current user base, priorities the reports eco system and also will give insigh...
Read more