nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

DavidJenkins

I am managing a production workflow with about a half-dozen editors in a versioned environment set up against a single ArcSDE instance (SQL Server 2008). Every once in a while something will happen where the editors will begin getting errors on reconcile about duplicate keys. Upon looking into the issue, I discovered that though there are two physical records in the geometry (duplicates) but only one record in the business table and the system will not allow a delete of the object(s). My initial thought was "multi-part feature" so explode, of course the result was "no multipart features found...".

Since it would not let the editor reconcile, I have been able to delete the affected version and recreate it minimizing the losses but it's still a loss...

Here's the thing. I am using Excel to track and report editing progress. I do this by executing sql queries (built and tested in SQL Server) through Excel data connections to read the contents into a worksheet, pivot the results and create graphs and charts. This is nothing new. I've been doing this for years and have never had a problem before. However, I've surmised that the error happens when there are active editing sessions and I run the reports.

I have therefore suspended the use of tracking progress using Excel but now I am faced with reporting progress some other way.

If anyone understands what may be happening here I would appreciate some insight so I can plan a new path for reporting.

Thank you,

Dave Jenkins

タグ付きの投稿をもっと見る

Accepted answers

All comments

nicogis

are you installed available patches for sql server and arcgis?

DavidJenkins

are you installed available patches for sql server and arcgis?

Yes, I'm somewhat a stickler for such things. I try to pushed forward as much as possible with all patches unless there's a problem.

Since my last post I've completely disassociated my reports from the database but we occasionally have the same issue. The editors will be working when all of the sudden one of them will have the error "cannot insert duplicate key" upon reconcile. Then another one will get it and another... and so on. Upon monitoring the issue more closely we're closing in on the fact that the issues seem to occur "around" the same time of day. I'm working with our IT department to see if they're performing any maintenance during these times but it seems unlikely. I'm wondering if it may be related to communication disruptions with the server.

Dave Jenkins

TerryAdams_2

David,

Saw your post.

We are having exactly the same issue and cannot figure out why it is happening. Same thing as you report, every now and again we're getting duplicate primary key errors when GIS officers attempt to reconcile. ESRI support have thus far been stumped as well. We can go for months without issue - then boom, we cannot get rid of the issue no matter what we try.

Did you have any luck at all in figuring this out?

AsrujitSengupta

did "sdegdbrepair -o daignose_table" report any inconsistency?

TerryAdams_2

Unsure of the command used I admit (not done by me), but yes, after the last reconcile / post we have duplicates reported in each of the editors' (3 of) versions when we run a command line check. We do have a command line repair tool ESRI alerted us to which fixes the problem. However, this requires all users to be disconnected from the system which has implications in our environment.

To perhaps make the situation more clear.

Editor 1 reconcile/post with admin version

Editor 2 reconcile/post with admin version

Editor 3 reconcile/post with admin version

Editor 2 reconcile with admin version

Editor 1 reconcile with admin version

This is to get all 3 editors versions the same.

Then....

Admin Reconcile / Post with SDE default.

Admin compress the database.

If editor1, 2 or 3 then tries to reconcile again....... duplicate primary Key is reported. This would - upon some research - appear to be a standard SQL duplicate primary key error.

Now.....

Load in the suspect dataset into arcmap.

open the attribute table.

What you find is that when you select one of the 'duplicated' features,the attribute table will report 2 selected features, I assume because there are two entries in the database using the same primary key. You can also try highlighting one feature from the attribute table and you will also get two features being reported as selected.

Anyone who's had this happen and can share info about it we'd like to hear from you. We are stumped.

BillFox

I'm not sure if this will prevent this issue but we use a weekly maintenance window to compress our protected DEFAULT version to state zero. This requires the editors to reconcile and post before the maintenance kicks off. All versions are deleted that night. Versions needed for applications are recreated with a batch file of sde commands after compression to state zero. The desktop editors create a fresh private version when they begin work the following morning. The same maintenance workflow is required for service packs, upgrades, etc.This has helped maintain the best performance and reduce strange errors like orphaned records.

DavidColey

Hey Dave, I had something like this awhile back before we moved over to sql geometry, only in reverse to what you describe, i.e. more business records than geometry. It was happening because certain editors were copying and pasting records from spreadsheets in a way that was violating the key constraints.

Basically I set up sql scripts to remove the dups and clean any and all dependencies in both directions. I've got some scripts somewhere, I'll see if I can round them up if you don't have this solved-

TerryAdams_2

Hi David. Am interested in what you found about your editors copying data from spreadsheets causing the duplicate primary key errors. This is something we theorized could be happening in our situation, but our errors weren't consistent enough to confirm or disprove. I admit though that I still can't fathom how copying data into attribute fields from spreadsheets could cause database primary key errors. Could you elaborate?

DavidColey

Well, there's not much to tell. Prior to our move to SQL Geometry at 10.1, I found that for certain utilities feature classes like hydrants, there would be from time to time more rows in the business table than in the feature (f) table. I discovered that nugget as I was transforming data into Intergraph format via FME.

I didn't even think it was possible to create or otherwise load rows into a feature class using standard ArcGIS front-end methods and not create geometry, but it happened. So it's not a primary key error per say, it's more of a key-constraint violation I would guess.

What I suspect had happened is that hydrants data, coming in from an xy event table created from a spreadsheet perhaps had inverted x,y's or were perhaps in a differenent coordinate system, or had differenent units. But because we use well-defined x,y domains, resolution and tolerances I think the information was added to the A and F table but upon, reconcile, post and/or compress, the invalid values in the F table were dropped, but the business rows remained. That's my theory.

DavidWendelken

I know this answer is a good bit after the question was asked, but it might help someone else.

Scenario: Attempting to add a new record to a geometry table via the arcgis rest service associated with it.   We get a duplicate primary key id.   The admin log for the arcgis website says the objectid value it's trying to use. We look in the table and there is not only a record in the table with that id, there are more records with objectid values higher than that record.

As far as I can tell, the sde database schema was designed back when DBase II was the bee's knees and it was ported as was to sql server. So, instead of using the built-in increment id feature available in sql server, the existing code was used.   There is a next id table per geometry table and that table was out of whack.

The sql server database has a stored procedure that you can execute if you're in that situation. It will increment the next objectid value in that next id table by 1.    Obviously, if the next id is 100 less than the highest objectid in the table, you'll need to do so 100 times. (Or open up the stored procedure, figure out what it does, and hack the next id table directly, but that's almost certainly an unsupported behavior.)

Here's a script to invoke it:

USE [YourDatabaseName]
GO
DECLARE @return_value int,
                  @rowid int
EXEC @return_value = [dbo].[next_rowid]
            @owner = N'YourSchemaName',
            @table = N'YourTableName',
            @rowid = @rowid OUTPUT

SELECT @rowid AS N'@rowid'
SELECT 'Return Value' = @return_value

That doesn't solve the problem of HOW the next objectid value got out of synch with the data in the table.   There appear to be a host of ways to make that happen.

会話に参加する

サインインしたメンバーは投稿、更新のフォローなどができます。初めてですか？無料アカウントを登録してください。

クイックリンク

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.