Skip to content
5MinuteBI Creating Data Power Users 5 Minutes at a time

5MinuteBI

Creating Data Power Users, 5 Minutes at a Time

  • Home
  • Blog
  • About
  • Contact

How To Automate SQL Azure Stored Procedures Using Runbooks and Email

BySteve Young Updated onJune 25, 2023

Overview

Minimize the impact on SQL Automation workflows when migrating to SQL Azure by automating stored procedures using Runbooks and E-mail.  This step-by-step tutorial and walkthrough will review a solution to provide scheduling via Azure Runbooks with integrated O365 email alerts.

This was a specific situation of an existing installation that utilized SQL Stored Procedures run by a corporate scheduler and provided return values that would become e-mails to their operational and business users.  This alerting framework and pattern were in production in on-premises SQL Server workloads and were being moved to Azure.  The scripts referenced here are at the end of this article and are also published GitHub.

Scenario

The scenario this post reviews is of an organization new to Azure and is currently working on a lift and shift of their existing SQL workloads from their on-premises data center to SQL Azure. There were many changes due to the migration to the environment that had to be taken into account. The risks that would be introduced in redesigning and updating the applications that used the databases would be for a follow-up phase as the goal was to number of structural changes they wanted to have in their operational processes.  Sometimes it is better to do one thing at a time than try and do everything at once when there are serious time pressures.

There are quite a few moving parts involved in migrating your on-premises SQL workloads to SQL Azure DB. A major decision point is automation. A popular pattern is scheduling SQL stored procedures and sending out e-mails based on success or failure return values. You can still leverage those patterns using Azure Runbooks and your O365 email accounts, and this step-by-step walkthrough will help you set this up in solution.

Create the Azure Automation Account

Runbooks are part of the Azure Automation Account features, which can be found here; An introduction to Azure Automation

Let’s start by creating the resources needed by first creating your Azure Automation Account.

Step 1- Select the Create a Resource menu item. Search for Azure Automation if the option is not at the top of the list.

Step 2. – This search brings the Automation resource up. Select the option and the next blade becomes visible.

Step 3 – The description becomes visible; select Create at the bottom of the window, which will display the Add Automation Account blade. Fill in the names, making them meaningful, as they can get lost when you have a large number of resources. I would recommend keeping these in the same resource group and the same data center as the SQL Azure Databases.

(Important Cost Saving Note: Not a requirement, but if you have a rule that says to keep resources groups in the same data center as your data, someone down the road does not create an Azure Data Lake in another data center with your Blobs in another one and you get hit with data transfer charges.)

Add in the Name, Subscription, and Resource Group information. Your automation does not have to be in the same location/datacenter as the Data you are going to work with, but you should think about the other objects, such as blob storage and the data you import’s location. You can limit the charges for data transfer if they are not between data centers.

I also selected Create Azure Run As Account, which is not required for running our example, but I normally select this option. Select Pin to Dashboard and then Create to kick off the process. I normally pin new objects to the dashboard to make it easier to find once complete. The success screen grab is pictured below.

Step 4 – Success.

Once you click Create, the resource’s blade is then displayed in the Azure Portal. If you did not select Pin to Dashboard, you could select from the notification you receive. Automation is one of those resources you are going to go into time and again, so having it as a Pin on the dashboard can be quite a time saver. You can also have multiple Dashboards; just saying.

The final dashboard item is pictured below.

Create Runbook Assets

Now that we have the automation account, we can start creating the assets we need to schedule and automate our routines.

A Quick overview of the important menu items for this section is pictured below.

  • Runbooks – This is where the runbooks are created and edited
  • Jobs – This is where the run book’s schedule will appear when complete
  • Runbooks Gallery – Never write from scratch when you can use it or at least start with a sample. Check out the gallery items to see what is available.
  • Schedules – This is where you set up the timeframes of when the runbooks will create Jobs and complete their tasks. These can be one-time, recurring, and also run until an expiry date and time are reached.
  • Credentials – These are the assets that your scripts / runbooks will pull into the process to get authorization to objects to complete their tasks.

