Post a reply

Add an Attachment

If you do not want to add an Attachment to your Post, please leave the Fields blank.

(maximum 10 MB; please compress large files; only common media, archive, text and programming file formats are allowed)


Topic review


Re: issue resolved

Thanks for sharing your solution.

You need to specify an exact version of the framework in the COMPLUS_version, not v4.0.0.0.

I have tried running the code below as batch script
set COMPLUS_version=v4.0.0.0
powershell -File C:\Users\sysadmin.kai\Desktop\test.ps1

which gives me error

Version v4.0.30319 of the .NET Framework is not installed and it is required to
run version 3 of Windows PowerShell.

I downloaded .Net 4.5 and tried to install this version and I am getting the message below:

Microsoft .NET Framework 4 is already a part of this operating system. You do not need to install the .NET Framework 4 redistributable. <A HREF="">More information</A>.

I am at a loss what I need to do now...

This is the version
Major  Minor  Build  Revision

-----  -----  -----  --------
4      0      30319  42000

Re: Powershell script on SQL agent no longer working

What does this print, if you put it to your PowerShell script?

Also the set COMPLUS_version has to be set before you start PowerShell. Not in the PowerShell script itself - that's too late. You can for example wrap your PowerShell execution to a batch file:
set COMPLUS_version=v4.6.01055.394271
powershell -File script.ps1

Powershell script on SQL agent no longer working

I downloaded .Net framework update package and Powershell install but the error still occurs.
the current Power shell version is 5.1.14409.1005 - below the installed .Net frameworks.
PSChildName                      Version        Release

-----------                      -------        -------
v2.0.50727                       2.0.50727.4927       
v3.0                             3.0.30729.4926       
Windows Communication Foundation 3.0.4506.4926         
Windows Presentation Foundation  3.0.6920.4902         
v3.5                             3.5.30729.4926       
Client                           4.6.01055      394271
Full                             4.6.01055      394271

The code below executes from powershell and can be called from Microsoft scheduler, but when executed in SQL server Agent I get the error message. This has been working for years - this error started on Oct 3rd - on this day the security update below was installed, not sure if that caused the error by restricting access to the library.
Here is the code that I have been using - I inserted the set Complus line and referred to the newer .net build that is installed - but this does not make a difference.

set COMPLUS_version=v4.6.01055.394271
# Load WinSCP .NET assembly
Add-Type -Path "C:\Program Files (x86)\WinSCP\WinSCPnet.dll"
# Setup session options
$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
    Protocol = [WinSCP.Protocol]::sftp
    HostName = "deleted"
    UserName = "deleted"
    Password = "deleted"
    SshHostKeyFingerprint = "deleted"
  $session = New-Object WinSCP.Session
    # Connect
   # set filenames - if executed on a monday subtract three days
   if((get-date).dayofweek -match 'Monday')
   {$filedate = (Get-Date).adddays(-3)}     
   {$filedate = (Get-Date).adddays(-1)}
   $file1 = 'TORON_RH' + $filedate.ToString('yyyyMMdd') + '.csv'
   $file2 = 'TORON_RC' + $filedate.ToString('yyyyMMdd') + '.csv'
   $file3 = 'TORON_RT' + $filedate.ToString('yyyyMMdd') + '.csv'
    # Download files
    $transferOptions = New-Object WinSCP.TransferOptions
    $transferOptions.TransferMode = [WinSCP.TransferMode]::Binary
    $transferResult =
        $session.GetFiles("/users/TORON/RPT/" + $file1, "\\ta_nas\Cidel\ReconData\NBCN\Toron\", $False, $transferOptions)
   $transferResult =
        $session.GetFiles("/users/TORON/RPT/" + $file2, "\\ta_nas\Cidel\ReconData\NBCN\Toron\", $False, $transferOptions)
   $transferResult =
        $session.GetFiles("/users/TORON/RPT/" + $file3, "\\ta_nas\Cidel\ReconData\NBCN\Toron\", $False, $transferOptions)
    # Disconnect, clean up

Powershell script on SQL agent no longer working


I created a powershell script that is scheduled in SQL Agent(2012), it is one of many steps in a process to download and process data. Since Oct 2 this script is no longer executed - on this day Security Update for Windows (KB4522007)
- it appears that the library cannot be loaded when executed from SQL agent - I can execute the script as user logged in on the machine via RDP or a scheduled task in the Windows task scheduler. I suspect that this the access to dll files has been restricted to the SQL agent as a measure of increasing security. How else can I refer to the assembly? Can I move the file(s) to another location that SQL agent has access to?

Below the error message:

PowerShell script. The corresponding line is ' Add-Type -Path "C:\Program Files (x86)\WinSCP\WinSCPnet.dll"'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Could not load file or assembly 'file:///C:\Program Files (x86)\WinSCP\WinSCPnet.dll' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded. '. Process Exit Code -1. The step failed.\

Thanks for any hint what I could do to fix this.