In one of my articles, Calling Procs from PowerShell with Parameters, there was a question in the discussion about calling PowerShell (PoSh) from SQL Agent Jobs. I haven't done much of that, as most of my PowerShell is interactive scripts to make work easier when managing lots of servers. However, it was an interesting challenge, so I decided to investigate and try to determine the best way to do this.

This articles walks through a bit about the PoSh environment within SQL Agent and how you you can more easily run PowerShell code, with parameters.

Where Am I?

The first thing I wanted to do when thinking about SQL Agent and PoSh is to determine the environment setup. If you create a job, and then add a step, you have a few options as to the type of job. In my case, I want to start with PowerShell here.

However, this is potentially a problem as there are limited rights here. So, I want to determine the version of PoSh and the modules that are available. I'll configure the job step with this code:

$PSVersionTable  |Out-File 'c:\sqlagent\psver.txt'
Get-InstalledModule | Out-File 'c:\sqlagent\modules.txt'

I created the c:\SQLAgent folder, and then executed the job.

Once this completes, I see some results.

I have Posh v5.x and I also have the SqlServer and dbatools modules under this profile. That's good, as it gives me a place to start from. Let's now try something that's a little more complex.

Executing a Script

Script execution is limited by default when you start PoSh on a machine. I have changed things for the machine for my account, but does that work for SQL Agent? I can check this by adding a step to my job. In this case, I'll run:

Get-ExecutionPolicy | Out-File 'c:\SQLAgent\policy.txt'

This returns "RemoteSigned" in the text file, so I have an idea of what I can do. Let's now create a script. I'll go with a script I had in my previous article, which looks like this:

The best way to call a script in PowerShell is to explicitly called the PowerShell executable. While SQL Agent has a Powershell step type, the exact version of PowerShell, the profile, and more could change. If I call powershell.exe, I know which profile is loaded, and I can then count on what will happen when the script runs.

More about PowerShell profiles is published on SimpleTalk. If you need to change or set a profile, read that article.

Adding Parameters

There are a few ways that I can manage parameters for my script. Certainly I can just put parameters into the SQL Agent Job Step, but this is like hard coding something, and is an area of hidden maintenance. Below, I have two ways to manage parameters.

Use SQL Server

I could store parameters in SQL Server, and query the server for the values and then use them in my script. For example, in my previous article, I passed parameters into SQL Server. I could instead, use a similar script to connect to SQL Server and then retrieve values. The result set from this first call can return values I use further down in the script. Here's an example of how I might do this.

At the top of my script, I'll connect to SQL Server. In this case, I need a table inside the server where I store the values. For this script, I can query a table, agentparameters, that has a job name, a variable, and a value. Here's some code to set this up:

CREATE DATABASE AdminDB
GO
USE AdminDB
GO
CREATE TABLE agentparameters (ValueID INT IDENTITY(1,1)
, AgentJob VARCHAR(100)
, VarName VARCHAR(20)
, VarValue VARCHAR(100)
)
GO
INSERT dbo.agentparameters
    (AgentJob, VarName, VarValue)
VALUES
    ('getproductdetails', 'productid', '729'),
('getproductdetails', 'checkdate', '2010-05-26')
SELECT a.VarName, a.VarValue FROM dbo.agentparameters AS a WHERE a.AgentJob = 'getproductdetails'

Now I can modify my script from the previous article, adding a couple queries to the top. I will reuse the $Server variable below, and I'll also reset the $Database value to the AdventureWorks in the script.

$Server = "localhost"
$Database = "AdminDB"
$productid = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query "SELECT a.VarName, a.VarValue FROM dbo.agentparameters AS a WHERE a.AgentJob = 'getproductdetails' and varname='productid'"
$checkdate = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query "SELECT a.VarName, a.VarValue FROM dbo.agentparameters AS a WHERE a.AgentJob = 'getproductdetails' and varname='checkdate'"

This returns a result set, but I can modify my parameter code to look like this:

$p1.Value = $productid.VarValue

I do this for the second parameter as well, getting the date. The rest of the script is the same, and it works as it did previously.

Using a Text File

The second way is something a fellow DBA suggested. This individual said we should use a text file to store parameters on our server. We can easily edit and change these, the values aren't in any version control or scripting, but they are easily managed by administrators. I found an answer on Stack Overflow that helped.

For example, on my local machine, I put a text file with this data:

productid=729
checkdate="2010-05-26"

I then add this code to my script at the top.

$file = 'E:\Documents\poshvars.txt'
Get-Content $file | ForEach-Object {
    $var = $_.Split('=')
    New-Variable -Name $var[0] -Value $var[1]
}

This script has the location of my parameters in the $file variable. The rest of the code splits this file up into lines and then separates the values before and after the equals sign. I use this to create new variables. Of course, I need to know the variable names, as I'll use them later in the script.

From here, this is the same script is in my previous article.

Summary

This article looked at how to run PowerShell from SQL Agent and understand the environment in which your script will run. I use the powershell.exe command to run my script, though there is a Powershell command type. I showed how to determine the nature of your environment, and how to add parameters.

There are multiple ways that you can deal with PowerShell scripts as a part of your regular maintenance, and hopefully I've given you some ideas in this article.