Create Assets

Let’s create 2 Credential Assets for the runbook to have the authentication required.

SQL ID – This is a SQL Account that will be used to run the stored procedures and, obviously, needs to have the authorization to run the tasks in the stored procedures. See the reference section below, which contains the Appendix: Create SQL User For Azure Automation RunBooks for the script run against the SQL Azure DB.

Azure Active Directory Account – This account needs to have an O365 Email account attached to it.

For Both assets, follow the same procedure. Click on Credentials, which will bring up the Add a Credential blade and select Add a Credential, which, funny enough, brings up the Add a Credential form.

Fill in the details and hit the Confirm button at the bottom of the blade/form.

Follow the same steps to create the Email Credential.

Now that we have our credential Assets, the blade should look like the sample below.

Create the script

We have a stored procedure in the Azure SQL DB that copies all the records from a staging table to a production table. We want to have a script that runs those in a runbook. To maximize code reuse, we are going to use parameters and also provide default values, so we do not have to type in values each time we write a new schedule.

Before we create the Runbook, let’s review the script.

Section 1: Parameters

When a runbook Schedule is created, you will enter or override the default values as entered. The only parameter, in this example, that you must enter at the time of schedule creation is the SQL Credential. The script handles both credentials differently simply because this is a sample I also use for other training.

Remember to change the parameter default values to what you need. There is a copy of the script at the end of this document with the areas you need to modify.

Section 2: Setup Variables

The script itself is contained in the inline script section pictured below. The first section sets up the parameters needed by the script. The important one below is the Credential, as entered from the parameters above. The Write-Output text will appear in the Job details when the script completes and is detailed in the schedule section. Add in any other information you want to appear. However, make sure that you do not write any sensitive information. The output appears in the Logs and Job detail runs.

Section 3: Try Section

The Try section completes the following steps;

  • Takes the credentials from the Parameters
  • Connects to the SQL Azure DB
  • Creates the SQL Command that runs the stored procedure
  • Sets up a variable to accept the success or failure token
  • Change the Email Subject to success or failure

This setup can differ depending on how you set up error detection and handling within your stored procedure. You can have an error occur in the stored procedure, but the stored procedure can finish gracefully. Your return value can be sent to change the $Subject variable to send the correct subject in the email as an alert even if the runbook script completes successfully.  There is a sample stored procedure shell in the appendix.

Section 4: Send Email, Catch, and Finally

There are two places where the Email is sent, at the end of the Try, and if there were an error captured by the script, this information would also be added. Finally is where the connection is closed, and output is provided.

Create the Runbook

Now that we have created the Assets, we can create the Runbook. From the Azure Automation Account blade, select Runbooks. You will also notice that there are sample runbooks included when you create the Azure Automation Account resource.

We have two choices to create the runbook, to create or import the script. I normally create a new script, which provides the correct name in the script code. The name at the top of the script must be the same as the script name. Select Create at the end of the blade.

The conversation then brings up the Script Editor, which we paste the existing script into the code window.

Now that we have the script, click on the Save menu item, then we can select the Test Pane menu item to test our script.

After you select the Test Pane, it will run the last saved version of the Runbook. Enter the parameters and select Start.

A test job is submitted, which will go into the Queue, which could take up to 10 minutes to run. Once complete, you will receive one of the following emails, depending on success or failure.

Once the editing and testing are complete, Select Publish to finalize the editing process.

Schedule the Runbook

Using parameters in the runbooks allow for each run of the scripts to be set up to run the same way. The following is a walkthrough of the setup schedule and the resulting job.

Select a Runbook

Select the Runbook from the list which brings up the detail. Select Schedule from the menu.

You will click on both options pictured below to set up the schedule. First, Link A Schedule to the Runbook, then Create a new schedule, then you have the setup information to enter.

