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.

more ▼

asked Apr 05, 2017 at 09:48 PM in Default

avatar image

50 2 2 5

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

Apr 06, 2017 at 01:21 PM JohnM

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.

Apr 06, 2017 at 03:21 PM Robp

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.

Apr 06, 2017 at 06:24 PM Shawn_Melton

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.

Apr 06, 2017 at 10:25 PM Robp
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 05, 2017 at 09:48 PM

Seen: 76 times

Last Updated: Apr 06, 2017 at 10:25 PM

Copyright 2018 Redgate Software. Privacy Policy