Question

Peter Skoglund on Fri, 29 Aug 2014 09:18:07


I run a SQL Server Agent job that has a step of the Powershell type, when a new SMO was installed the job started to fail. The exact same code run fine on other sql servers that does not have the new SMO installed yet.  The version that has 12.0.2000.8 fails but works for version 11.2.5058.0.

The error is

The corresponding line is '$resultList =   Invoke-PolicyEvaluation  -Policy $policy  -TargetServer $TargetServer -TargetExpression $TargetExpression'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Value cannot be null.

Parameter name: policy'

Note, I have tried to get it to use a another smo with changing

Import-Module 'sqlps' –DisableNameChecking

to

Add-Type -AssemblyName "Microsoft.SqlServer.Smo"

But no luck.

Plz HELP!

Replies

Elvis Long on Tue, 02 Sep 2014 02:08:19


Hi Peter,

Thank you for your question. 

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.

If you have any feedback on our support, please click here.

Jinchun Chen on Tue, 02 Sep 2014 05:01:51


Hi Peter,

From the message "Parameter name: policy", we can know the issue was caused by the value of $policy(for pamaeter "Policy") is null. $policy is a variable, so there must has other code to assign value to it.

So, please review the code that is used to assign value to $policy. Or, please run the PowerShell script manually, and ensure it works. Or, please share us the code, it would be helpful to narrow down the issue.

Thanks,
Jinchun Chen

Peter Skoglund on Tue, 02 Sep 2014 09:11:17


Hi,

Below is my code(just replace XXX), as you can see fron the debug print the $policy variable has a value.

Import-Module 'sqlps' –DisableNameChecking

$TargetExpression="Server[@Name='XXX\XXX']/Database[@Name='XXX']"
$TargetServer="XXX\XXX"
$PolicySourceServer= "XXX\XXX"

#Setup a connection to the policy store 
$Conn = new-object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=$PolicySourceServer;Trusted_Connection=true"); 
$PolStore = new-object Microsoft.SqlServer.Management.DMF.PolicyStore($Conn); 



foreach ($Policy in $PolStore.Policies) 
{ 
    $tmp= "Set="+$Policy.Id + " name="+$Policy.Name + " Category="+ $Policy.PolicyCategory
    Write-Output  ("$tmp")
			    

    $resultList =   Invoke-PolicyEvaluation  -Policy $Policy  -TargetServer $TargetServer -TargetExpression $TargetExpression
    foreach ($res in $resultList)
    {					
        Write-Output  $res.Result; 						
    }                
}

########################

Set=100 name=AlwaysOnAgAutomaticFailoverHealthPolicy Category=Availability group errors (primary replica only)
Invoke-PolicyEvaluation : Value cannot be null.
Parameter name: policy
At line:19 char:44
+     $resultList =   Invoke-PolicyEvaluation <<<<   -Policy $policy  -TargetServer $TargetServer -TargetExpression $TargetExpression
    + CategoryInfo          : NotSpecified: (:) [Invoke-PolicyEvaluation], ArgumentNullException
    + FullyQualifiedErrorId : System.ArgumentNullException,Microsoft.SqlServer.Management.PowerShell.InvokePolicyEvaluationCommand

Set=99 name=AlwaysOnAgOnlineStateHealthPolicy Category=Availability group errors (any replica role)
Invoke-PolicyEvaluation : Value cannot be null.
Parameter name: policy
At line:19 char:44
+     $resultList =   Invoke-PolicyEvaluation <<<<   -Policy $policy  -TargetServer $TargetServer -TargetExpression $TargetExpression
    + CategoryInfo          : NotSpecified: (:) [Invoke-PolicyEvaluation], ArgumentNullException
    + FullyQualifiedErrorId : System.ArgumentNullException,Microsoft.SqlServer.Management.PowerShell.InvokePolicyEvaluationCommand 
...

Jinchun Chen on Tue, 02 Sep 2014 10:55:46


Hi Peter,

Thanks for posting the detailed PowerShell script.

