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

• MG-148046

  SSCrazy Eights

  Points: 9946

  More actions

  September 10, 2010 at 7:21 am

  #1219141

  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

  September 10, 2010 at 7:52 am

  #1219166

  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

  September 10, 2010 at 8:04 am

  #1219176

  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.