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 

# 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}

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

• Edited by Schwizla Thursday, September 09, 2010 10:20 AM wrong title

An unhandled exception or a throw will cause powershell.exe to send a a failure signal to SQL Server Agent. See this MSDN forum post for details:

http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/d511fce9-fa79-4f92-bc44-72d6d562930a/#9beea9fc-337f-4af5-94e6-c5ea81b2c92b

If you're using PowerShell V2 I would suggest using a try/catch block with a throw statement:

try

{

$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}

}

catch

{

$ex = $_.Exception

 throw "$ex.Message"

break

}

I assume that your reference to a SQL job means that you are running a Transact-SQL job step in SQL Server Agent. In that case, I'd simplify this a lot by not using a TSQL job step and xp_cmdshell. I don't think your failure is getting transmitted from the DB Engine back to Agent.

If you're running on SQL Server 2008 and SQL Server 2008 R2, I would instead use a PowerShell script job step. That will launch a sqlps.exe process that will load in both the SQL Server provider and SMO dlls, so you can simplify the script by removing that logic and just focusing on the SMO scripting logic.

If you need to run on an earlier release, like SQL 2005, then I'd create a .cmd file that called powershell.exe, and then set up a job step that executed that.

With a TSQL job step, Agent is connecting to the DB Engine and sending down a batch with the EXEC xp_cmdshell statement. That then creates a process on the computer running the instance of the DB Engine, in which PowerShell is launched to run your .ps1 script. xp_cmdshell only returns a 0 or 1 as a return code, but even if your script caused PowerShell to cause xp_cmdshell to return 1, I don't know that the return code would get sent back to Agent because it's technically not a tsql statement error.

If you want to keep the TSQL job step, then I"d put the logging either in the script itself, or bulk up the TSQL batch to detect an xp_cmdshell failure and launch the logging from there:

DECLARE @result int
EXEC @result = xp_cmdshell 'powershell.exe %Deployment_root%apps\Systems\sql\DR\ScriptSQLServerObjects.ps1 "gssqlbackup"'
IF (@result <> 0)
   -- Put your logging logic here, or do a RAISERROR statement that would signal a TSQL error likely to be transmitted back to Agent.

Hi Alan, thanks for the answer, declaring the result as a variable sounds like an interesting approach which I will try. Originally I did use a powershell step but the decided I wanted to call a powershell script from a central location (as the script is used by many SQL Servers with the same SQL job existing on each server). This way I only need to update the script with changes in one place. I found that with the code directly in a Powershell job step meant that any small change needed to be updated on every occurance of that job in all my SQL environments. Hence I went with the XP_cmdshell option. Maybe I could have done this differently?

cmille19, the try\cacth did not work for me :-(

Hi Alan,

I'm using PowerShell to kickoff a job in SQL using the PowerShell type and I've tried exitting with errorlevel 1 and throwing an exception but neither will cause the step to fail. What am I doing wrong?

I can sort of get this to work - is there any cleaner method?  This is what is defined in my PowerShell job (SQL Server 2008):

$Result = powershell "& J:\FIMTasks\RunMA.ps1 -MAName 'FIM_MA' -RunProfileName 'DIDS' -FIMServerName 'tstsql-inst01'"
if ($Result -ne "success") {throw $Result}
else {write-output $Result}

However, I've lost some of the textual output of the script since I'm parsing the string output by itself and have had to limit the output of the script to only the basic status message.

I have to admit I'm not a SQL Agent expert, the only thing I've aware of for running a script is setting up a SQL Server multi-server environment. You could then use the Targets dialog in the Agent Job entry to specify the servers you want the script to run on. It might be possible to also set something up with registered server groups, but I'm not sure of the interaction with groups and jobs.

For those interested I did get this working so I thought I'd share. The Job now fails with a meaningfull error

 try
 {
 Get-ChildItem -path $SQLServerJobsPath | %{$Job = $_.Name; $_.script($so) | out-file -filepath $JobsOutputLocation$Job.sql} -ea continue
 }
catch
 {
 $output = $_.ErrorDetails
 throw $output
 }

 

cmille19, your try catch example pointed me in the right direction so thank you

• Marked as answer by Schwizla Friday, September 24, 2010 2:41 PM

Hey Schwizla,

Do u have this script working i am looking for same solution can u please send me the script if its working for you.

Thanks