I verified that, in 12.0.2000.8, the error message "Parameter name: policy" is thrown by another internal function. Invoke-PolicyEvaluation assigns $policy to an internal object "Policy", then Invoke-PolicyEvaluation uses the internal object as parameter to call the internal function. When the internal object is null, the internal function throw the mentioned error message. 
It seems when $policy is null, the error happens. However, Invoke-PolicyEvaluation will have a NullOrEmpty check at first, when $policy is null,  the error message should be like:
Cannot validate argument on parameter 'Policy'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.

So, the issue now is why the internal "Policy" object was changed to null. It is hard to say what is wrong right now. I need your help to write out the full call stack to help to narrow down the issue.
Could you please help to include the following code in your script, run it, and pose the detailed call stack?

function Resolve-Error ($ErrorRecord=$Error[0])
{
   $ErrorRecord | Format-List * -Force
   $ErrorRecord.InvocationInfo |Format-List *
   $Exception = $ErrorRecord.Exception
   for ($i = 0; $Exception; $i++, ($Exception = $Exception.InnerException))
   {   "$i" * 80
       $Exception |Format-List * -Force
   }
}

Resolve-Error($error)

Thanks,
Jinchun Chen

Peter Skoglund on Wed, 03 Sep 2014 06:25:07


Hi Jinchun,

I included your code and rerun my script, below is the result.

Set=100 name=AlwaysOnAgAutomaticFailoverHealthPolicy Category=Availability group errors (primary replica only)
Invoke-PolicyEvaluation : Value cannot be null.
Parameter name: policy
At H:\PowerShellDBA\Test policy.ps1:19 char:44
+     $resultList =   Invoke-PolicyEvaluation <<<<   -Policy $Policy  -TargetServer $TargetServer -TargetExpression $TargetExpression
    + CategoryInfo          : NotSpecified: (:) [Invoke-PolicyEvaluation], ArgumentNullException
    + FullyQualifiedErrorId : System.ArgumentNullException,Microsoft.SqlServer.Management.PowerShell.InvokePolicyEvaluationCommand
 
Set=99 name=AlwaysOnAgOnlineStateHealthPolicy Category=Availability group errors (any replica role)
Invoke-PolicyEvaluation : Value cannot be null.
Parameter name: policy
At H:\PowerShellDBA\Test policy.ps1:19 char:44
+     $resultList =   Invoke-PolicyEvaluation <<<<   -Policy $Policy  -TargetServer $TargetServer -TargetExpression $TargetExpression
    + CategoryInfo          : NotSpecified: (:) [Invoke-PolicyEvaluation], ArgumentNullException
    + FullyQualifiedErrorId : System.ArgumentNullException,Microsoft.SqlServer.Management.PowerShell.InvokePolicyEvaluationCommand
 

PSMessageDetails      : 
Exception             : System.ArgumentNullException: Value cannot be null.
                        Parameter name: policy
                           at Microsoft.SqlServer.Management.PowerShell.PolicyPolicyEvaluator..ctor(Policy policy)
                           at Microsoft.SqlServer.Management.PowerShell.PolicyEvaluatorFromPolicyObjects.<GetEnumerable>d__0.MoveNext()
                           at Microsoft.SqlServer.Management.PowerShell.InvokePolicyEvaluationCommand.ProcessRecord()
                           at System.Management.Automation.CommandProcessor.ProcessRecord()
TargetObject          : 
CategoryInfo          : NotSpecified: (:) [Invoke-PolicyEvaluation], ArgumentNullException
FullyQualifiedErrorId : System.ArgumentNullException,Microsoft.SqlServer.Management.PowerShell.InvokePolicyEvaluationCommand
ErrorDetails          : 
InvocationInfo        : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {0, 1}

PSMessageDetails      : 
Exception             : System.ArgumentNullException: Value cannot be null.
                        Parameter name: policy
                           at Microsoft.SqlServer.Management.PowerShell.PolicyPolicyEvaluator..ctor(Policy policy)
                           at Microsoft.SqlServer.Management.PowerShell.PolicyEvaluatorFromPolicyObjects.<GetEnumerable>d__0.MoveNext()
                           at Microsoft.SqlServer.Management.PowerShell.InvokePolicyEvaluationCommand.ProcessRecord()
                           at System.Management.Automation.CommandProcessor.ProcessRecord()
TargetObject          : 
CategoryInfo          : NotSpecified: (:) [Invoke-PolicyEvaluation], ArgumentNullException
FullyQualifiedErrorId : System.ArgumentNullException,Microsoft.SqlServer.Management.PowerShell.InvokePolicyEvaluationCommand
ErrorDetails          : 
InvocationInfo        : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {0, 1}

