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>"
$sqlConn = new-object System.Data.SqlClient.sqlConnection "server=$Server;database=msdb;Integrated Security=sspi"
#Create Command Obj
$sqlCommand = $sqlConn.CreateCommand()
$sqlCommand.CommandText = "EXEC dbo.sp_start_job N'$JobName'"
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.