ERROR

Duplicate key in Shape column

Last Published: April 26, 2020

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

Article ID:000009472

Software:
  • Legacy Products
  • ArcMap 9 x

Receive notifications and find solutions for new or common issues

Get summarized answers and video solutions from our new AI chatbot.

Download the Esri Support App

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options