SQL Azure Stored Procedure Automation Using Runbooks and Email

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 which 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 was in production in on-premesis 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 redesigning and updating the applications that used the databases would be for a followup 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 that 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 the 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

Lets 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 keep resources groups in the same data centre as your data, someone down the road does not create an Azure Data Lake in another data centre 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 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, 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 and are pictured below..

Runbooks – This is where the runbooks are created and edited
Jobs – This is where the run book’s scheduled will appear when complete
Runbooks Gallery – Never write from scratch when you can use 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 simply 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 with 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 be different depending on how you setup 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 placed where the Email is sent, at the end of the Try, and if there was an error captured by the script, this information would also be added. The Finally is where the connection is closed, and output 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 a 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 values of some credential parameters.

Your Schedule will create a JOB once a specified time is reached, which you 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. The 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 are screen captures of various jobs. Clicking on the various items bring 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 schedules. This process mimics the same pattern that many on premises installations follow. Stored procedures are written, 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 amount of changes required.

Resources

Get started with Azure Automation: https://docs.microsoft.com/en-us/azure/automation/automation-offering-get-started#authentication-planning

The following Reference provides additional information on Azure Automation.

Overview – https://docs.microsoft.com/en-us/azure/automation/automation-intro

Intro and Setup – https://docs.microsoft.com/en-us/azure/automation/automation-offering-get-started

Workflow Runbook Tutorial – https://docs.microsoft.com/en-us/azure/automation/automation-first-runbook-textual

Create a new Runbook – https://docs.microsoft.com/en-us/azure/automation/automation-first-runbook-textual

The Azure Runbook credential functionality has the following resources;

Credential assets in Azure Automation – https://docs.microsoft.com/en-us/azure/automation/automation-credentials

Sending email from Azure Automation using Office 365 (Secure SMTP) – https://gallery.technet.microsoft.com/office/Sending-email-from-Azure-24ee27cd

Azure Log Analytics – https://azure.microsoft.com/en-us/updates/send-your-runbook-job-status-and-job-streams-from-automation-to-log-analytics-oms/ and https://docs.microsoft.com/en-us/azure/automation/automation-manage-send-joblogs-log-analytics

Sample Script – https://gallery.technet.microsoft.com/office/Sending-email-from-Azure-24ee27cd

Sample Scripts Referenced in this article.

They scripts are also published 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

SQL Azure Stored Procedure Automation Using Runbooks and Email

Overview

Scenario

Create the Azure Automation Account