This topic contains 4 replies, has 2 voices, and was last updated by
September 27, 2019 at 4:14 am #180006
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:
"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?
September 30, 2019 at 6:44 am #180411ParticipantTopics: 1Replies: 96Points: 514Rank: Major Contributor
You need to import the module explicitly inside the SQL agent jobs...
September 30, 2019 at 5:41 pm #180522
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....
September 30, 2019 at 8:15 pm #180546
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.
October 3, 2019 at 8:31 am #180914
You must be logged in to reply to this topic.