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

This topic contains 4 replies, has 2 voices, and was last updated by

 
Participant
2 weeks, 3 days ago.

  • 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

    Participant
    Topics: 1
    Replies: 96
    Points: 514
    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

    Participant
    Topics: 1
    Replies: 96
    Points: 514
    Helping Hand
    Rank: Major Contributor
    @MatthewJ91 – Can you please share your code by trimming all the sensitive info if any? Thank you.

You must be logged in to reply to this topic.