Increase PowerShell available commands in a SQL Server Agent Job step


By:   |   Updated: 2018-08-15   |   Comments   |   Related: More > SQL Server Agent


Problem

It is often required to perform advanced operations within a SQL Server Agent Job step, like interact with the operating system or import custom modules. For this, you’re able to create a PowerShell type step, but the problem is that the Powershell version varies when it runs inside a job step, so many people recommend (link link link) creating a .ps1 file and specify an “Operating system (CmdExec)” type step.

But what if you need to implement this job as a Multi-Server job? It will be a nightmare when you need to update the script in all your servers, keeping up to date the existing servers and copy this script to the new ones, not to mention that the path may not always be the same. So, what other option do you have?

Solution

Here’s the thing: when you run a “PowerShell” type step, it runs as SQLPS.exe which is a sub-set of PowerShell, so not all commands are available. Here is a comparison of the first commands available in each of them:

SQLPS.exe PowerShell.exe
  Add-Computer
Add-Content Add-Content
Add-History Add-History
Add-Member Add-Member
  Add-PSSnapin
  Add-Type
  Checkpoint-Computer
Clear-Content Clear-Content
  Clear-EventLog
  Clear-History
Clear-Item Clear-Item
Clear-ItemProperty Clear-ItemProperty
Clear-Variable Clear-Variable
Compare-Object Compare-Object
  Complete-Transaction
  Connect-WSMan
  ConvertFrom-Csv
ConvertFrom-SecureString ConvertFrom-SecureString
  ConvertFrom-StringData
Convert-Path Convert-Path
  ConvertTo-Csv

As you can see, there are a lot few commands in SQLPS.exe than when it runs from PowerShell.exe.

Let’s start by looking at a command to get the perf counters of the local machine, and what is the output in the different PowerShell options.

From PowerShell.exe:

PS C:\Users\PE> $result = Get-Counter | select -ExpandProperty CounterSamples
PS C:\Users\PE> $result[0]
Path                              InstanceName       CookedValue
----                              ------------       -----------
\\server01\network interface(...  core               122376.167085421
PS C:\Users\PE>

From SQLPS.exe:

PS C:\Users\PE> $result = Get-Counter | select -ExpandProperty CounterSamples
The term 'Get-Counter' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:22
+ $result = Get-Counter <<<<  | select -ExpandProperty CounterSamples
    + CategoryInfo          : ObjectNotFound: (Get-Counter:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
PS C:\Users\PE>

From a PowerShell type step:

A job step received an error at line 1 in a PowerShell script. The corresponding line is '$result = Get-Counter | select -ExpandProperty CounterSamples'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Get-Counter' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.  '.  Process Exit Code -1.  The step failed.

But this doesn’t mean we can’t use all the available PowerShell commands and functions, because you can invoke PowerShell.exe passing the command to execute, just like you would do from a CmdExec type step. If we modify the script, we’re able to get the results.

Script:

$result = powershell.exe -command "Get-Counter | select -ExpandProperty CounterSamples"
$result[1]
$result[2]
$result[3]

Output:

Path                               InstanceName       CookedValue
----                               ------------       -----------
\\server01\network interface(...   core               109935.744974759

As you may have already noticed, the result is an array of strings with the output from PowerShell.exe, so if you need the object with its properties and methods, you must think about serialize/deserialize.

However, if you can deal with this string output (as in my case), here are a few things you must consider:

Problem Solution
Need to run multiple commands in a single line Separate the commands with semicolon [ ; ]
Need to run multiple commands in multiple lines Declare a here-string as follows:

$var = @”

“@


Note that if you declare a variable, you need to escape the $ [ `$var ] And if you need to use double quotes, you need to escape the “ [ \” ] Note that there are different escape sequences, and some may not work in every case, so it’s trial and error, but here are some links to help you with that: link link link.
Need to pass a variable to the command being executed You just leave the variable without escaping the $:

Get-Counter -Counter @($availCounters)

Where $availCounters in my case is a string variable, which holds valid perf counters in the form:

‘\\server01\counter1’, ‘\\server01\counter2’, etc.
Output strings get trimmed Append the following commands at the end of your script: link

 | Format-Table -Property * -AutoSize | Out-String -Width 100

Replace 100 with a size big enough to hold all your content.
Output strings split at character 80 This is a limit from cmd.exe, you need to add the following command at the beginning of your script: link

`$Host.UI.RawUI.BufferSize = New-Object
Management.Automation.Host.Size(500,25);


Replace 500 with a size big enough to hold all your content.
Need to convert the results back to a table There may be several ways to accomplish this, but the one I used was to include a new property in the result set:

 | select Path, '|', CookedValue

Then I searched for the position of it:

$pos = $results[1].IndexOf("|")

And then split all lines at that position (leaving out the header and the line below the header):

$results2 = $results | where {$_.StartsWith("\\")} | select @{l="Path";e={$_.Substring(0, $pos).Trim()}}, @{l="CookedValue";e={$_.Substring($pos, $_.length - $pos).Trim()}}

Note that if you have several columns, you may need to automate this task in another way.
Need to add these results to an existing result set The easiest way I found was to declare my result set as a list, which is valid from SQLPS.exe: link

$list = New-Object System.Collections.Generic.List[System.Object]

Then I added the new results to that list:

 foreach ($r in ($results2 | select Path, CookedValue)) { $list.add($r) }

The final script, the one that runs inside a PowerShell type step, contains the following lines:

$list = New-Object System.Collections.Generic.List[System.Object]
$availCounters = powershell.exe -command "Get-Counter -ListSet * | select -expand Counter"
$availCounters = $availCounters -join ","
$results = powershell.exe -command "`$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size(500,25); Get-Counter -Counter @($availCounters) | select -ExpandProperty CounterSamples | select Path, '|', CookedValue | Format-Table -Property * -AutoSize | Out-String -Width 100"
$pos = $results[1].IndexOf("|")
$results2 = $results | where {$_.StartsWith("\\")} | select @{l="Path";e={$_.Substring(0, $pos).Trim()}}, @{l="CookedValue";e={$_.Substring($pos, $_.length - $pos).Trim()}}
foreach ($r in ($results2 | select Path, CookedValue)) { $list.add($r) }

And what is the benefit of this way vs. CmdExec? As I mentioned earlier, if you need to create a multi-server job, you don’t have to create a .ps1 file in each of your servers, avoiding the cumbersome of finding a common path and dealing with updating the file when you make a change to the script.

You could also have a stored procedure that can be easily updated in multiple servers at once by running a multi-server query, but you would have to manage all the programming (string, split, tables, etc.) through T-SQL, which will require a huge amount of work and at the end the code will be illegible.

Next Steps
  • Think of what other command you would like to use and you couldn’t because it was not available in a PowerShell type job step.
  • Here is a list of powershell commands available in SQL Server 2016 link
  • In the next tip we’re going to get the top priority perf counters with their thresholds, to have an automated email alert system when there are issues.


Last Updated: 2018-08-15


get scripts

next tip button



About the author
MSSQLTips author Pablo Echeverria I've worked for more than 10 years as a software programmer and analyst. In 2016, I switched jobs to a DBA position, where I have implement new processes, created better monitoring tools and grown my data scientist skills.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Running a SSIS Package from SQL Server Agent Using a Proxy Account

Querying SQL Server Agent Job Information

Querying SQL Server Agent Job History Data

SQL Server Agent Job Schedule Reporting

Query SQL Server Agent Jobs, Job Steps, History and Schedule System Tables








get free sql tips
agree to terms


Learn more about SQL Server tools