question

Sagar Bhargava avatar image
Sagar Bhargava asked

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.

errorpowershellsql agent
5 comments
10 |1200

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

KenJ avatar image KenJ commented ·

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 Likes 0 ·
Sagar Bhargava avatar image Sagar Bhargava commented ·

@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 Likes 0 ·
KenJ avatar image KenJ commented ·

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 Likes 0 ·
Sagar Bhargava avatar image Sagar Bhargava KenJ commented ·

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 Likes 0 ·
KenJ avatar image KenJ Sagar Bhargava commented ·

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 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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