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

PowerShell Help
1

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?
2

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

use Import-Module SqlServer

3

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…

4

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.

5
@MatthewJ91 - Can you please share your code by trimming all the sensitive info if any? Thank you.