How to Pass the Result of a Stored Procedure to a JAMS Variable

It is possible using PowerShell to invoke a SQL Command in order to easily pass the result of a stored procedure to a JAMS Variable so that the value can be shared with any other Job.

#
# Import Modules
#
Import-Module SQLServer
Import-Module JAMS

#
# Perform the query, note that you must declare a SQL variable and select the variable as a result
#
$interim =  Invoke-SqlCmd -ServerInstance "<<ServerName>>" -database "<<DatabaseName>>" -Query "declare @P_TxnNum int; exec <<PROC_NAME>> @P_TxnNum output, 'GV10'; select @P_TxnNum as result";

#
# Get the result, store as a powershell variable
#
$INTERVAL=$interim.result

#
# Set the JAMS Variable
#
Set-JAMSVariable \DEV\INTERVAL -Value $INTERVAL -Server JAMS-UAT 

Write-Host "Current Interval Value:"$INTERVAL

if ( $error.Count -ne 0 )
{
                throw $error[0].Exception
}

The SQLPS Module is imported to allow the use of Invoke-SqlCmd. This allows the query to be performed in PowerShell, and capture the result. Then, setting a JAMS variable with the result value.

The SQLPS Module is included in SQL installation packages 2012 and up. If SQL 2012 is not installed, the components can still be installed without issue.

Once the Variable Value has been updated, it can be referenced in any other Job Source using double angle brackets. <<>>

Simply use double angle brackets, but include the full path to the variable <<\Folder\VariableName>> within the Job Source if there are multiple Variables with the same name in different locations.

Information on Parsing JAMS variables can be found here.

Parsing Parameters and Variables

Comments