SQL Agent job - Power shell script error

Question

SQL Agent job - Power shell script error

itsvenkyyy

SSC Veteran

Points: 213
More actions
January 15, 2018 at 4:35 pm

#402836

I have created a sql agent job to execute powershell script.
this is to stop SSAS service in another server.
Script :
Get-service MSSQLServerOLAPService -ComputerName vmpid-g4uwbi02 | stop-service
Start-Sleep -s 60
Get-service MSSQLServerOLAPService -ComputerName vmpid-g4uwbi02 | start-service
Job completes with no action : result as :
The job script encountered the following errors. Theseerrors did not stop the script: A job step received an error at line 1 ina PowerShell script. The corresponding line is 'Get-service MSSQLServerOLAPService-ComputerName vmpid-g4uwbi02 | stop-service '. Correct the script andreschedule the job. The error information returned by PowerShell is: 'Cannotfind any service with service name 'MSSQLServerOLAPService'
Kindly help

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

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

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 1

itsvenkyyy - Monday, January 15, 2018 4:35 PM
Script :
Get-service MSSQLServerOLAPService -ComputerName vmpid-g4uwbi02 | stop-service
Start-Sleep -s 60
Get-service MSSQLServerOLAPService -ComputerName vmpid-g4uwbi02 | start-service

I think that Start-Service and Stop-Service are case sensitive commands. Also you have to run a power shell as administrator in order to be able to execute this command.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

itsvenkyyy - Monday, January 15, 2018 4:35 PM
I have created a sql agent job to execute powershell script.
this is to stop SSAS service in another server.
Script :
Get-service MSSQLServerOLAPService -ComputerName vmpid-g4uwbi02 | stop-service
Start-Sleep -s 60
Get-service MSSQLServerOLAPService -ComputerName vmpid-g4uwbi02 | start-service
Job completes with no action : result as :
The job script encountered the following errors. Theseerrors did not stop the script: A job step received an error at line 1 ina PowerShell script. The corresponding line is 'Get-service MSSQLServerOLAPService-ComputerName vmpid-g4uwbi02 | stop-service '. Correct the script andreschedule the job. The error information returned by PowerShell is: 'Cannotfind any service with service name 'MSSQLServerOLAPService'
Kindly help

I'm curious... why are you stopping and restarting the SSAS service on that remote box, especially on a scheduled basis?

--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)

Sue_H SSC Guru Points: 90860 More actions · Answer 3

itsvenkyyy - Monday, January 15, 2018 4:35 PM
I have created a sql agent job to execute powershell script.
this is to stop SSAS service in another server.
Script :
Get-service MSSQLServerOLAPService -ComputerName vmpid-g4uwbi02 | stop-service
Start-Sleep -s 60
Get-service MSSQLServerOLAPService -ComputerName vmpid-g4uwbi02 | start-service
Job completes with no action : result as :
The job script encountered the following errors. Theseerrors did not stop the script: A job step received an error at line 1 ina PowerShell script. The corresponding line is 'Get-service MSSQLServerOLAPService-ComputerName vmpid-g4uwbi02 | stop-service '. Correct the script andreschedule the job. The error information returned by PowerShell is: 'Cannotfind any service with service name 'MSSQLServerOLAPService'
Kindly help

I pasted your script, put it in a Powershell step and it ran fine.
Does the Agent Service account on the server where you are running the job have permission to vmpid-g4uwbi02 and permissions restart services?
It might be worth checking the security log on vmpid-g4uwbi02 to see if there are any errors when you run the job.

Sue