Steps to take if PS script test yields no results in either view

Dec 17, 2014 at 5:36 PM
What troubleshooting steps can be taken in the event testing the PS script yields no results in either view?

I am in that situation and the solution in a similar thread does not work. I'm running SQL 2012 as a CMS using EPM 4.1 and trying to evaluate servers from 2012 to 2005 both named and default instances.

Thanks for any help.
Coordinator
Dec 17, 2014 at 5:38 PM
Try evaluating policies manually (via CMS) on servers and see if you get anything. Also see if latest updates (4.1.1+) helps out at all.

Sent from my Windows Phone

Dec 17, 2014 at 5:49 PM
Thanks for the prompt feedback. I re-registered my test servers and ran policy evaluation from the CMS. I got some records in the second view but, with a discouraging message in each records evaluation results column. It read:

"System.ArgumentNullException, Value cannot be null.
Parameter name: policy"

And to clarify, this is version 4.1.1

Mike
Coordinator
Dec 17, 2014 at 6:06 PM
You're very welcome. At first glance and I'm not sure how this would be, but it looks like the policy doesn't have a name (or maybe condition your using) or at least its not parsing name correctly. Do you have oddly named policies or conditions?

Sent from my Windows Phone

Dec 17, 2014 at 7:02 PM
Edited Dec 17, 2014 at 7:03 PM
I don't think so. I imported them from the SQL Server Best Practices.msi installation.

Names are, for example:
Data and Log File Location
Database Auto Close
etc.

Evaluating an individual policy locally on the CMS works just fine.
Coordinator
Dec 17, 2014 at 8:03 PM
Are you evaluating multiple policies at once? Try evaluating one at a time to find problem one. Wondering if a condition in the MS ones is tripping you up.

Sent from my Windows Phone

Dec 17, 2014 at 8:07 PM
Edited Dec 17, 2014 at 8:12 PM
I'll try each individually and report back. Thanks very much for the help.

The policy titled "Last Successful Backup" throws:

TITLE: Microsoft SQL Server Management Studio

Exception encountered while executing policy 'Last Successful Backup Date'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)&EvtSrc=PolicyEvaluationException&LinkId=20476

ADDITIONAL INFORMATION:

Facets mismatch. The Object Set 'Last Successful Backup Date_ObjectSet' references the Management Facet 'IDatabaseMaintenanceFacet'. The Condition 'Safe Last Full or Diff Backup Date' references the Management Facet 'Database'. (Microsoft.SqlServer.Dmf)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)&LinkId=20476

BUTTONS:

OK

THe policy titled "SQL Server Password Policy" throws:

TITLE: Microsoft SQL Server Management Studio

Exception encountered while executing policy 'SQL Server Password Policy'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)&EvtSrc=PolicyEvaluationException&LinkId=20476

ADDITIONAL INFORMATION:

Object reference not set to an instance of an object. (Microsoft.SqlServer.Dmf)

BUTTONS:

OK

I did not import all of the policies, just a few for testing.
Dec 23, 2014 at 11:51 PM
I experienced this as well on my first tries at the PowerShell script. tl;dr: try commenting out the line for 'LoadAssemblies' in the PowerShell script if your host machine has SQL Server 2012 client tools.

After adding write-host and traps to figure out the error, the exception occurred on the line:
(line 162 as of Release 4.1.1)
Invoke-PolicyEvaluation -Policy $Policy -TargetServerName $ServerName -AdHocPolicyEvaluationMode $EvalMode -OutputXML > $OutputFile
Exception is:
'Value cannot be null. Parameter name: policy'

