Error not loading any data in to PolicyHistory

Apr 24 at 9:23 PM
I have installed the components for this and think I have everything in place. I have tried to run the powershell with filters and categories and without and still get nothing stored for PolicyHistory or PolicyHistoryDetail. Everything goes to EvaluationErrors with the error below. I am not sure why it is saying parameter missing. I have checked my policy and I can evaluate them manually without error.

System.ArgumentNullException, Value cannot be null.
Parameter name: policy
Aug 4 at 1:53 PM
Hi gbargsle

I had this problem too and it took me quite some days to figure this issue out.

the issue lies in the fact that if you have installed 'SQL Server 2012 SMO' another windows assembly is created called 'Microsoft.SqlServer.Smo' that has version 11.0.0.0.

this framework seems to only work with the 10.0.0.0 version (SQL Server 2008) . Therefor the only way to make sure that the script uses the version 10 assemblies you have to specify them manually in the powershell script, like so:

Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Dmf\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Dmf.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlWmiManagement\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlWmiManagement.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SmoExtended\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SmoExtended.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.RegisteredServers\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.RegisteredServers.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlEnum\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlEnum.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.RegSvrEnum\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.RegSvrEnum.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.WmiEnum\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.WmiEnum.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ServiceBrokerEnum\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ServiceBrokerEnum.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfoExtended\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfoExtended.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Collector\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Collector.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.CollectorEnum\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.CollectorEnum.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlClrProvider\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlClrProvider.dll"


this codeblock needs to substitute the following part of the script:

$assemblylist =
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum"

foreach ($asm in $assemblylist)
{
$asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}

once this change has been put into place, you force the script to use these assemblies and the evaluation script should work.
It appears that something has changed in the SQL 2012 SMO (probably extra parameter) that causes the current script to fail.

Hope this helps.

Kind regards,
Geoffrey
Aug 4 at 2:21 PM
This is great information. I cannot find where to put this update. The only file I have is the EPM_EnterpriseEvaluation_3.0.0.ps1 and do not show where it loads any assemblies. Any information on where to place these changes would be helpful.
Aug 4 at 2:39 PM
it seems we had updated the script a bit, anyway the code block should be put just before the initialization of the variables, so right above the following:

$CentralManagementServer = "WIN2008"
$HistoryDatabase = "MDW"

if needed I can upload our configuration (with the servernames and locations omitted ofcourse)

kind regards,
Geoffrey
Aug 4 at 2:51 PM
Can you upload your code if possible? I added those where you mentioned and still not working.

gbargsley@gmail.com
Aug 4 at 3:06 PM
script contents below (for future reference if someone else has this issue) usage is just change the paths + execute via powershell commandline
#
# Initialize-SqlpsEnvironment.ps1
#
# Loads the SQL Server provider extensions
#
# Usage: Powershell -NoExit -Command "& '.\Initialize-SqlPsEnvironment.ps1'"
#
# Change log:
# June 14, 2008: Michiel Wories
#   Initial Version
# June 17, 2008: Michiel Wories
#   Fixed issue with path that did not allow for snapin\provider:: prefix of path
#   Fixed issue with provider variables. Provider does not handle case yet
#   that these variables do not exist (bug has been filed)
$ErrorActionPreference = "Stop"
$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Powershell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}

#
# Preload the assemblies. Note that most assemblies will be loaded when the provider
# is used. if you work only within the provider this may not be needed. It will reduce
# the shell's footprint if you leave these out.
#
#$assemblylist = 
#"Microsoft.SqlServer.Dmf ",
#"Microsoft.SqlServer.SqlWmiManagement ",
#"Microsoft.SqlServer.ConnectionInfo ",
#"Microsoft.SqlServer.Management.RegisteredServers ",
#"Microsoft.SqlServer.Management.Sdk.Sfc ",
#"Microsoft.SqlServer.SqlEnum ",
#"Microsoft.SqlServer.RegSvrEnum ",
#"Microsoft.SqlServer.WmiEnum ",
#"Microsoft.SqlServer.ServiceBrokerEnum ",
#"Microsoft.SqlServer.ConnectionInfoExtended ",
#"Microsoft.SqlServer.Management.Collector ",
#"Microsoft.SqlServer.Management.CollectorEnum"

#foreach ($asm in $assemblylist)
#{
#    $asm = [Reflection.Assembly]::LoadWithPartialName($asm)
#}

#
# Set variables that the provider expects (mandatory for the SQL provider)
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll"                                   
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Dmf\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Dmf.dll"                                     
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlWmiManagement\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlWmiManagement.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll"          
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SmoExtended\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SmoExtended.dll"              
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.RegisteredServers\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.RegisteredServers.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll"       
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlEnum\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlEnum.dll"                             
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.RegSvrEnum\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.RegSvrEnum.dll"                       
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.WmiEnum\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.WmiEnum.dll"                             
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ServiceBrokerEnum\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ServiceBrokerEnum.dll"         
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfoExtended\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfoExtended.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Collector\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Collector.dll"   
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.CollectorEnum\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.CollectorEnum.dll"
Add-Type -path “C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlClrProvider\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlClrProvider.dll"


#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml 
update-FormatData -prependpath SQLProvider.Format.ps1xml 
Pop-Location
#Write-Host -ForegroundColor Yellow 'SQL Server Powershell extensions are loaded.'
#Write-Host
#Write-Host -ForegroundColor Yellow 'Type "cd SQLSERVER:\" to step into the provider.'
#Write-Host
#Write-Host -ForegroundColor Yellow 'For more information, type "help SQLServer".'

# Evaluate specific Policies against a Server List
# Uses the Invoke-PolicyEvaluation Cmdlet

