How To Automate SQL Azure Stored Procedures 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 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
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.
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
