Powershell V5 SQL Agent job receives: Read-SQLTableData is not recognized

Welcome Forums General PowerShell Q&A Powershell V5 SQL Agent job receives: Read-SQLTableData is not recognized

Viewing 3 reply threads
  • Author
    Posts
    • #180006
      Participant
      Topics: 1
      Replies: 2
      Points: 24
      Rank: Member

      I get the following error when running Powershell in a SQL Agent job:

      The error information returned by PowerShell is: 'The term 'Read-SQLTableData' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. '. Process Exit Code -1.

      The step works when running in Powershell ISE itself and I have recently upgraded to Powershell V5.

      When I run "$psversiontable" in the SQL Agent jobstep it returns:

      PSVersion 5.1.14409.1018

      "Get-Command -Name SQLTableData" retruns:

      `  Read-SqlTableData               Write-SQlTableData                     `
      So the SQL Agent appears to have the correct version and the desired commands appear to be there, but I still get an error when running with either Read-SQLTableData or Write-SQLTableData thru a SQL Agent job.  I also updated dbo.syssubsystems but all the Poweshell executables appear to be V1, does this matter?
    • #180411
      Moderator
      Topics: 3
      Replies: 172
      Points: 906
      Helping Hand
      Rank: Major Contributor

      You need to import the module explicitly inside the SQL agent jobs...

      use Import-Module SqlServer

      • #180522
        Participant
        Topics: 1
        Replies: 2
        Points: 24
        Rank: Member

        Hi Kiran, yes that module is installed but the directory Powershell is looking at when doing a Get-module -listavailable is "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS". I attempt to change this by updating msdb.dbo.syssubsystems to have "C:\Program Files\WindowsPowerShell\Modules\SQLServer" for the PowerShell proxy, but when I restart the SQL Agent it goes back to the original directory path, so I know I need to update this but haven't found how....

    • #180546
      Participant
      Topics: 1
      Replies: 2
      Points: 24
      Rank: Member

      Also, I am running this in SQL Server 2014, from what I read it doesn't look like the SQL Agent can recognize the SQLSERVER module, it only looks for the SQLPS module. If anyone knows how to get around this, please let me know.

    • #180914
      Moderator
      Topics: 3
      Replies: 172
      Points: 906
      Helping Hand
      Rank: Major Contributor
      @MatthewJ91 – Can you please share your code by trimming all the sensitive info if any? Thank you.
Viewing 3 reply threads
  • The topic ‘Powershell V5 SQL Agent job receives: Read-SQLTableData is not recognized’ is closed to new replies.