question

Robp avatar image
Robp asked

Use PowerShell in SQL Server Agent job.

Im trying to create a job in SSMS 2012 that has three steps. The first step stops a service, second step runs some sql, and the third step starts a service again. In steps one I set the Type to PowerShell, Run as: SQL Server Agent Service Account, and the Command was Stop-Service X. Step three was the same except it was Start-Service X. When I ran the job it ran succesfully, however when I was watching the Task Manager service at the same time the job was running, the status didn't change at all. Am I missing additional PowerShell code to start and stop the service and/or is there is an issue between the communication with SSMS and PowerShell or even something else? This is all done on the same machine, no remoting. The rights are all administrative rights.
ssmspowershellagent
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Have you run just the one step to stop the service and confirm that the service was actually stopped?
0 Likes 0 ·
Yes, I've done that as well. Both for stopping and starting. Starting when the process was in stopped status and stopping when the process was in Running status.
0 Likes 0 ·
On your server open "sqlps.exe" (should be able to from run prompt). Run your `Stop-Service x` command from that window and see the results. This is the same context as what the SQL Agent job will execute the PS step in.
0 Likes 0 ·
When I start and stop the process using in sqlps.exe it works just fine. Doing it in the job still doesn't change the status. I'm guessing it has something to do with Administrative permissions, but I'm not sure how to set those in the job step.
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.