SQL Agent job error while running powershell

Question

question

Sagar Bhargava asked Nov 27, '18

SQL Agent job error while running powershell

Hi,

I have a SQL Agent job that runs a powershell script. It was running fine until I applied the SQL Server 2016 SP2 CU2 on top of SQL Server 2016 SP1. The error I am getting is :

New-WebServiceProxy : The term 'New-WebServiceProxy' 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.

I can run the script from powershell or by launching SQLPS but this results in error whenever I run it through SQL Agent job. I am not sure what changed. The job step has been configured to run as type "Operating System CmdExec" and has been running run good since a long time.

Command that I am running is:

powershell.exe -Noninteractive -executionpolicy bypass "powershell -c D:\temp\RunQuery.ps1"

Name Value ---- -----

PSVersion 5.0.10586.117

PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}

BuildVersion 10.0.10586.117

CLRVersion 4.0.30319.42000

WSManStackVersion 3.0

PSRemotingProtocolVersion 2.3

SerializationVersion 1.1.0.1

Has anyone had or seen this issue before?

Thanks.

error powershell sql agent

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ commented · Nov 28, 2018 at 12:36 AM

If you run those commands with xp_cmdshell will you get the error? just wondering if this is limited to the agent or to any invocation by the sql instance

0 ·

Sagar Bhargava commented · Nov 29, 2018 at 05:53 AM

@kenJ: Thank for your reply. Unfortunately, xp_cmdshell is disabled across the production environment. However on a test server where it is enabled, the job is running fine. Only difference is that the test box has Powershell 4. Is there anything else we can check?

0 ·

KenJ commented · Nov 29, 2018 at 12:55 PM

You might upgrade the test server to PowerShell 5 to see if you can reproduce the error. Is anything else different between production and test?

It might be worth opening a support case with MS since this started when you applied a sql patch.

0 ·

Sagar Bhargava KenJ commented · Dec 04, 2018 at 04:41 AM

Thanks @KenJ. I had a further look and found that the test server does not have the 'WMF 5.0' which the production server got recently as part of monthly patching with SQL Server updates. Given it has a lot of Powershell updates, will followup with Microsoft on this.

0 ·

KenJ Sagar Bhargava commented · Dec 04, 2018 at 11:48 AM

Sounds like a potential culprit.

Going forward, would it make sense in your environment to apply these patches/updates to the test server before production to help catch this sort of thing earlier?

0 ·

question