How can I reset the Internal ID counter for ArcGIS SDE table?

2421
4
Jump to solution
03-17-2017 10:17 AM
roemhildtg
Occasional Contributor III

So I have an sql table that is shared with ArcMap and a 3rd party application. Both applications need to edit/insert rows. The 3rd party application uses the auto sql Identity = ON to use the sql server's method of generating new ID's on insert. ArcGIS on the other hand uses some internal counter method to generate the next id.

What happens is, when the 3rd party app inserts a row, a new ID gets generated, lets say 1000 and the row is inserted. But ArcMap's counter isn't updated, so when ArcMap tries to insert a row using its internal counter, it tries to insert with id=1000 also, and receives a duplicate key error. 

A) Can I reset arcmap's counter to the new value?

B) Is there a better method I should use instead?

0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

You can work with versioned data outside of ArcGIS applications using a versioned view:  What is a versioned view? and An overview of editing versioned data using SQL.

For nonversioned data, have you read Editing nonversioned geodatabase data in SQL Server using SQL?  I think it will answer your question(s).

View solution in original post

4 Replies
JoshuaBixby
MVP Esteemed Contributor

Versioned data by chance?

0 Kudos
roemhildtg
Occasional Contributor III

Nope, in this dataset it is not versioned. I realize that versioned data won't integrate with a third party app so I'm sticking with a plain old table. 

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

You can work with versioned data outside of ArcGIS applications using a versioned view:  What is a versioned view? and An overview of editing versioned data using SQL.

For nonversioned data, have you read Editing nonversioned geodatabase data in SQL Server using SQL?  I think it will answer your question(s).

roemhildtg
Occasional Contributor III

Yes! This is exactly what I was looking for. Thanks!

Editing nonversioned geodatabase data in SQL Server using SQL—Help | ArcGIS Desktop 

DECLARE @id as integer
EXEC dbo.next_rowid 'gisdata4', 'farmland', @id OUTPUT;
SELECT @id "Next ObjectID";

RETURNS:Next ObjectID423



In Python (sqlalchemy) this function works for me: 
def get_next_objectid(session, table, schema='dbo'):
    """
        selects the next valid id from a arc sde table for sqlalchemy
        Does not validate arguments, and is vulnerable to sql injection,
         only use this function with arguments that you TRUST
    """
    sql_string = """
        DECLARE @id as integer
        EXEC dbo.next_rowid '{schema}', '{table}', @id OUTPUT;
        SELECT @id "id";
    """.format(table=table, schema=schema)
    ident = session.execute(sql_string).first()[0]

    return ident

0 Kudos