adam-gligor monologue about stuff, brain dump

PowerShell tip - exit code

A PowerShell script executed by SQL server using xp_cmdshell to return a numeric value.

A PowerShell script executed by SQL server using xp_cmdshell returns usually a table so one has to assign the result to a table variable, then query that.

Suppose that the script is doing some kind of validation and only the outcome (as in true/false) is of interest. Using exit codes it is possible to have the result straight as an int variable in SQL server and not deal with tables. Here’s how …

The problem

The original version of the script was this:

validate.ps1 

try
{
    # do stuff ... 

    # no errors
    return $false
}
catch [System.Xml.Schema.XmlSchemaValidationException]
{
    # errors
    return $true
}

The fragment that executes it from SQL server


DECLARE @strCommandValidate VARCHAR(1000)
DECLARE @intError INTEGER

SET @strCommandValidate = 'powershell validate.ps1'
EXEC @intError =  xp_cmdshell @strCommandValidate

SELECT @intError

Result will be a table:

ps

Solution

To have the result straight as an int when executed SQL server one has to return only exit codes from the script

Read on exit codes here

So with this update the new script is:

validate2.ps1 

$validationError = 0 

try
{
    # do stuff ... 
}
catch [System.Xml.Schema.XmlSchemaValidationException]
{
    $validationError = 1
}

EXIT $validationError

And the result will be a single int value:

ps