Archive | October 2013

SQL Enterprise Policy Management

I implemented the Enterprise Policy Management a long time ago but ran into some issues that some other people may be having similar issues.

The EPM can be found at:

The problem I had occurred after it had been running for a few weeks and was due to the process timing out.

The actual error is:

The corresponding line is ‘Invoke-Sqlcmd -ServerInstance $CentralManagementServer -Database $HistoryDatabase -Query “exec policy.epm_LoadPolicyHistoryDetail”  -ErrorAction Stop’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.  The statement has been terminated.  ‘

There are two ways to fix this depending on what you want to do — the easiest way is to open up the powershell script and scroll all the way to the bottom and find this line:

Invoke-Sqlcmd -ServerInstance $CentralManagementServer -Database $HistoryDatabase -Query “exec policy.epm_LoadPolicyHistoryDetail”  -ErrorAction Stop

and replace it with:

Invoke-Sqlcmd -ServerInstance $CentralManagementServer -Database $HistoryDatabase -Query “exec policy.epm_LoadPolicyHistoryDetail”  -ErrorAction Stop -querytimeout 0

If that doesn’t work you may need to put a real value in for querytimeout.  I believe this may be the case for servers before SQL 2012.

The other method is to remove that line from the powershell script [or just comment it out with the #] and add a new step to your SQL agent job that has a target database of your warehouse and use:  exec policy.epm_LoadPolicyHistoryDetail.

Voila, you are back in business!

Questions?  Comments?  Leave a note!