Jinchun Chen on Wed, 03 Sep 2014 07:28:28


Hi Peter,

Appreciated.

From the call stack, the issue happened when tried to initialize an instance of PolicyPolicyEvaluator. The constructor function of PolicyPolicyEvaluator request an object, which is come from $policy.BaseObject(It is a policy array).

Seems one policy from $policy.BaseObject is null, which cause the issue.

To verify it, could you please replace the original Foreach code with the following one, and post the output here?

foreach ($Policy in $PolStore.Policies) 
{ 
    $tmp= "Set="+$Policy.Id + " name="+$Policy.Name + " Category="+ $Policy.PolicyCategory
    Write-Output  ("$tmp")
    $baseObject =  $Policy.PSObject.BaseObject -as [Microsoft.SqlServer.Management.Dmf.Policy] 
    if($baseObject)
    {
	$str =  "        BaseObject is " + $baseObject.Name + ",with Id " +  $baseObject.Id
	Write-Output $str
    }
    else
    {
	write-output "BaseObject is null"
    }                    
}

Thanks,
Jinchun Chen


Jinchun Chen on Wed, 03 Sep 2014 08:25:39


Hi Peter,

I updated the code in my last reply. Please note it.

The code it to simulate the things PowerShell does. We need to ensure everything is going in the right way.

Thanks,
Jinchun Chen


Peter Skoglund on Wed, 03 Sep 2014 10:45:40


Hi Jinchun,

Below is the new result. (I still think the problem could have something to do with the different SMO versions.
The same script works on sql servers that does not have the new SMO installed yet. 
The version that has 12.0.2000.8 fails but works for version 11.2.5058.0.)

Set=100 name=AlwaysOnAgAutomaticFailoverHealthPolicy Category=Availability group errors (primary replica only)
        BaseObject is AlwaysOnAgAutomaticFailoverHealthPolicy,with Id 100
Set=99 name=AlwaysOnAgOnlineStateHealthPolicy Category=Availability group errors (any replica role)
        BaseObject is AlwaysOnAgOnlineStateHealthPolicy,with Id 99

PSMessageDetails      : 
Exception             : System.ArgumentNullException: Value cannot be null.
                        Parameter name: policy
                           at Microsoft.SqlServer.Management.PowerShell.PolicyPolicyEvaluator..ctor(Policy policy)
                           at Microsoft.SqlServer.Management.PowerShell.PolicyEvaluatorFromPolicyObjects.<GetEnumerable>d__0.MoveNext()
                           at Microsoft.SqlServer.Management.PowerShell.InvokePolicyEvaluationCommand.ProcessRecord()
                           at System.Management.Automation.CommandProcessor.ProcessRecord()
TargetObject          : 
CategoryInfo          : NotSpecified: (:) [Invoke-PolicyEvaluation], ArgumentNullException
FullyQualifiedErrorId : System.ArgumentNullException,Microsoft.SqlServer.Management.PowerShell.InvokePolicyEvaluationCommand
ErrorDetails          : 
InvocationInfo        : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {0, 1}

PSMessageDetails      : 
Exception             : System.ArgumentNullException: Value cannot be null.
                        Parameter name: policy
                           at Microsoft.SqlServer.Management.PowerShell.PolicyPolicyEvaluator..ctor(Policy policy)
                           at Microsoft.SqlServer.Management.PowerShell.PolicyEvaluatorFromPolicyObjects.<GetEnumerable>d__0.MoveNext()
                           at Microsoft.SqlServer.Management.PowerShell.InvokePolicyEvaluationCommand.ProcessRecord()
                           at System.Management.Automation.CommandProcessor.ProcessRecord()
TargetObject          : 
CategoryInfo          : NotSpecified: (:) [Invoke-PolicyEvaluation], ArgumentNullException
FullyQualifiedErrorId : System.ArgumentNullException,Microsoft.SqlServer.Management.PowerShell.InvokePolicyEvaluationCommand
ErrorDetails          : 
InvocationInfo        : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {0, 1}

Peter Skoglund on Fri, 05 Sep 2014 08:22:55


