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

• Evgeny Garaev

  SSCertifiable

  Points: 7428

  More actions

  January 15, 2018 at 7:51 pm

  #1975947

  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: 1004619

  More actions

  January 15, 2018 at 10:22 pm

  #1975952

  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

  January 16, 2018 at 10:12 am

  #1976008

  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