Post reply

Using PowerShell to start SQL JOb

  • February 18, 2010 at 8:24 am

    #216931

    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.

  • February 18, 2010 at 8:49 am

    #1120973

    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

  • February 18, 2010 at 9:18 am

    #1120995

    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?

  • February 18, 2010 at 9:31 am

    #1121017

    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)

  • February 18, 2010 at 11:01 am

    #1121148

    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.

  • February 18, 2010 at 11:26 am

    #1121184

    PowerShell yes, SMO no.

  • February 18, 2010 at 12:02 pm

    #1121234

    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