Hi Jinchun,

Any progress? I found a thread where they get the same error massage

http://epmframework4.rssing.com/chan-8364372/all_p2.html

Jinchun Chen on Fri, 05 Sep 2014 08:50:06


Hi Peter,

Could you please help to list out the loaded assemblies?
[appdomain]::CurrentDomain.GetAssemblies()

I am trying to reproduce the issue. Seems I have to installed a earlier version, and them install 12.0.8000 to reproduce it.

Thanks,
Jinchun Chen

Peter Skoglund on Fri, 05 Sep 2014 11:26:30


This is the result

Working Sql Server
Location=C:\Windows\Microsoft.NET\Framework64\v2.0.50727\mscorlib.dll
Location=C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\SQLPS.exe
Location=C:\Windows\assembly\GAC_MSIL\System.Management.Automation\1.0.0.0__31bf3856ad364e35\System.Management.Automation.dll
Location=C:\Windows\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.PowerShell.ConsoleHost\1.0.0.0__31bf3856ad364e35\Microsoft.PowerShell.ConsoleHost.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.PowerShell.Commands.Diagnostics\1.0.0.0__31bf3856ad364e35\Microsoft.PowerShell.Commands.Diagnostics.dll
Location=C:\Windows\assembly\GAC_MSIL\System.Core\3.5.0.0__b77a5c561934e089\System.Core.dll
Location=C:\Windows\assembly\GAC_MSIL\System.Configuration.Install\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.Install.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.WSMan.Management\1.0.0.0__31bf3856ad364e35\Microsoft.WSMan.Management.dll
Location=C:\Windows\assembly\GAC_64\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.PowerShell.Commands.Utility\1.0.0.0__31bf3856ad364e35\Microsoft.PowerShell.Commands.Utility.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.PowerShell.Commands.Management\1.0.0.0__31bf3856ad364e35\Microsoft.PowerShell.Commands.Management.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.PowerShell.Security\1.0.0.0__31bf3856ad364e35\Microsoft.PowerShell.Security.dll
Location=C:\Windows\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll
Location=C:\Windows\assembly\GAC_MSIL\System.Management\2.0.0.0__b03f5f7f11d50a3a\System.Management.dll
Location=C:\Windows\assembly\GAC_MSIL\System.DirectoryServices\2.0.0.0__b03f5f7f11d50a3a\System.DirectoryServices.dll
Location=C:\Windows\assembly\GAC_64\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Dmf\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Dmf.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlWmiManagement\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlWmiManagement.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SmoExtended\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SmoExtended.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.RegisteredServers\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.RegisteredServers.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlEnum\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.RegSvrEnum\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.RegSvrEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.WmiEnum\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.WmiEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ServiceBrokerEnum\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ServiceBrokerEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Collector\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Collector.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.CollectorEnum\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.CollectorEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Utility\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Utility.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.UtilityEnum\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.UtilityEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.HadrDMF\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.HadrDMF.dll
Location=C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll
Location=C:\Windows\assembly\GAC_64\Microsoft.SqlServer.BatchParser\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.BatchParser.dll
Location=C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSProvider.dll
Location=C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Microsoft.AnalysisServices.PowerShell.Provider.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.AnalysisServices\11.0.0.0__89845dcd8080cc91\Microsoft.AnalysisServices.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlClrProvider\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlClrProvider.dll
Location=C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLASCMDLETS\Microsoft.AnalysisServices.PowerShell.Cmdlets.dll

