English

How To: Using SQL in SQL Server, generate a report for the recommended version reconcile order

Summary

Instructions provided describe how to generate a report for the recommended version reconcile order using SQL in SQL Server. The output from the query is the order that versions should be reconciled with the DEFAULT version prior to performing a compress. By reconciling the versions in the order returned by the query, it allows the compress command to move as many rows as necessary from each versioned class delta tables to the base tables.

Using the SQL approach is the same as if using the ArcObjects interface IVersionedWorkspace2::RecommendedReconcileOrder to obtain an enumeration of version names.

Procedure

Creating a global temporary table and a stored procedure as well as executing a SQL statement is required to return the list of versions in the recommended reconcile order.

  1. Create the stored procedure, which in turn creates the global temporary table that stores recommended version order.
    Code:
    -- Report the recommended reconcile order using SQL
    -- Related to : http://support.esri.com/index.cfm?fa=knowledgebase.techarticles.articleShow&d=35735
    -- to run: exec common_ancestor_sp

    if (OBJECT_ID('common_ancestor_sp') is not null)
    drop procedure common_ancestor_sp
    go
    CREATE PROCEDURE common_ancestor_sp
    AS
    -- Check Schema Owner...
    declare @SchemaOwner varchar(32)
    select @SchemaOwner=ss.name
    from sys.objects so
    join sys.schemas ss
    on so.schema_id = ss.schema_id
    where so.name = 'sde_layers'

    if (@SchemaOwner is null)
    begin
    Print 'Sde Schema does not exist in this database!'
    return;
    end

    -- Create temp table to hold version list
    if (OBJECT_ID('tempdb..##version_order') is not null)
    drop table ##version_order

    CREATE TABLE ##version_order
    (
    owner VARCHAR(32),
    name VARCHAR(64),
    state_id int,
    ca_state_id int
    )

    -- Populate the version list in temp table
    Declare @default_id int;
    Declare @default_lin int;
    Declare @source_lin int;
    Declare @common_ancestor_id int;

    Declare @name varchar(32);
    Declare @owner varchar(32);
    Declare @state_id int;
    -- if Schema owner is sde
    if (@SchemaOwner = 'sde')
    begin
    SELECT @default_id=state_id, @default_lin=lineage_name
    FROM sde.sde_states
    WHERE state_id = (SELECT state_id FROM sde.sde_versions WHERE name = 'DEFAULT' AND owner = @SchemaOwner);

    declare ver_list CURSOR
    for
    SELECT name, owner, state_id
    FROM sde.sde_versions
    WHERE name <> 'DEFAULT'
    AND name NOT LIKE 'SYNC_%'
    AND name NOT LIKE 'REP_CO_SYNC_%'
    ORDER BY state_id;

    open ver_list
    fetch ver_list into @name, @owner, @state_id
    while (@@FETCH_STATUS = 0)
    Begin
    SELECT @source_lin=lineage_name FROM sde.sde_states WHERE state_id = @state_id;

    SELECT @common_ancestor_id=MAX(lineage_id) FROM
    (SELECT lineage_id FROM sde.sde_state_lineages WHERE lineage_name = @default_lin AND lineage_id <= @default_id
    INTERSECT
    SELECT lineage_id FROM sde.sde_state_lineages WHERE lineage_name = @source_lin AND lineage_id <= @state_id) _lid;

    IF (@common_ancestor_id < @default_id)
    INSERT INTO ##version_order VALUES (@owner, @name, @state_id, @common_ancestor_id);

    fetch ver_list into @name, @owner, @state_id
    End
    close ver_list
    deallocate ver_list
    end
    -- if Schema Owner is dbo
    else if (@SchemaOwner = 'dbo')
    begin
    SELECT @default_id=state_id, @default_lin=lineage_name
    FROM dbo.sde_states
    WHERE state_id = (SELECT state_id FROM dbo.sde_versions WHERE name = 'DEFAULT' AND owner = @SchemaOwner);

    declare ver_list CURSOR
    for
    SELECT name, owner, state_id
    FROM dbo.sde_versions
    WHERE name <> 'DEFAULT'
    ORDER BY state_id;

    open ver_list
    fetch ver_list into @name, @owner, @state_id
    while (@@FETCH_STATUS = 0)
    Begin
    SELECT @source_lin=lineage_name FROM dbo.sde_states WHERE state_id = @state_id;

    SELECT @common_ancestor_id=MAX(lineage_id) FROM
    (SELECT lineage_id FROM dbo.sde_state_lineages WHERE lineage_name = @default_lin AND lineage_id <= @default_id
    INTERSECT
    SELECT lineage_id FROM dbo.sde_state_lineages WHERE lineage_name = @source_lin AND lineage_id <= @state_id) _lid;

    IF (@common_ancestor_id < @default_id)
    INSERT INTO ##version_order VALUES (@owner, @name, @state_id, @common_ancestor_id);

    fetch ver_list into @name, @owner, @state_id
    End
    close ver_list
    deallocate ver_list
    end
    -- SELECT owner, name FROM ##version_order ORDER BY ca_state_id, state_id;

  2. Execute the stored procedure to generate the recommended reconcile order:
    Code:
    exec common_ancestor_sp

  3. Execute the following SQL statement, which returns the owner and name of each version based on the recommended reconcile order:
    Code:
    SELECT owner, name FROM ##version_order ORDER BY ca_state_id, state_id

    The versions should then be reconciled with ArcGIS against the DEFAULT version. For each version reconciled, more rows will be moved from the delta tables to the base tables.