Calling a SQL Server Agent job

Question

Calling a SQL Server Agent job

Tomys

Mr or Mrs. 500

Points: 567
More actions
February 7, 2020 at 9:26 pm

#3724067

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
}

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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: 1004339 More actions · Answer 2

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.

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