Calling a SQL Server Agent job

  • Tomys

    Mr or Mrs. 500

    Points: 533

    Hi, I was looking for a way or possibility to call a SQL Server agent job using a powershell script. And wanted this powershell script to be in a windows task scheduler job.

    I am pretty new to PowerShell, and so far I was able to get a script as below: (but does not call the SQL Agent job) The command just comes out.

    ======

    param(

    [string]$DB4,

    [string]$test

    )

    Write-Verbose "Starting SQL Agent Job $($JobName) on Server $($ServerName)"

    $date = Get-Date

    Write-Verbose "It is now: $($date)"

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

    $srv = New-Object Microsoft.SqlServer.Management.SMO.Server("$ServerName")

    $job = $srv.jobserver.jobs["$JobName"]

    if ($job)

    {

    if($StepName -ne '')

    {

    $job.Start($StepName)

    }

    else

    {

    $job.Start()

    }

    Write-Verbose "Job $($JobName) on Server $($ServerName) started"

    $i = 0

    }

  • Sue_H

    SSC Guru

    Points: 90700

    That looks like the code for a function to start a job in Powershell so I'm trying to guess at what it was originally and figure out what you are trying to do :). You can start by wrapping the code in a function by just doing something like:

    Function Start-SQLJob

    {

    Then insert all the code you have here after the curly braces. Then end the function with curly braces.

    I would guess you renamed the parameters in the code you found:

    param(

    [string]$DB4,

    [string]$test

    )

    And those are the parameters for the function. You want those back to how they originally were - it's like declare in t-sql. From the code, they originally were

    param(

    [string]$ServerName,

    [string]$JobName)

    So put those back how they were originally. Then if you are trying to execute the function against the server DB4 to run the job named Test, you would call the function like:

    Start-SQLJob "DB4" "Test"

    That should work, I just did the same and it worked. It looks like they had another parameter in there for the job step but it should still just run without adding that in. The job steps I leave alone for now - it's something you can play with in trying to work with this function.

    And another approach that is way easier would be to use dbatools. It's a module that is pretty popular and has a lot of different SQL Server cmdlets you can use. And it has Start-DbaAgentJob. I get it that your just getting started and it's good to work on getting this working just for the learning experience. But dbatools is a great resource you may want to look into and play with as well. Here is the link:

    DBATools

    Sue

     

  • Jeff Moden

    SSC Guru

    Points: 996830

    I'm thinking that it should be the other way around... an SQL Server Agent job should be calling the PowerShell.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

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