Pictured above is the creation blade. Enter a Name, Description, Date, and Time you want to schedule to begin, if you want to recur as pictured above, or run once pictured below, fill in your choices. Select the expire for the schedule if you only want the recurring run to happen for a specific timeframe. The below is set up for recurring each hour and until the expiry date and time.

Select Create at the bottom of the blade to create the schedule.

Once you create the schedule, the name is on the first tab; now select the Configure Parameters. All RunBooks that you create can have parameters that you can accept the defaults for or enter a value to override. You have to enter the values of some credential parameters.

Your Schedule will create a JOB once a specified time is reached, which you will find on the “RunBook” screen.

Selecting the Schedule tab will display a listing of the next runs of any and wil be displayed in the list as is pictured below.

Once the Schedule meets the criteria, a Job is run and shown in the Jobs Blade. Below is an example of the job that will be created. Each panel in the Overview section can be reviewed for different information based on the Below screen captures of various jobs. Clicking on the various items brings up running information and logs.

The following log sample is generated by the sample script provided in this walkthrough. In the script, any values specified by the Write-Output commands are output to the log. You can add the level of logging that you need to be generated in order to debug processes in your scripting.

Completed

Now the script is written, deployed, and scheduled. This process mimics the same pattern that many on-premises installations follow. Stored procedures are written and scheduled through automation, and email alerts are generated. This example provides the same framework but in Azure. By not changing and rewriting our process during a lift and shift type of project, you minimize the number of changes required.

Resources

Azure Automation documentation | Microsoft Learn

Sample Scripts Referenced in this article.

The scripts are also published on GitHub.

======================================
Part 1 Add SQL User
======================================




-- ========================================================================================
-- Create User as DBO template for Azure SQL Database and Azure SQL Data Warehouse Database
-- ========================================================================================
-- For login <login_name, sysname, login_name>, create a user in the database

--use [Master]   -- Note "Use  DB" is not avalable in SQL Azure DB
--Remember complexity requirements on your password
CREATE LOGIN  RunBookUserAccount WITH password='password!!!';

CREATE USER RunBookUserAccount
    FOR LOGIN RunBookUserAccount
    WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'RunBookUserAccount'
GO


-- ========================================================================================
--          Another Example, depending on your requirements
-- ========================================================================================
 

EXEC sp_droprolemember 'db_owner', 'RunBookUserAccount';  
GO

EXEC sp_addrolemember 'db_datareader', 'RunBookUserAccount';

GO

EXEC sp_addrolemember 'db_datawriter', 'RunBookUserAccount'; 
GO



======================================
Part 2 Axure Run Book
======================================




