Wednesday, January 4, 2012

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.

7 comments:

  1. How would you do this if you wanted to use SQL Server Authentication?

    ReplyDelete
    Replies
    1. It's all in the connection string of the $sqlconn object. Remove the "Integrated Security=sspi" and replace with "UID=XXX;PWD=XXX"

      Delete
  2. Thanks for sharing such a valuable information.This post is very useful for me.SQL Jobs

    ReplyDelete
  3. how to run the above job with a job step?

    ReplyDelete
  4. The writer has truly done equity with his perusers and his work.
    resumeyard.com

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. I think there is one little problem here. Not sure. I went with "Invoke-Sqlcmd -ServerInstance $SQLServer -Database $Database -Query $ExecAgent" where $ExecAgent is "exec dbo.sp_start_job N'...". And as most people know, scheduling is generally a predecessor, successor thing that is most useful synchronously. From what I can tell so far this is asynchronous either way, ie your calling scheduler is NOT made to wait for the command to finish. I'll post back here what I find.

    ReplyDelete