Stumbled upon this write up on the web (https://social.technet.microsoft.com/Forums/scriptcenter/en-US/0573fc94-3f31-4718-a683-4b7091fe16b2/invokepolicyevaluation-fails-with-value-cannot-be-null-parameter-name-policy?forum=sqlsmoanddmo) which appears to confirm from a Microsoft technician that the null parameter is happening within the 'Invoke-PolicyEvaluation' cmdlet. At least for a certain version of the dll. The post describes re-ordering an environment variable to prefer the SQL Server 2014 (\120) tools.

In my environment - a tools server - Windows Server 2008 + SQL Server 2012, the 2014 tools aren't yet installed. As an experiment, I commented out the loading of custom assemblies since EPM 3.0 had been working fine and doesn't include such a step. This did the trick:
(line 110 as of Release 4.1.1)
#LoadAssemblies
The environment is an inplace upgrade from Server 2008R2 to Server 2012, which I would typically avoid in favor of a clean install. That or some combination of the Server 2008 r2 patch level might be why this is uncommon.

Not sure the ramifications of not loading assemblies for other environments, but in this case, all the required funcationality appears to be present because of the load of the 'sqlps' module (?)
Coordinator
Dec 24, 2014 at 5:22 PM
Awesome find, thanks for sharing solution! We'll see if we can implement a version check in shell script to try and avoid this issue moving forward (unless someone else has better approach).


Sent from my Windows Phone

Dec 31, 2014 at 7:57 PM
I dunno I'm still not having any luck. When I run the EPM_EnterpriseEValuation_41.ps1 I get something along the following:


Date.xml' because it does not exist.
At C:\users\public\Documents\EPM_EnterpriseEvaluation_41.ps1:164 char:31
  • $PolicyResult = Get-Content <<<< $OutputFile -encoding UTF8
    • CategoryInfo : ObjectNotFound: (C:\Results\VRT0...lBackupDate.x
      ml:String) [Get-Content], ItemNotFoundException
    • FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetCo
      ntentCommand
Get-Content : Cannot find path 'C:\Results\VRT01%5CDEV2012_SQLServerMaxDegreeof
Parallelism.xml' because it does not exist.
At C:\users\public\Documents\EPM_EnterpriseEvaluation_41.ps1:164 char:31
  • $PolicyResult = Get-Content <<<< $OutputFile -encoding UTF8
    • CategoryInfo : ObjectNotFound: (C:\Results\VRT0...Parallelism.x
      ml:String) [Get-Content], ItemNotFoundException
    • FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetCo
      ntentCommand
Get-Content : Cannot find path 'C:\Results\VRT01%5CDEV2012_SQLServerPasswordPol
icy.xml' because it does not exist.
At C:\users\public\Documents\EPM_EnterpriseEvaluation_41.ps1:164 char:31
  • $PolicyResult = Get-Content <<<< $OutputFile -encoding UTF8
    • CategoryInfo : ObjectNotFound: (C:\Results\VRT0...swordPolicy.x
      ml:String) [Get-Content], ItemNotFoundException
    • FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetCo
      ntentCommand
VERBOSE: Dec 31 2014 1:55:07:607PM - Starting data integration for ALL
categories
VERBOSE: Dec 31 2014 1:55:07:607PM |- 0 rows inserted...
VERBOSE: Dec 31 2014 1:55:07:607PM - Starting no target data integration
VERBOSE: Dec 31 2014 1:55:07:607PM |- 0 rows inserted...
VERBOSE: Dec 31 2014 1:55:07:607PM - Starting errors data integration
VERBOSE: Dec 31 2014 1:55:07:607PM |- 0 rows inserted...
VERBOSE: Dec 31 2014 1:55:07:607PM - Finished data integration for ALL
categories
PS C:\users\public\Documents> .\EPM_EnterpriseEvaluation_41.ps1 -ConfigurationGr
oup "" -PolicyCategoryFilter "" -EvalMode Check
Coordinator
Dec 31, 2014 at 9:47 PM
Does your service account that is running SQL Agent have NTFS permission to that file location?

Sent from my Windows Phone

Jan 6, 2015 at 9:15 PM
Double checked and made sure. We also commented out the lines that delete the temp XML files and nothing is being left behind as we expected would be.