Sql Agent Session Storage
I’ve been fiddling around with some jobs recently that required values to be kept from one job step to another. Not finding anything much exciting I went for plan Z – roll my own.
They’re simple procedures but do work nicely.
First I create a Schema that everything will be bundled under:
- If Not Exists ( Select 1 From sys.schemas Where Name = 'Aracs.Utilities.Jobs')
- Exec sp_ExecuteSql N'Create Schema [Aracs.Utilities.Jobs]';
Now we’ll need a backing store:
- Create Table [Aracs.Utilities.Jobs].SessionSaverStore
- (
- JobId UniqueIdentifier Not Null
- ,ParameterName NVarChar(128) Not Null
- ,Value Sql_Variant Null
- );
Next I create the SessionDestroyer Procedure (since it can be called from the next one this keeps the dependencies happy and calm):
- If Object_Id('[Aracs.Utilities.Jobs].SessionDestroyer') Is Null
- Exec sp_ExecuteSql N'Create Procedure [Aracs.Utilities.Jobs].SessionDestroyer As';
- Go
- Alter Procedure [Aracs.Utilities.Jobs].SessionDestroyer
- As
- Declare @JobHex NVarChar(32)
- ,@JobId UniqueIdentifier
- ;
- Select @JobHex = Substring(Program_Name, 32, 32)
- From sys.dm_Exec_Sessions
- Where Session_Id = @@Spid;
- Delete [Aracs.Utilities.Jobs].SessionSaverStore
- Where JobId = Cast('' as xml).value('xs:hexBinary(sql:variable("@JobHex") )', 'VarBinary(MAX)');
Along comes the SessionSaverStore next:
- If Object_Id('[Aracs.Utilities.Jobs].SessionSaverStore') Is Null
- Create Table [Aracs.Utilities.Jobs].SessionSaverStore
- (
- JobId UniqueIdentifier
- ,ParameterName NVarChar(128)
- ,Value Sql_Variant
- );
- If Object_Id('[Aracs.Utilities.Jobs].SessionSaver') Is Null
- Exec sp_ExecuteSql N'Create Procedure [Aracs.Utilities.Jobs].SessionSaver As';
- Go
- Alter Procedure [Aracs.Utilities.Jobs].SessionSaver
- @ParameterName NVarChar(128)
- ,@Value Sql_Variant
- ,@StartFresh Bit = 0
- As
- Declare @JobHex NVarChar(32)
- ,@JobId UniqueIdentifier
- ;
- Select @JobHex = Substring(Program_Name, 32, 32)
- From sys.dm_Exec_Sessions
- Where Session_Id = @@Spid;
- Set @JobId = Cast('' as xml).value('xs:hexBinary(sql:variable("@JobHex") )', 'VarBinary(MAX)');
- If Exists ( Select 1 From MSDB.dbo.sysJobs Where Job_Id = @JobId )
- Begin
- If @StartFresh = 1
- Exec [Aracs.Utilities.Jobs].SessionDestroyer;
- If Exists (
- Select 1
- From [Aracs.Utilities.Jobs].SessionSaverStore
- Where JobId = @JobId
- And ParameterName = @ParameterName
- )
- Update [Aracs.Utilities.Jobs].SessionSaverStore
- Set Value = @Value
- Where JobId = @JobId
- And ParameterName = @ParameterName;
- Else
- Insert
- Into [Aracs.Utilities.Jobs].SessionSaverStore
- (
- JobId
- ,ParameterName
- ,Value
- )
- Values
- (
- @JobId
- ,@ParameterName
- ,@Value
- );
- End
Finally the SessionRetriever:
- If Object_Id('[Aracs.Utilities.Jobs].SessionRetriever') Is Null
- Exec sp_ExecuteSql N'Create Procedure [Aracs.Utilities.Jobs].SessionRetriever As';
- Go
- Alter Procedure [Aracs.Utilities.Jobs].SessionRetriever
- @ParameterName NVarChar(128)
- ,@Value Sql_Variant Output
- As
- Declare @JobHex NVarChar(32)
- ,@JobId UniqueIdentifier
- ,@Valuei Sql_Variant
- ,@DataType NVarChar(128)
- ;
- Select @JobHex = Substring(Program_Name, 32, 32)
- From sys.dm_Exec_Sessions
- Where Session_Id = @@Spid;
- Set @JobId = Cast('' as xml).value('xs:hexBinary(sql:variable("@JobHex") )', 'VarBinary(MAX)');
- If Exists ( Select 1 From MSDB.dbo.sysJobs Where Job_Id = @JobId )
- Begin
- Select @Value = Value
- From [Aracs.Utilities.Jobs].SessionSaverStore
- Where JobId = @JobId
- And ParameterName = @ParameterName;
- End
Use is pretty straightforward. As an Proof of concept I created a Sql Agent Job with two steps – Store’ and ‘Fetch’
Store contained the following:
- Exec [Aracs.Utilities.Jobs].SessionSaver 'Name', 'Fred', 1
- Exec [Aracs.Utilities.Jobs].SessionSaver 'Age', 37
And Fetch:
- Declare @Name Sql_Variant
- Declare @Age Sql_Variant
- Exec[Aracs.Utilities.Jobs].SessionRetriever 'Name', @Name Output;
- Exec[Aracs.Utilities.Jobs].SessionRetriever 'Age', @Age Output;
- Print Cast(@Name As VarChar(128)) + ' ' + Cast( @Age As VarChar(4));
Granted it’s missing some bells and whistles (like exception handling) and I’d wager I could stuff the Retriever in as a function. However as a first hit I was pretty chuffed with it!
