ERROR
Posting a version to the default version results in the following error message:
Error: The version could not be posted. Underlying DBMS error[Microsoft OLE DB Provider for SQL Server: Duplicate key in Shape column.]Saving edits to the default version results in the following error message:
Error: Unable to save edits. Underlying DBMS error[Microsoft OLE DB Provider for SQL Server: Duplicate key in Shape column.]
This bug is specific to ArcSDE SQL Server geodatabases in which feature classes have archiving enabled before applying a service pack.
A trigger is created when archiving is enabled for feature classes after a service pack is applied. The naming convention for the trigger is sp_col_ins_<layer_id>. Layer_id is the value in the layer_id column in the sde_layers table for the history layer.
Both "Duplicate key in Shape column" errors are caused by a trigger on the history table for the feature class or classes being edited.
Note: If using a DBO owned ArcSDE schema change the @AdminUser variable below from 'sde' to 'dbo'.
-- This code drops triggers that are not needed on history tables. -- Need to run this code from the admin databases ... ex: sde database. -- SQL Server 2005 specific declare @dbName varchar(100) declare @owner varchar(100) declare @SelStmt varchar(1000) declare @TrigName varchar(100) Declare @AdminUser varchar(30) Set @AdminUser = 'sde' Set @SelStmt = 'select distinct database_name into ##sde_0_DatabaseName from ' + @AdminUser + '.sde_table_registry tr join ' + @AdminUser + '.sde_archives ar on tr.registration_id = ar.history_regid' Exec (@SelStmt) declare ch cursor for select database_name from ##sde_0_DatabaseName open ch Fetch ch into @dbName while (@@fetch_status=0) begin -- Get a list of the triggers set @SelStmt = 'select tr.owner, st.name into ##sde_1_TriggerName from ' + @dbName + '.sys.objects so join ' + @AdminUser + '.sde_table_registry tr on so.name = tr.table_name join ' + @dbName + '.sys.triggers st on so.object_id = st.parent_id join ' + @AdminUser + '.sde_archives ar on tr.registration_id = ar.history_regid where (st.name like ''sp_col_ins%'')' --print @SelStmt exec (@SelStmt) ----------------- Inner Cursor to drop triggers Declare tr cursor for select * from ##sde_1_TriggerName open tr Fetch tr into @owner,@TrigName while (@@fetch_status=0) Begin set @SelStmt = 'USE ' + @dbname +'; DROP TRIGGER ' + @owner + '.' + @TrigName print @selStmt exec (@SelStmt) Fetch tr into @owner,@TrigName End close tr Deallocate tr ----------------- Fetch ch into @dbName drop table ##sde_1_TriggerName end close ch Deallocate ch drop table ##sde_0_DatabaseName
-- This code drops triggers that are not needed on history tables. -- Need to run this code from the admin databases ... ex: sde database. -- SQL Server 2000 specific declare @dbName varchar(100) declare @owner varchar(100) declare @SelStmt varchar(1000) declare @TrigName varchar(100) Declare @AdminUser varchar(30) Set @AdminUser = 'sde' Set @SelStmt = 'select distinct database_name into ##sde_0_DatabaseName from ' + @AdminUser + '.sde_table_registry tr join ' + @AdminUser + '.sde_archives ar on tr.registration_id = ar.history_regid' Exec (@SelStmt) declare ch cursor for select database_name from ##sde_0_DatabaseName open ch Fetch ch into @dbName while (@@fetch_status=0) begin -- Get a list of the triggers set @SelStmt = 'select tr.owner, st.name into ##sde_1_TriggerName from ' + @dbName + '.dbo.sysobjects so join ' + @AdminUser + '.sde_table_registry tr on so.name = tr.table_name join ' + @dbName + '.dbo.sysobjects st -- representing the triggers table on (so.id = st.parent_obj) and (st.xtype = ''TR'') join ' + @AdminUser + '.sde_archives ar on tr.registration_id = ar.history_regid where (st.name like ''sp_col_ins%'')' --print @SelStmt exec (@SelStmt) ----------------- Inner Cursor to drop triggers Declare tr cursor for select * from ##sde_1_TriggerName open tr Fetch tr into @owner,@TrigName while (@@fetch_status=0) Begin set @SelStmt = 'USE ' + @dbname +'; DROP TRIGGER ' + @owner + '.' + @TrigName print @selStmt exec (@SelStmt) Fetch tr into @owner,@TrigName End close tr Deallocate tr ----------------- Fetch ch into @dbName drop table ##sde_1_TriggerName end close ch Deallocate ch drop table ##sde_0_DatabaseName
Get help from ArcGIS experts
Download the Esri Support App