Running Powershell Tasks With SQL Agent

Chrissy LeMaire shows how to run Powershell cmdlets from SQL Agent:

By default, only members of the sysadmin role are allowed to create jobs with the CmdExec Job Step, but adding non-sysadmins as principals to the CmdExec proxy works as well.

So here are the steps that I use to schedule my tasks:

  • Create a Windows-based Login in SQL Server

  • Ensure dbatools is available to the account

  • Create a SQL Server Credential

  • Create the Agent Proxy

  • Create the PowerShell .ps1 file

  • Create the Job and Job Step

Chrissy walks you through step by step, making the whole thing easy.

Related Posts

Automating Data Importation With dbatools

Bob Pusateri shows how to keep a dataset up to date using Powershell and dbatools: Now for the magic – let’s load some data! The dbatools command that does all the heavy lifting here is called Import-DbaCsvToSql. It loads CSV files into a SQL Server table quickly and easily. As an added bonus, the entire import […]

Read More

Command Shell In SQL Agent Only Runs One Command

Kenneth Fisher makes an important discovery about SQL Agent jobs running in command shell mode: Command shell steps in SQL Agent jobs only process the first command. (Yea, I know it’s the title of the post so this wasn’t much of a surprise. You’ll live.) In order to prove this, I created a job with […]

Read More

Categories

September 2017
MTWTFSS
« Aug Oct »
 123
45678910
11121314151617
18192021222324
252627282930