HOW TO
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.
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;
Code:
exec common_ancestor_sp
Code:
SELECT owner, name FROM ##version_order ORDER BY ca_state_id, state_id
Article ID: 000010646
Get help from ArcGIS experts
Download the Esri Support App