Random Thunks: 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!