The use of Write-Host and SQL Server Agent PowerShell job steps

SQL Server Agent implements a job subsystem that allows users to directly run PowerShell scripts in SQL Server Agent. Internally this is implemented by reusing the SQLPS.EXE shell stub (which is another shape of POWERSHELL.EXE, but preconfigured for SQL Server).

When SQL Server Agent starts SQLPS.EXE, it does not do this through the console (there is no console attached).

The $Host variable will indicate what host is attached to your runtime. When running SQLPS.EXE on a console this will be your host:

PS C:\Users\mwories> $Host

Name : ConsoleHost
Version : 2.0
InstanceId : 40b6135d-0f89-4b12-b44e-d3d70a729343
UI : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture : en-US
CurrentUICulture : en-US
PrivateData : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace : System.Management.Automation.Runspaces.LocalRunspace

However print the host variable in an Agent Job and you will see this:

Name : Default Host
Version : 2.0
InstanceId : 9cf1e259-93b1-476f-8c3f-6fe9cf935aab
UI : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture : en-US
CurrentUICulture : en-US
PrivateData :
IsRunspacePushed :
Runspace :

The article How PowerShell Works explains well how the runspace interacts with the host.

The Host implement PSHostUserInterface and PSHostRawUserInterface that form the display interface. These may not implement certain methods for the Default Host. And indeed, for the Default Host (the host an Agent Job receives, as there is no console), the method that implements Write-Host is not available, and it will fail with the following message:

Executed as user: REDMOND\mwories. A job step received an error at line 3 in a PowerShell script. The corresponding line is 'write-host 'foobar''. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot invoke this function because the current host does not implement it.  '.  Process Exit Code -1.  The step failed.

Now what to do when you want to create your scripts to run in a console but also hosts that may not have a console, and may not implement any host output interface?

This will work.

if ($host.Name -eq "ConsoleHost")
{
 write-host 'foobar'
}

Of course you can find ways to simplify your scripts by wrapping this in a function that only writes output when a console is attached.

If there is any output that you like to be logged, it will not appear in the SQL Agent output. It is therefore better to use Write-Output if you want to provide some kind of output that shows up in SQL Agent output or history.

Hope this helps to understand the PowerShell subsystem in SQL Server Agent a bit better.