Start a SQL Server Agent Job using the SQL Server PowerShell Provider

Start a SQL Server Agent Job using the SQL Server PowerShell Provider

Rate This
  • Comments 2

Whew!  That’s a mouthfull. Anyway, I thought I would share part of a script I wrote today to help automate (more) from PowerShell. This assumes a few things: that you’re doing this from the SQL Server 2008 PowerShell provider (not just good old regular PowerShell) and that you change the HAL9000 and SQL2K8 parts with your server name and your Instance name. Oh, and the Jobs I start are called “Test” and then something else. That’s the Test* part. You can use a specific name and drop the * or use your own naming convention.

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

 

# Start a Job named Test in the SQL Server 2008 PowrShell Provider

DIR SQLSERVER:\SQL\HAL9000\SQL2K8\JobServer\Jobs\Test* | % {$_.Start()}

Leave a Comment
  • Please add 7 and 6 and type the answer here:
  • Post
  • Nice! Using provider is much shorter than the regular Powershell method:

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

    $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") "HAL9000\SQL2K8"

    $jobsrv = $server.JobServer

    $jobsrv.Jobs | where {$_.name -like "Test*"} | foreach {$_.Start()}

  • This is a little more complex, but it will start the job and return the result...

    $strSrvInst = "Server\Instance";

    $strJob = "My_Job";

    $iTmeOut = 600;

    $strSQL = "

    declare

      @nvcJob nvarchar(4000),

      @iStrtDte int,

      @iStrtTme int;

    select

      @nvcJob = '$strJob',

      @iStrtDte = cast(right(replicate('0', 4) + cast(datepart(yyyy, getdate()) as char(4)), 4)

      + right(replicate('0', 2) + cast(datepart(mm, getdate()) as nvarchar(2)), 2)

      + right(replicate('0', 2) + cast(datepart(dd, getdate()) as nvarchar(2)), 2) as integer),

      @iStrtTme = cast(right(replicate('0', 2) + cast(datepart(hh, getdate()) as nvarchar(2)), 2)

      + right(replicate('0', 2) + cast(datepart(mi, getdate()) as nvarchar(2)), 2)

      + right(replicate('0', 2) + cast(datepart(ss, getdate()) as nvarchar(2)), 2) as integer);

    execute msdb.dbo.sp_start_job @nvcJob;

    while not exists (

      select sjh.run_status

      from  msdb.dbo.sysjobhistory sjh, msdb.dbo.sysjobs sj

      where sjh.job_id =  sj.job_id

      and name = @nvcJob

      and run_date >= @iStrtDte

      and run_time >= @iStrtTme)

    begin

      waitfor delay '00:00:02';

    end;

    select

      case run_status

      when 0 then 'FAILED'

      when 1 then 'SUCCEEDED'

      when 2 then 'RETRY'

      when 3 then 'CANCELLED'

      end as Job_Result

      from  msdb.dbo.sysjobhistory sjh, msdb.dbo.sysjobs sj

      where sjh.job_id =  sj.job_id

      and name = @nvcJob

      and step_name = '(Job outcome)'

      and run_date >= @iStrtDte

      and run_time >= @iStrtTme;";

    Invoke-Sqlcmd -ServerInstance $strSrvInst -Query $strSQL -QueryTimeout $iTmeOut;

Page 1 of 1 (2 items)