SQL Server Agent Error Logging for Powershell Job Steps

By:   |   Comments   |   Related: > PowerShell


Problem

Unfortunately the SQL Server Agent error logging can be deceiving for job steps when you run PowerShell scripts. I had a situation where the job looked like it finished properly, but after investigating I noticed that the job did not run as planned. In this tip I explain how I setup my SQL Server Agent Jobs to get correct error messaging from my PowerShell scripts.

Solution

Recently I had set up an index maintenance procedure for one of my clients to run off hours. While the index maintenance job was running, the Windows error log on the web server became full of warning messages about application timeouts, because there were still clients accessing the website after hours.

As a "quick and dirty" workaround, I chose to redirect the client's website to a maintenance page while running the index maintenance procedure and then revert this change after the maintenance was completed. According to the product documentation, this involves a sequence that I found suitable for automation through a SQL Server Agent Job having these steps:

  • PowerShell step - changing the website's application pool mode to "classic" (to avoid a redirect loop)
  • PowerShell step - changing the web.config file to allow the redirection to the maintenance page
  • T-SQL step - run the index maintenance procedure
  • PowerShell step - revert the site's application pool mode back to "integrated" (which is the default)
  • PowerShell step - revert the changes done to the web.config file

Because I needed to work with objects not inside SQL Server (files and application pool settings), I expected permission issues. For example, let's try to change the application pool mode using the following PowerShell script: 

#you need this module to access and change the app pool settings
Import-Module WebAdministration
Write-Host -ForegroundColor Green "WebAdministration module imported..."
#getting the app pool(s) you need to change
$pool = Get-ChildItem IIS:\AppPools | where {$_.Name -eq "your_app_pool"}
Write-Host -ForegroundColor Green "application pool found..."
#changing the app pool mode value
$pool.managedPipelineMode = "Classic"
$pool | Set-Item
Write-Host -ForegroundColor Green "Done..."

non-terminating errors

As you can see from the results above, you need to run the script under an administrative account. If you go to the "computer management" console you'll see that the app pool's mode was unchanged. There is no error handling in this example, so I inserted a few Write-Host lines just to show the errors are non-terminating.

Now try to run the script under an administrative account - run PowerShell or your ISE "as an administrator". This time there are no errors and the setting is changed as expected. I addressed the permission issue in SQL Server Agent by running the PowerShell steps under a proxy account based on an admin account, but what if I forget to change the default proxy, let's see how to catch PowerShell errors in SQL Server Agent.

Create Sample SQL Server Agent Job

Let's create a SQL Server Agent Job with one step that runs the PowerShell script above. I saved the file and named it changing_settings_no_error_handling.ps1. To test the issue of not catching the error, make sure the step executes with a non-privileged account, such as the SQL Server Agent service account.

USE [msdb]
GO

/****** Object:  Job [ChangingAppPoolSettings_no_err_handling]  ******/
BEGIN TRANSACTION
   DECLARE @ReturnCode INT
   SELECT  @ReturnCode = 0
   
   /****** Object:  JobCategory [[Uncategorized (Local)]]  ******/
   IF NOT EXISTS ( SELECT name 
                   FROM msdb.dbo.syscategories
                   WHERE name = N'[Uncategorized (Local)]'
                   AND category_class = 1 )
   BEGIN
      EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB', 
      @type = N'LOCAL', @name = N'[Uncategorized (Local)]'
      IF ( @@ERROR <> 0 OR @ReturnCode <> 0 )
         GOTO QuitWithRollback
   END
    
   DECLARE @jobId BINARY(16)
    
   EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'ChangingAppPoolSettings_no_err_handling',
   @enabled = 1, @notify_level_eventlog = 0, @notify_level_email = 0,
   @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0,
   @description = N'No description available.',
   @category_name = N'[Uncategorized (Local)]',
   @owner_login_name = N'your_login', @job_id = @jobId OUTPUT
   IF ( @@ERROR <> 0 OR @ReturnCode <> 0 )
      GOTO QuitWithRollback
       
   /****** Object:  Step [PowershellScript] *****/
   EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,
   @step_name = N'PowershellScript', @step_id = 1, @cmdexec_success_code = 0,
   @on_success_action = 1, @on_success_step_id = 0, @on_fail_action = 2,
   @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0,
   @os_run_priority = 0, @subsystem = N'PowerShell',
   @command = N'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe  Your_script_location\changing_settings_no_error_handling.ps1',
   @database_name = N'master', @flags = 0
   IF ( @@ERROR <> 0 OR @ReturnCode <> 0 )
      GOTO QuitWithRollback
    
   EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
   IF ( @@ERROR <> 0 OR @ReturnCode <> 0 )
      GOTO QuitWithRollback
       
   EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
   IF ( @@ERROR <> 0 OR @ReturnCode <> 0 )
      GOTO QuitWithRollback
   
