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

[roemhildtg]

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?

[JoshuaBixby]

Versioned data by chance?

[roemhildtg]

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. 

[JoshuaBixby · Accepted solution]

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]

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