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