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

[TerryAdams]

Thanks for your help again Asrujit.

Ok - so the "sdegdrepair -o repair_tables"  command is one we're familiar with, and we've run that just now.  So problem resolved once again for now.  But the underlying cause we're still no closer to solving.  The reconcile and post procedure I detailed above is indeed what we were instructed to do by ESRI support after we opened no less than two tickets with them.  Can you suggest email contact with someone who could assist further?

All the editing is done within ESRI ArcMap ver 10.1.  ArcGIS 10.1 SP1 for Desktop. We use no 3rd party applications to edit our data.

So at this point, if anyone can suggest, or see a problem with our workflow then we'd like to hear it.

[AsrujitSengupta]

Upon taking a closer look at your workflow, why don't you suggest all the 3 editors to delete and recreate their versions after reconcile/post with the Admin version is done.  That is also a recommended workflow to recreate your versions from time to time.

You can automate this as a script as well if needed, to be executed at the desired day/time.

[TerryAdams]

We have gone down the path of recreating versions before, and have actually done that again in this instance as well.  It really needs to be said though, that you shouldn't have to do this.  The issue needs to be resolved instead of using a series of workarounds to temporarily get past these errors.

Could you provide us an example or template of the script you'd use to recreate versions?

[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]

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.