COMMIT TRANSACTION
GOTO EndSave
    
QuitWithRollback:
IF ( @@TRANCOUNT > 0 )
   ROLLBACK TRANSACTION
 
EndSave:
GO

In SSMS, right click the job and choose "Start job...". You'll see that it looks likes everything ran without issue. However, if you check the application pool's "managed pipeline mode" setting, you'll find it is unchanged. Check the job history - there is no error reported at the job level, the message says that "The job succeeded...".

But if we look at the job step we can see a message telling us that something is wrong: "The job script encountered the following errors. These errors did not stop the script...". However, the message ends with "Process Exit Code 0. The step succeeded.". Sure this is confusing and if the job has more steps that follow "on success" or "on failure", the workflow will lead to unexpected results, since the previous step is "successful", but in fact it did not change the app pool setting as expected.

job history for a script having no error handling

From this message you can see that the root of the problem is that the errors encountered don't stop the PowerShell script from running - they are non-terminating errors and the script doesn't have any error handling.

Add Error Handling to the PowerShell Script

Let's add error handling to the PowerShell script and run the SQL Server Agent Job again.

Here is an updated version of the initial PowerShell script above. I removed the Write-Host commands and the comments. I also put in the actual name of the app pool that I am working with. I also added Try Catch code to the script.

$ErrorActionPreference = "Stop"try{
   Import-Module WebAdministration
   $pool = Get-ChildItem IIS:\AppPools | where {$_.Name -eq "culinary"}
   $pool.managedPipelineMode = "Classic"
   $pool | Set-Item
   }
catch{
   #use Throw or Write-Error here
   Throw
     }

After running the SQL Server Agent Job again, the "error reporting" is the same - apparently all went OK and you have to "dig deeper" to realize that this is not true.

Add Error Handling to SQL Server Agent Job Step

What if I also add error handling to the Command textbox of the step properties screen, as shown below:

$ErrorActionPreference = "Stop"
try{
   C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe  C:\Notes\MSSQLTips\Powershell\ChangingSettings\changing_settings_no_error_handling.ps1
   }
catch{
   Throw
}

error_handling_out

This time the error is correctly reported at the job level ("The job failed.") and at the job step level. The Process Exit Code reported at the step level is -1.

job history err handling out

Notice the Throw statement from the catch block. If it is used alone in a catch block, the Throw statement will re-throw the exception caught and the "outer script" stops because of the $ErrorActionPreference value. In fact, it is enough to stop the PowerShell step if an error occurs. You can simply set the $ErrorActionPreference to "Stop". You don't need the try-catch block in the SQL Server Agent Job step command. The SQL Server Agent Job step command can be as simple as below and the error will be reported just as you see in the picture above. 

$ErrorActionPreference = "Stop"
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe  C:\Notes\MSSQLTips\Powershell\ChangingSettings\changing_settings_no_error_handling.ps1
Next Steps

Originally I ran the code snippets on SQL Server 2012 SP2 and PowerShell v4, but they will also work on SQL 2014 SP1 and PowerShell v5.



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Diana Moldovan Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article

















get free sql tips
agree to terms