# param([string]$ConfigurationGroup = $(Throw "Parameter missing: -ConfigurationGroup ConfigGroup"),[string]$PolicyCategoryFilter=$(Throw "Parameter missing: -PolicyCategoryFilter Category"), [string]$EvalMode=$(Throw "Parameter missing: -EvalMode EvalMode"))

# Parameter -ConfigurationGroup specifies the 
# Central Management Server group to evaluate
# Parameter -PolicyCategoryFilter specifies the 
# category of policies to evaluate
# Parameter -EvalMode accepts "Check" to report policy
# results, "Configure" to reconfigure any violations 

# Declare variables to define the central warehouse
# in which to write the output, store the policies
$CentralManagementServer = "WIN2008"
$HistoryDatabase = "MDW"
# Define the location to write the results of the
# policy evaluation.  Delete any files in the directory.
$ResultDir = "e:\Results\"
$ResultDirDel = $ResultDir + "*.xml"
Remove-Item -Path $ResultDirDel
$ConfigurationGroup = "PROD"
$PolicyCategoryFilter = ""
$EvalMode = "Check"
# End of variables

#Function to insert policy evaluation results
#into SQL Server - table policy.PolicyHistory
function PolicyHistoryInsert($sqlServerVariable, $sqlDatabaseVariable, $EvaluatedServer, $EvaluatedPolicy, $EvaluationResults) 
{
   &{
    $sqlQueryText = "INSERT INTO policy.PolicyHistory (EvaluatedServer, EvaluatedPolicy, EvaluationResults) VALUES(N'$EvaluatedServer', N'$EvaluatedPolicy', N'$EvaluationResults')"
    Invoke-Sqlcmd -ServerInstance $sqlServerVariable -Database $sqlDatabaseVariable -Query $sqlQueryText -ErrorAction Stop
    }
    trap
    {
      $ExceptionText = $_.Exception.Message -replace "'", "" 
    }
}

#Function to insert policy evaluation errors 
#into SQL Server - table policy.EvaluationErrorHistory
function PolicyErrorInsert($sqlServerVariable, $sqlDatabaseVariable, $EvaluatedServer, $EvaluatedPolicy, $EvaluationResultsEscape) 
{
    &{
    $sqlQueryText = "INSERT INTO policy.EvaluationErrorHistory (EvaluatedServer, EvaluatedPolicy, EvaluationResults) VALUES(N'$EvaluatedServer', N'$EvaluatedPolicy', N'$EvaluationResultsEscape')"
    Invoke-Sqlcmd -ServerInstance $sqlServerVariable -Database $sqlDatabaseVariable -Query $sqlQueryText -ErrorAction Stop
    }
    trap
    {
      $ExceptionText = $_.Exception.Message -replace "'", "" 
    }
}

# Connection to the policy store
$conn = new-object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server=$CentralManagementServer;Trusted_Connection=true");
$PolicyStore = new-object Microsoft.SqlServer.Management.DMF.PolicyStore($conn);

# Create recordset of servers to evaluate
$sconn = new-object System.Data.SqlClient.SqlConnection("server=$CentralManagementServer;Trusted_Connection=true");
$q = "SELECT DISTINCT server_name FROM $HistoryDatabase.[policy].[pfn_ServerGroupInstances]('$ConfigurationGroup');"

$sconn.Open()
$cmd = new-object System.Data.SqlClient.SqlCommand ($q, $sconn);
$cmd.CommandTimeout = 0;
$dr = $cmd.ExecuteReader();

# Loop through the servers and then loop through
# the policies.  For each server and policy,
# call cmdlet to evaluate policy on server

while ($dr.Read()) { 
    $ServerName = $dr.GetValue(0);
    foreach ($Policy in $PolicyStore.Policies)
   {
        if (($Policy.PolicyCategory -eq $PolicyCategoryFilter)-or ($PolicyCategoryFilter -eq ""))
    {
        &{
            $OutputFile = $ResultDir + ("{0}_{1}.xml" -f (Encode-SqlName $ServerName ), (Encode-SqlName $Policy.Name));
            Invoke-PolicyEvaluation -Policy $Policy -TargetServerName $ServerName -AdHocPolicyEvaluationMode $EvalMode -OutputXML > $OutputFile;
            $PolicyResult = Get-Content $OutputFile -encoding UTF8;
            $PolicyResult = $PolicyResult -replace "'", "" 
            PolicyHistoryInsert $CentralManagementServer $HistoryDatabase $ServerName $Policy.Name $PolicyResult;
        }
            trap [Exception]
            { 
                  $ExceptionText = $_.Exception.Message -replace "'", "" 
                  $ExceptionMessage = $_.Exception.GetType().FullName + ", " + $ExceptionText
                  PolicyErrorInsert $CentralManagementServer $HistoryDatabase $ServerName $Policy.Name $ExceptionMessage;
                  continue;   
            }       
    }
   } 
 }

$dr.Close()
$sconn.Close()

#Shred the XML results to PolicyHistoryDetails
Invoke-Sqlcmd -ServerInstance $CentralManagementServer -Database $HistoryDatabase -Query "exec policy.epm_LoadPolicyHistoryDetail"  -ErrorAction Stop
Kind regards,
Geoffrey
Aug 4 at 4:15 PM
This still does not work. I am running SQL 2014 on Windows Server 2012 R2. I have put your script in and get Add-PSSnapin : No snap-ins have been registered for Windows PowerShell version 4.

I have installed the 2008 R2 feature pack for CLR, ShareManagementObjects and PowerShell and still no luck.

Thanks for your help though. I might try on a lower version test server to see what happens.