This project is read-only.
1
Vote

Unable to insert rows on SQL 2016

description

While testing the PowerShell script EPM_EnterpriseEvaluation_412.ps1 (version 4.1.2.2) the data never gets inserted, although the scripts runs fine. I am using SQL Server 2016 against SQL Server 2014 and SQL Server 2016.

EPM Framework v4.1.2
Starting policy category evaluation - 9/26/2016 9:33:30 AM
Loading Assemblies
SQLPS module already loaded
Connecting to the policy store
Get list of servers to evaluate
Starting server loop
Finished server loop
VERBOSE: Sep 26 2016 9:33:44:633AM - Starting data integra
VERBOSE: Sep 26 2016 9:33:44:700AM |- 0 rows inserted...
VERBOSE: Sep 26 2016 9:33:44:700AM - Starting no target da
VERBOSE: Sep 26 2016 9:33:44:737AM |- 0 rows inserted...
VERBOSE: Sep 26 2016 9:33:44:737AM - Starting errors data
VERBOSE: Sep 26 2016 9:33:44:760AM |- 0 rows inserted...
VERBOSE: Sep 26 2016 9:33:44:760AM - Finished data integra
Finished policy category evaluation - 9/26/2016 9:33:44 AM

comments

MJREGO0330 wrote Nov 1, 2016 at 8:27 PM

I have the same problem. I am using SQL Server 2014 Standard Edition as my CMS.

MJREGO0330 wrote Nov 1, 2016 at 9:05 PM

Boraq - If the list of registered instances in your CMS is not under a server group you will not get any output. Run the SQL below to alter the function that returns the list of registered servers. Notice I commented out the code that joins on the Server group:

ALTER FUNCTION [policy].[pfn_ServerGroupInstances] (@server_group_name NVARCHAR(128))
RETURNS TABLE
AS
RETURN(WITH ServerGroups(parent_id, server_group_id, name) AS
    (
        SELECT parent_id, server_group_id, name 
        FROM msdb.dbo.sysmanagement_shared_server_groups tg
        WHERE is_system_object = 0
            AND (tg.name = @server_group_name OR @server_group_name = '')   
        UNION ALL
        SELECT cg.parent_id, cg.server_group_id, cg.name 
        FROM msdb.dbo.sysmanagement_shared_server_groups cg
        INNER JOIN ServerGroups pg ON cg.parent_id = pg.server_group_id
    )
    SELECT s.server_name --, sg.name AS GroupName
    FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] s
    --INNER JOIN ServerGroups SG ON s.server_group_id = sg.server_group_id
)

GO