SQL Job not failing when Powershell script runs and Fails

Question

SQL Job not failing when Powershell script runs and Fails

schwizla

SSCrazy

Points: 2668
More actions
September 9, 2010 at 5:47 am

#387754

I have a sql job step that runs a powershell script using XP_cmdshell as follows
EXEC xp_cmdshell 'powershell.exe %Deployment_root%apps\Systems\sql\DR\ScriptSQLServerObjects.ps1 "gssqlbackup"'
However, if the script fails the job finishes successfully. I want the SQL Job to fail when the powershell script fails and also update the code below so that it outputs the error to a log file
The code in my script "ScriptSQLServerObjects.ps1" is as follows. The first part loads the SQLPS provider (obtained from MSDN) and the second part scripts every Job on the SQL Server for Backup purposes
[PowerShell Code BLock Start]
# Pass the Backup Share Location into the script i.e ScriptsSQLServerObjects "$BackupShare"
param ([string]$BackupShare)
# Add the SQL Server Provider.
$ErrorActionPreference = "Stop"
$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
$item = Get-ItemProperty $sqlpsreg
$sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}
#
# Set mandatory variables for the SQL Server provider
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000
#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml
Pop-Location
# Set the Server Name and Default starting location
$SName = 'L3PSQL71'
Set-Location C:
# Load the Dot Net Assembly For SQL Server SMO. Out-Null indicates no output of the GAC and Version
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
# Assign the Scripting Options to a variable
$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
# Set the Input and Output Location Variables
$JobsOutputLocation = '\\' + $BackupShare + '\' + $SName + 'Failover\OtherBits\' + $SName + '_SQLJob - '
$SQLServerJobsPath = 'SQLSERVER:\sql\' + $SName + '\default\JobServer\Jobs'
# Script the Jobs
$so.IncludeDatabaseContext = 1
gci -path $SQLServerJobsPath | %{$Job = $_.Name; $_.script($so) | out-file -filepath $JobsOutputLocation$Job.sql}
[PowerShell Code BLock END]
The $ErrorActionPreference is set to stop. So even though I can get it to fail, this failure does not cause the SQL job to fail. How can I trap the failure and also outut the failure details to a log file?
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 1

You might try this

EXEC xp_cmdshell 'powershell.exe %Deployment_root%apps\Systems\sql\DR\ScriptSQLServerObjects.ps1 "gssqlbackup"'; exit $LASTEXITCODE

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 2

dibbydibby (9/9/2010)
The $ErrorActionPreference is set to stop. So even though I can get it to fail, this failure does not cause the SQL job to fail. How can I trap the failure and also outut the failure details to a log file?

Caveat: I'm learning this PowerShell stuff as I go 😀

From what I have read when you use $ErrorActionPreference = "Stop", you can then use the Trap command before your command you think it might be erroring on. Setting it to Stop will allow the cmdlet to report the error to the trap so you can capture it. Read a little further and you can actually capture the message and write it out.

# Script the Jobs

$so.IncludeDatabaseContext = 1

Trap {Write-Host -force Red -back White $_.Exception.Message; Continue};

gci -path $SQLServerJobsPath | %{$Job = $_.Name; $_.script($so) | out-file -filepath $JobsOutputLocation$Job.sql}

[PowerShell Code BLock END]

The Continue is treated like the ErrorAction "SilentlyContinue". Note I got all this info from here:

http://powershell.com/cs/blogs/ebook/[/url], see Chapter 11.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 3

I second your caveat :-D:-D

Here is a link to a pretty good explanation of error handling for PoSh:

http://huddledmasses.org/trap-exception-in-powershell/

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.