Invoke-Command in sql agent job step

Welcome Forums General PowerShell Q&A Invoke-Command in sql agent job step

Viewing 3 reply threads
  • Author
    Posts
    • #270337
      Participant
      Topics: 5
      Replies: 3
      Points: 49
      Rank: Member

      Hi ,

      I was trying to collect the SSL  certificate  details in remote servers and writing in sql server table.

      when I was trying to execute the command

      ———

      $details =(Invoke-Command -ComputerName ‘servername’ -ScriptBlock {

      Get-ChildItem Cert:\LocalMachine\My }  –( No error when I executed in powershell console.)

      $details [email protected]($detailsarr | select-object -Property Issuer,Subject,NotBefore,NotAfter,PSComputerName -ExcludeProperty RunspaceId )

      ————-

      inside the sql agent job  step I am getting the error.

       

      FYI – I executed with same user as I executed in powershell console

      “Executed as user: NT Service\SQLSERVERAGENT. The job script encountered the following errors. These errors did not stop the script: Unhandled Exception: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list. at System.Text.StringBuilder.AppendFormatHelper(IFormatProvider provider, String format, ParamsArray args) at System.String.FormatHelper(IFormatProvider provider, String format, ParamsArray args) at System.String.Format(IFormatProvider provider, String format, Object[] args) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.GetErrorRecordMessage(ErrorRecord errorRecord) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.HandleAgentJob(RunspaceConfiguration config) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.Main(String[] args). Process Exit Code 255. The step failed. ”
      Thanks in advance!

      Mohan

    • #270508
      Participant
      Topics: 16
      Replies: 1855
      Points: 3,568
      Helping Hand
      Rank: Community Hero

      $detailsarr is not defined. The variable for the results is set to $details and then you overwrite $details with $detailsarr, which from what you posted would be null.

      Also, if you you are specifying the properties, there is no need to exclude a specific property. That would only be used if you wanted all properties with the exception of the exclude properties, but you are specifying the properties that are returned with Property.

    • #270634
      Participant
      Topics: 5
      Replies: 3
      Points: 49
      Rank: Member

      Hi Simmer

      Thanks for your reply.

      Let me short my script

      (Invoke-Command -ComputerName ‘servername’ -ScriptBlock {Get-ChildItem Cert:\LocalMachine\My })

       

      I ran only the above line in the MSSQL  sever agent job in step1 , it was failed and got the same error .

      The same above line is running fine in power shell console

      note: servername is remote server and not local server name.

      Error:

      Executed as user: NT Service\SQLSERVERAGENT. The job script encountered the following errors. These errors did not stop the script: Unhandled Exception: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list. at System.Text.StringBuilder.AppendFormatHelper(IFormatProvider provider, String format, ParamsArray args) at System.String.FormatHelper(IFormatProvider provider, String format, ParamsArray args) at System.String.Format(IFormatProvider provider, String format, Object[] args) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.GetErrorRecordMessage(ErrorRecord errorRecord) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.HandleAgentJob(RunspaceConfiguration config) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.Main(String[] args). Process Exit Code -532462766. The step failed.

       

    • #270718
      Participant
      Topics: 16
      Replies: 1855
      Points: 3,568
      Helping Hand
      Rank: Community Hero

      The command is being executed in the context of ‘NT Service\SQLSERVERAGENT’, which most likely does not have access to the remote computer. You need to specify credentials to Invoke-Command.

Viewing 3 reply threads
  • You must be logged in to reply to this topic.