Using PowerShell to start SQL JOb

  • Folks,

    Can someone explain or point me to information on how to start a SQL Job from a remote machine using a PowerShell script? I need to know what needs to be installed on the remote machine, and needs to be installed on the SQL Server (if anything), any permission issues, and example scripts. Also, I would need to be able to do this on a SQL 2005 and 2008 server. I did find the following example, but it appears this is only for 2008.

    http://max-pit.spaces.live.com/blog/cns!A034D6A0DDC4E64E!863.entry

    Thank you in advance for taking your time to help me out.

  • take a look at using the SQLCMD utility and stored procedure sp_start_job, something like (may not be exactly right):

    SQLCMD -S servername -Q "exec sp_start_job 'sqlagent job name'"

    Jim

  • Thanks Jim for the reply. Using SQLCMD was my original thought too, but our client is requesting this be done using PowerShell. I guess another question is that when PowerShell is used, is it running under the hood opening a SQL connection and not using WMI?

  • Maybe something like this? (I've "borrowed" most of this and used it but don't make me explain it!)

    $SB = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=$servername"

    $SQLcon = New-object system.data.sqlclient.SqlConnection

    $SQLcon.ConnectionString = $SB

    $SelectCMD = New-object system.data.sqlclient.SqlCommand

    $SelectCMD.CommandTimeout = 30

    $SelectCMD.Connection = $SQLCon

    $SelectCMD.CommandText = "exec sp_start_job 'sqlagent job name'"

    $da = new-object System.Data.SqlClient.SQLDataAdapter($SelectCMD)

    $ds = new-object System.Data.dataset

    $da.fill($ds)

  • Thanks Jim. So to answer my questions, you need to have PowerShell and SMO installed on the remote computer. The identity that script is being executed under has to have rights on the SQL Server to start a job. And this is the script I ended up writing.

    param (

    [string] $instancename,

    [string] $db,

    [string] $jobname

    )

    $sqlConnection = new-object System.Data.SqlClient.SqlConnection

    $sqlConnection.ConnectionString = 'server=' + $instancename + ';integrated security=TRUE;database=' + $db

    $sqlConnection.Open()

    $sqlCommand = new-object System.Data.SqlClient.SqlCommand

    $sqlCommand.CommandTimeout = 120

    $sqlCommand.Connection = $sqlConnection

    $sqlCommand.CommandText= "exec dbo.sp_start_job " + $jobname

    Write-Host "Executing Job => $jobname..."

    $result = $sqlCommand.ExecuteNonQuery()

    $sqlConnection.Close()

    Thanks again.

  • PowerShell yes, SMO no.

  • Sure makes sense. Everything being used in my example is standard .NET namespaces. Thanks again Jim.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply