Starting a SQL Agent job with Powershell and Windows Scheduled Tasks

In the past, I’ve needed to start SQL Agent jobs using Windows Scheduled Tasks. Using Powershell, this is now easier than ever.

First, let’s create the Powershell script. It’s very basic and relies on Windows Authentication and SQL Native Client to connect to the SQL Server.

#Vars for Server and JobName
$Server = "<Server>"
$JobName = "<JobName>"

#Create/Open Connection
$sqlConn = new-object System.Data.SqlClient.sqlConnection "server=$Server;database=msdb;Integrated Security=sspi"
$sqlConn.Open()

#Create Command Obj
$sqlCommand = $sqlConn.CreateCommand()
$sqlCommand.CommandText = "EXEC dbo.sp_start_job N'$JobName'"

#Exec Command
$sqlCommand.ExecuteReader()

#Close Conneection
$sqlConn.Close()


In a nutshell, this script will need you to set 2 variables, $Server and $JobName. Once those have been set, the script will open up a connection to the server, execute the sp_start_job command and then close the connection.

Next, we want to start the Powershell script via Windows Scheduled Tasks. When setting up the task, choose "Start a program" under Action and type powershell.exe . We specify the Powershell script to execute in the additional arguments. By adding &'\\<filepath>\StartTest_SQL_AgentJob.ps1' Powershell will start up and execute the script.

Note: If your <filepath> has spaces in it, you will need the "&", otherwise it can be omitted.