Not working Sql Server
Location=C:\Windows\Microsoft.NET\Framework64\v2.0.50727\mscorlib.dll
Location=C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\SQLPS.exe
Location=C:\Windows\assembly\GAC_MSIL\System.Management.Automation\1.0.0.0__31bf3856ad364e35\System.Management.Automation.dll
Location=C:\Windows\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.PowerShell.ConsoleHost\1.0.0.0__31bf3856ad364e35\Microsoft.PowerShell.ConsoleHost.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.PowerShell.Commands.Diagnostics\1.0.0.0__31bf3856ad364e35\Microsoft.PowerShell.Commands.Diagnostics.dll
Location=C:\Windows\assembly\GAC_MSIL\System.Core\3.5.0.0__b77a5c561934e089\System.Core.dll
Location=C:\Windows\assembly\GAC_MSIL\System.Configuration.Install\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.Install.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.WSMan.Management\1.0.0.0__31bf3856ad364e35\Microsoft.WSMan.Management.dll
Location=C:\Windows\assembly\GAC_64\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.PowerShell.Commands.Utility\1.0.0.0__31bf3856ad364e35\Microsoft.PowerShell.Commands.Utility.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.PowerShell.Commands.Management\1.0.0.0__31bf3856ad364e35\Microsoft.PowerShell.Commands.Management.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.PowerShell.Security\1.0.0.0__31bf3856ad364e35\Microsoft.PowerShell.Security.dll
Location=C:\Windows\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll
Location=C:\Windows\assembly\GAC_MSIL\System.Management\2.0.0.0__b03f5f7f11d50a3a\System.Management.dll
Location=C:\Windows\assembly\GAC_MSIL\System.DirectoryServices\2.0.0.0__b03f5f7f11d50a3a\System.DirectoryServices.dll
Location=C:\Windows\assembly\GAC_64\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Dmf\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Dmf.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlWmiManagement\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlWmiManagement.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SmoExtended\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SmoExtended.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.RegisteredServers\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.RegisteredServers.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlEnum\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.RegSvrEnum\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.RegSvrEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.WmiEnum\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.WmiEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ServiceBrokerEnum\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ServiceBrokerEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Collector\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Collector.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.CollectorEnum\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.CollectorEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Utility\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Utility.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.UtilityEnum\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.UtilityEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.HadrDMF\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.HadrDMF.dll
Location=C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SmoExtended\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SmoExtended.dll
Location=C:\Windows\assembly\GAC_64\Microsoft.SqlServer.BatchParser\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.BatchParser.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlWmiManagement\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlWmiManagement.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlEnum\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Dmf\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Dmf.dll
Location=C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSProvider.dll
Location=C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Microsoft.AnalysisServices.PowerShell.Provider.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.AnalysisServices\11.0.0.0__89845dcd8080cc91\Microsoft.AnalysisServices.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.RegisteredServers\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.RegisteredServers.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.WmiEnum\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.WmiEnum.dll
Location=C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlClrProvider\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlClrProvider.dll
Location=C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLASCMDLETS\Microsoft.AnalysisServices.PowerShell.Cmdlets.dll


Jinchun Chen on Sun, 07 Sep 2014 13:46:17


Hi Peter,

Thanks a lot for the posting.

From the loaded assemblies list, we can see the version of the sqlps module for the "not working" environment is still 110. Two versions of Microsoft.SqlServer.Dmf.dll were loaded. However, the expert version of sqlps is 120. It is expected to load Microsoft.SqlServer.Dmf.dll with 12.0 only.

I finally forced my test lab to load sqlps with 110 version, and reproduced the issue.
What I did is changing the system environment variable PSModulePath to the following value:

%SystemRoot%\system32\WindowsPowerShell\v1.0\Modules\;c:\Program Files\Microsoft Security Client\MpProvider\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\;C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\

110 is listed before 120.

To force the PowerShell to import sqlps module with 120 version. Please list path for 120 version before 110. The value should be:
%SystemRoot%\system32\WindowsPowerShell\v1.0\Modules\;c:\Program Files\Microsoft Security Client\MpProvider\;C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\

It is the solution to fix the issue.

Thanks,
Jinchun Chen

Jinchun Chen on Sun, 07 Sep 2014 13:59:11


The cause to the issue is that:

  1. sqlps is loaded with version 11
  2. The actual used "Microsoft.SqlServer.Dmf"  has version 12
  3. sqlps try to initialize an instance of Policy with version 12 from parameter "-Policy", which is version 11, and send to PolicyEvaluatorFromPolicyObjects. Trying to convert a lower version Policy to the higher version should be fail. So that , sqlps may send an null instance to PolicyEvaluatorFromPolicyObjects
  4. While initializing PolicyPolicyEvaluator by using the policy got from PolicyEvaluatorFromPolicyObjects, we will get the error as the policy is null

Thanks,
Jinchun Chen

Peter Skoglund on Thu, 11 Sep 2014 10:44:32


Changing the system environment variable för the PSModulePath order to have the list path for 120 version before 110 did the trick. Thanks!