Data Exploration

There might be several cases where post completion of data loads certain data validation and quality checks needs to done to ensure we are delivering quality data for reporting and decision support and analytical team. The manual way of doing this is tedious job and if you want to buy some data quality tool for this job it would be extra $ spent which doesn't add much value to the end product. Here using simple table and stored proc we are designing a simple and powerful application that does data validation and provide complete details about the data inconsistency in your application. Also there is feature which runs data correction scripts to correct the data issue well before some down stream application uses it. The complete setup is reusable and can be used across any relational db platform. Design: We need three object to achieve this process  Metadata Table: Here we will store all our validation scripts and the correction scripts that has to be executed on any...

We might have came across a scenario where the Sql server package execution is dependent on some other job to kick start. So we need to handle the dependent nature of the job in a smart way. The dependent job might be a frequently running one or it might be overnight job, so the challenge in frequently running job is our package should not start when the dependent job is in running state. The solution for this use case lies in msdb (msdb) a system database available in Microsoft Sql Server. This db maintains all information about sql job agent and many more information regarding transaction and other audit details. I have come up with a reusable T-SQL stored procedure which does this task for us without much manual interaction. We can call this stored proc in the first job step and this sp will keep checking for dependent job to either complete or will check for latest successful run for current execution day. USE Testdb GO IF EXISTS (   SELECT *   FR...

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: '+@Er...