workflow RunStoredProcAzureWEmail
{
    [cmdletbinding()]
    param
    (
        # Fully-qualified name of the Azure DB server
        [parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string] $SqlServerName = "<DBSERVER>.database.windows.net",

        # Name of database to connect and execute against
        [parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string] $DBName = "<DBNAME>",

        # Name of stored procedure to be executed
        [parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string] $StoredProcName = "usp_Stage_To_Prod_Employee",

        # Credentials for $SqlServerName stored as an Azure Automation credential asset
        [parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [PSCredential] $Credential ,

        # Subject for the email
        [parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [String] $SubjectText = "Running of usp_Stage_To_Prod_Employee through runbook",

       # PowerShell Credentials for the Secure SMTP Service
        [parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [String] $AzureOrgIdCredentialString = "RunEmail", 
        
        [parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [String] $BodyText = "This is an automated mail send from Azure Automation relaying mail using Office 365.",
                        
        [parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [String] $ToUser ="<To User or Group in Operations>"
    )
    inlinescript
    {
        Write-Output "========================================================================"
        Write-Output “JOB STARTING”
        Write-Output "========================================================================"

        # Setup variables
        $ServerName = $Using:SqlServerName
        $UserId = $Using:Credential.UserName
        $Password = ($Using:Credential).GetNetworkCredential().Password
        $DB = $Using:DBName
        $SP = $Using:StoredProcName
        
        #Email Variables
        $Subject = $Using:SubjectText 
        $AzureOrgIdCredential = $Using:AzureOrgIdCredentialString
        $Body = $Using:BodyText
        $To = $Using:ToUser
        $From = "<RunBookEmailCred Email Address>.onmicrosoft.com"




        Try {
            #---------------------------------------------------------------------------------
            # Get the PowerShell Credentials from Azure Automation account assets (For Email)
            #---------------------------------------------------------------------------------               
                # Get the PowerShell credential and prints its properties
                $Cred = Get-AutomationPSCredential -Name $AzureOrgIdCredential
                if ($Cred -eq $null)
                {
                    Write-Output "Credential entered: $AzureOrgIdCredential does not exist in the automation service. Please create one `n"   
                }
                else
                {
                    $CredUsername = $Cred.UserName
                    $CredPassword = $Cred.GetNetworkCredential().Password
                    Write-Output "We have the Credential Username: $CredUsername"
                }

            # Create and Open the PowerShell Connection to the Database
            $DatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
            $DatabaseConnection.ConnectionString = “Data Source = $ServerName; Initial Catalog = $DB; User ID = $UserId; Password = $Password;”
            $DatabaseConnection.Open();
           
            Write-Output “Connection to the Database is open”

            # Create & Define command and query text
            $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
            $DatabaseCommand.CommandType = [System.Data.CommandType]::StoredProcedure
            $DatabaseCommand.Connection = $DatabaseConnection
            $DatabaseCommand.CommandText = $SP
            
            #Set up for return value that will drive the success or failur from the stored Procedure.
            $DatabaseCommand.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]"Int") 
            $DatabaseCommand.Parameters["@ReturnValue"].Direction = [System.Data.ParameterDirection]"ReturnValue"

            Write-Output “Excuting the Query: $StoredProcName”
           
            # Execute the query
            $OUT = $DatabaseCommand.ExecuteNonQuery()   | out-null

            Write-Output $OUT
            #Changed Code
            $returnValue = $DatabaseCommand.Parameters["@ReturnValue"].Value
            Write-Output "Return Value = $ReturnValue "

            if($ReturnValue -eq $null -or $ReturnValue -eq 0)
            {
                Write-Output "An Error Occured"
                $Subject = "Mail sent from Azure Automation using Office 365- $SP = Error in SP"
            } 
            else
            {
                Write-Output "Success"
                $Subject = "Mail sent from Azure Automation using Office 365- $SP = Successful"
            }
            Write-Output " TRY SECTION COMPLETED”
            Write-Output "========================================================================"
 
 
            # ---------------------------------------------------------------------------------
            # This section sends a mail using Office 365 secure SMTP services.  
            # ---------------------------------------------------------------------------------
            #$Subject = "Mail sent from Azure Automation using Office 365- $SP = Successful"
            Send-MailMessage -To $To -Subject $Subject  -Body $Body -UseSsl -Port 587 -SmtpServer 'smtp.office365.com' -From $From -Credential $Cred
              
            Write-Output "Success Mail is now sent `n"
            Write-Output " TRY Really Completed - Email sent `n”
            Write-Output "========================================================================"
        }
        Catch {
            # ---------------------------------------------------------------------------------
            # This section sends a mail using Office 365 secure SMTP services.  
            # ---------------------------------------------------------------------------------
            $Subject = "Mail sent from Azure Automation using Office 365- $SP = Failure"
            $Body = $_.Exception


                 Send-MailMessage -To $To -Subject $Subject  -Body $Body -UseSsl -Port 587 -SmtpServer 'smtp.office365.com' -From $From -Credential $Cred
              
                    Write-Output "Failure  Mail is now sent `n"
                    Write-Output "-------------------------------------------------------------------------"
                Write-Error -Message $_.Exception
                Throw $_.Exception

        }

        Finally{
            # Close connection to DB
            $DatabaseConnection.Close()
            Write-Output “CONNECTION CLOSED”
            Write-Output “JOB COMPLETED”
            Write-Output "-------------------------------------------------------------------------"
        }

    }

}


======================================
Part 3 Sample SQL Stored Procedure with Return values
       Includes Logging to SQL Table that is not included
       but left in as reference
======================================

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*Testing  
USE [TestLabTrainingDatabase]
GO

DECLARE @RC int
DECLARE @_Logging_FLAG char(1)

Set @_Logging_FLAG  = 'N' 

EXECUTE @RC = [dbo].[usp_Stage_To_Prod_Employee] 
   @_Logging_FLAG
GO

select count(*) from [dbo].[Prod_Employee]

select count(*) from [dbo].[Stage_Employee]

*/

Create PROCEDURE [dbo].[usp_Stage_To_Prod_Employee]
    @_Logging_FLAG CHAR(1) = 'N' 
    
AS




BEGIN
-- Return Value for Stored Proc
    DECLARE @Returns INT
    SET @Returns = 1 


--XACT_ABORT On
SET NOCOUNT ON;
    --  ===================================================================   
    --               Declare Variables
    --  =================================================================== 
    DECLARE @RC INT;
    DECLARE @_TableName VARCHAR(100);
    DECLARE @_PkgName VARCHAR(100);
    DECLARE @_CommandTxt VARCHAR(2000);
    DECLARE @_RunStage VARCHAR(100);
    DECLARE @_ExecStartDT DATETIME;
    DECLARE @_ExecStopDT DATETIME;
    DECLARE @_ExtractRowCnt BIGINT;
    DECLARE @_InsertRowCnt BIGINT;
    DECLARE @_UpdateRowCnt BIGINT;
    DECLARE @_ErrorRowCnt BIGINT;
    DECLARE @_TableInitialRowCnt BIGINT;
    DECLARE @_TableFinalRowCnt BIGINT;
    DECLARE @_TableMaxDateTime DATETIME;
    DECLARE @_SuccessfulProcessingInd CHAR(1);
    DECLARE @_Notes VARCHAR(1000);
    DECLARE @_MasterBatchNumber BIGINT;
    DECLARE @_ChildBatchNumber BIGINT;
    DECLARE @_SQLErrorNumber  INT;
    DECLARE @_SQLErrorLine INT;
    DECLARE @_SQLErrorMessage VARCHAR(1024); 
    DECLARE @_SQLErrorProcedure VARCHAR(1024);
    DECLARE @_SQLErrorSeverity INT; 
    DECLARE @_SQLErrorState INT; 
    DECLARE @_Azure_Input_File VARCHAR(256); 
    DECLARE @_SQL_Statement NVARCHAR(1024); 
    DECLARE @_SQL_Statement_Truncate NVARCHAR(1024);
    --Error checking 
    DECLARE @Err INT;
    DECLARE @ErrPoint VARCHAR(100);

        --  ===================================================================   
    --               Setup 
    --  =================================================================== 

    --  !!!!!!!!!!!!!!!!! Change for each Stored Procedure !!!!!!!!!!!!!!!!!!!!!!!
    --  Change for each package
    SET @_TableName = '[dbo].[Prod_Employee]';
    SET @_PkgName =  '[usp_Stage_To_Prod_Employee]';
    SET @_RunStage = 'Package Start';
    
    --  Starting values
    SET @_CommandTxt = NULL;
    SET @_ExecStartDT = GETDATE();
    SET @_ExecStopDT =  GETDATE();
    SET @_ExtractRowCnt = NULL;
    SET @_InsertRowCnt = NULL;
    SET @_UpdateRowCnt = NULL;
    SET @_ErrorRowCnt = NULL;
    SET @_TableInitialRowCnt = NULL;
    SET @_TableFinalRowCnt = NULL;
    SET @_TableMaxDateTime = NULL;
    SET @_SuccessfulProcessingInd = 'N';
    SET @_Notes = 'Stage to Production ';
    EXECUTE [dbo].[usp_Audit_BatchNumber] @_MasterBatchNumber OUTPUT;
    --Set @_MasterBatchNumber = 33
    SET @_ChildBatchNumber = NULL;
    SET @_SQLErrorNumber = NULL;
    SET @_SQLErrorLine = NULL;
    SET @_SQLErrorMessage = NULL;
    SET @_SQLErrorProcedure = NULL;
    SET @_SQLErrorSeverity = NULL;
    SET @_SQLErrorState = NULL;


--  ================= Error Handle Begin Try ================================ 
    BEGIN TRY  
 
      BEGIN TRANSACTION

    IF (@_Logging_FLAG = 'Y') 
    BEGIN 
    PRINT '[Stage to Prod] - Delete Prod Table.'; 
    PRINT ' '; 
    END; 


      --  !!!!!!!!!!!!!!!!! Change for each Stored Procedure !!!!!!!!!!!!!!!!!!!!!!!
      --  ================= Starting Rows Table ================================ 
     Select  @_TableInitialRowCnt =  Count(*) From [dbo].[Prod_Employee] 



    --  ================= Clear out Table ================================ 
    DELETE  [dbo].[Prod_Employee];


    IF (@_Logging_FLAG = 'Y') 
    BEGIN 
    PRINT '[Copy Table] - Begin Insert.'; 
    PRINT ' '; 
    END; 

    -- Set RunStage
    SET @_RunStage = 'Insert';
 
    --  ================= Insert Into Table ================================
INSERT INTO [dbo].[Prod_Employee]
           (EmployeeID
           ,FacilityID
           ,OrganizationLevel
           ,SalesPersonFlag
           ,JobTitle
           ,BirthDate
           ,MaritalStatus
           ,Gender
           ,HireDate
           ,SalariedFlag
           ,VacationHours
           ,SickLeaveHours
           ,CurrentFlag
           ,ModifiedDate
           ,NameFull
           ,PayGradeID)
    Select 
            EmployeeID
           ,FacilityID
           ,OrganizationLevel
           ,SalesPersonFlag
           ,JobTitle
           ,BirthDate
           ,MaritalStatus
           ,Gender
           ,HireDate
           ,SalariedFlag
           ,VacationHours
           ,SickLeaveHours
           ,CurrentFlag
           ,ModifiedDate
           ,NameFull
           ,PayGradeID
           From [dbo].[Stage_Employee]

            --  ================= Grab Row Changed ================================ 
        Select @_InsertRowCnt = @@ROWCOUNT
        
        --  !!!!!!!!!!!!!!!!! Change for each Stored Procedure !!!!!!!!!!!!!!!!!!!!!!!
        --  ================= Starting Rows Table ================================ 
     Select  @_TableFinalRowCnt =  Count(*) From [dbo].[Prod_Employee] 
    
      --  ================= How long did the insert take ================================ 
      Set @_ExecStopDT =  getdate()

    -- Set RunStage
    SET @_RunStage = 'Insert Success';
    
        --  ================= Write Successful Input Log Entry ================================  
    EXECUTE @RC = [dbo].[usp_Insert_Audit_ProcessLog_Entry] 
        @_TableName,@_PkgName,@_SQL_Statement,@_RunStage,@_ExecStartDT,@_ExecStopDT,@_ExtractRowCnt,@_InsertRowCnt,
        @_UpdateRowCnt,@_ErrorRowCnt,@_TableInitialRowCnt,@_TableFinalRowCnt,@_TableMaxDateTime,@_SuccessfulProcessingInd,
        @_Notes,@_MasterBatchNumber,@_ChildBatchNumber,@_SQLErrorNumber,@_SQLErrorLine,@_SQLErrorMessage,@_SQLErrorProcedure,
        @_SQLErrorSeverity,@_SQLErrorState; 

  
 

      --  ================= How long did the Proces take ================================ 
      Set @_ExecStopDT =  getdate()
    -- Set RunStage
    SET @_RunStage = 'Process Complete';
    SET @_SuccessfulProcessingInd = 'Y';

    -- COMMIT the transaction if successful
      COMMIT
        --  ================= Write Successful Transform Log Entry ================================  
    EXECUTE @RC = [dbo].[usp_Insert_Audit_ProcessLog_Entry] 
        @_TableName,@_PkgName,@_SQL_Statement,@_RunStage,@_ExecStartDT,@_ExecStopDT,@_ExtractRowCnt,@_InsertRowCnt,
        @_UpdateRowCnt,@_ErrorRowCnt,@_TableInitialRowCnt,@_TableFinalRowCnt,@_TableMaxDateTime,@_SuccessfulProcessingInd,
        @_Notes,@_MasterBatchNumber,@_ChildBatchNumber,@_SQLErrorNumber,@_SQLErrorLine,@_SQLErrorMessage,@_SQLErrorProcedure,
        @_SQLErrorSeverity,@_SQLErrorState; 


    --  ================= End Try ================================  
    END TRY 

    
    --  ================= Begin Catch ================================  
    BEGIN CATCH 
           IF @@TRANCOUNT > 0
         ROLLBACK

         --Set Error Return Value
         SET @Returns = 0 
    --  ================= Grab values and Write Log Entry ================================
    SELECT 
        @_SQLErrorNumber = ERROR_NUMBER(), 
        @_SQLErrorProcedure = ERROR_PROCEDURE(), 
        @_SQLErrorLine = ERROR_LINE(), 
        @_SQLErrorMessage = ERROR_MESSAGE(), 
        @_SQLErrorSeverity = 16,--ERROR_SEVERITY(),
        @_SQLErrorState = 1,--ERROR_STATE(),
        @_SuccessfulProcessingInd = 'N';


        --Log Error
        SET @_RunStage = 'Process Error';
        --  ================= Send Error Log Entry ================================  
        EXECUTE @RC = [dbo].[usp_Insert_Audit_ProcessLog_Entry] 
        @_TableName,@_PkgName,@_CommandTxt,@_RunStage,@_ExecStartDT,@_ExecStopDT,@_ExtractRowCnt,@_InsertRowCnt,
        @_UpdateRowCnt,@_ErrorRowCnt,@_TableInitialRowCnt,@_TableFinalRowCnt,@_TableMaxDateTime,@_SuccessfulProcessingInd,
        @_Notes,@_MasterBatchNumber,@_ChildBatchNumber,@_SQLErrorNumber,@_SQLErrorLine,@_SQLErrorMessage,@_SQLErrorProcedure,
        @_SQLErrorSeverity,@_SQLErrorState; 

    -- Raise error 
    RAISERROR ('An error occurred within a user transaction. 
                Error Number        : %u 
                Error Message       : %s  
                Affected Procedure  : %s 
                Affected Line Number: %u
                Error Severity      : %u
                Error State         : %u' 
                , 16, 1 
                , @_SQLErrorNumber, @_SQLErrorMessage, @_SQLErrorProcedure, @_SQLErrorLine, @_SQLErrorSeverity, @_SQLErrorState);       
  
    -- ** Error Handling - End Catch **    
    END CATCH;     
        RETURN @Returns      
    END;
GO

Related

Steve Young

With over 34 years of experience in the data and technology industry, the last 16 with Microsoft, I have had the opportunity to work in various capacities, contributing to my knowledge and expertise in Data Engineering, Power BI, and Data Visualization.

Facebook Twitter Instagram YouTube Linkedin Pinterest

Meta

  • Log in
  • Entries feed
  • Comments feed
  • Powered by WordPress.com.

Disclaimer: The views expressed are my own. I offer no guarantees for the accuracy of the information shared, and it is for educational purposes only.

All non-original photography is sourced and licensed from my account on PEXELS,  STORYBLOCKS, iStockPhoto, and Pixabay. Please use our Contact Page if you have a question.

The information provided on this blog is for educational purposes only. Steve Young is not responsible for any errors or omissions or for any actions taken based on the information provided on this blog.

© 2023 5MinuteBI

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.Accept Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT
  • Home
  • Blog
  • About
  • Contact