The Multifunctioning DBA

Aug 28 2009   7:12PM GMT

Powershell execute SQL Server Jobs

Colin Smith Colin Smith Profile: Colin Smith

I have been asked to write a script that will allow users to connect to a server, get a list of only the jobs that I want them to see, and execute only those jobs. No options to do anything other than execute the job. This is what I have come up with.

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | out-null
$s = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) “Servername\instnace”
$jobserver = $s.JobServer
$jobs = $jobserver.Jobs | where {$_.Name -like “*jobname*”}
$totaljobs = $jobs.Count
$i = 1
echo “`n”
foreach ($job in $jobs)
{
echo “$i – $job.name”
$i ++
}
$exit = $i
Echo “$i – Exit”
echo “`n”
$exjob = Read-Host “What job would you like to execute`n”
if($exjob -ne $exit)
{
echo “Running Job”
$run = $exjob – 1
$jobs[$run].Invoke()
}
Echo “Exiting”

This will use integrated authentication to connect and enumerate jobs with the name criteria that you define. You will then get a numbered menu of the jobs and a prompt asking what job to run. Select the number of the job and it will execute on the server.

Please let me know if you have any questions or comments.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: