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.

10 |1200

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

JohnM commented · Apr 06, 2017 at 01:21 PM

Have you run just the one step to stop the service and confirm that the service was actually stopped?

0 ·

Robp commented · Apr 06, 2017 at 03:21 PM

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 ·

Shawn_Melton commented · Apr 06, 2017 at 06:24 PM

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 ·

Robp commented · Apr 06, 2017 at 10:25 PM

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 ·

question