Error: Duplicate key in Shape column
Error Message
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.]
Cause
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.
Solution or Workaround
Upgrade to ArcSDE 9.2 Service Pack 4 or later releases, or drop the trigger after the upgrade. The trigger can be dropped with the T-SQL code below.
The code works for single and multiple database models. Ensure that the SQL Server Query Analyzer is pointing to the database with the SDE/DBO schema.
Note: If using a DBO owned ArcSDE schema change the @AdminUser variable below from 'sde' to 'dbo'.
SQL Server 2005:
-- 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
SQL Server 2000:
-- 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
Last Published: 7/22/2016
Article ID: 000009472
Software: Legacy Products