List Database Sequences for Enterprise Geodatabase

[JoeBorgione]

Looks like I had this same question back in January of 2020:  https://community.esri.com/thread/245985-listdatabasesequences-for-enterprise-gdb and didn't get any takers then, but I'll toss it up again.

I have created a sequence in an enterprise geodatabase; at least I think I have.  The arcpy.da.ListDatabseSequences() function is limited to file geodatabases. See ListDatabaseSequences—ArcGIS Pro | Documentation  where it states :

This function is only supported for file geodatabases.

How do I verify that I have the sequence created as well as monitor where in the sequence values is?  Is this functionality expected to be expanded to include egdbs anytime soon? (Or is it ideas time for me?)

Kory Kramer‌

Chris Fox‌

[KoryKramer]

Hi Joe.  I looked to see and I can't find any existing enhancement request or idea specifically about listing database sequences from enterprise gdbs using Python.  You mentioned it in your idea here https://community.esri.com/ideas/18428 , but that idea is different.  I can't speak to the underlying reasons that it is only supported for file gdb, but I'd say that if you post an idea that specifically asks for this, at least it gives us something solid to reference rather than chasing different GeoNet threads! 

Thank you!!!

[JoeBorgione]

Thanks Kory    Forgot about that idea; so far I'm the only one that voted it up. I guess I can start another one and vote for it as well.  My guess is ten points is well below the threshold for consideration....

I'm kinda stuck now porting the Address Data Management Solution to the enterprise environment.  

edited to add: Idea posted.  My lonely vote could use some help!

[Raj-Chavada]

If post the idea, please tag me. I will support it. It is somewhat counterproductive why I couldn't use any GP tool to look for existing sequences in an EGDB and use SSMS to do the same. 

[JoeBorgione]

Refer to https://community.esri.com/ideas/18798 I've psoted a work around there from ESRI Tech Support.  

[IanAlexander]

Sorry for the potential misunderstanding.  Where is the link to the work around?

[JoeBorgione]

You'd have to look for my idea for an arcpy tool.  I never got the ESRI tech support work around to work for me, but I was able to look at the various sequences and their states through SQL Server Manager Studio.

[IanAlexander]

Thank you for the advice.

I was able to create a workaround for my purposes.

1. Create database sequence (assume failure means it already exists):

try:
     arcpy.management.CreateDatabaseSequence(r"C:\Users\test\test.sde", "test", 1, 1)
except:
     e = sys.exc_info()[1]
     if "ERROR 002907: Sequence name is already in use by dataset in workspace." in e.args[0]:
           arcpy.AddMessage("Sequence exists")
           pass

2.  Use Arcade expression to access the sequence

exp = "NextSequenceValue('test')"
arcpy.CalculateField_management(the_layer, the_id_field, exp, "Arcade")

[JoeBorgione]

If you want to have the sequence to provide the next value automagically you can write an attribute rule that looks something  like this:

//Ensure the ID is not already set, if it is, //return the original id
if (IsEmpty($feature.AddressID)) {
   return NextSequenceValue("SiteAddressID")
}
else {
   return $feature.AddressID
}

Which for me fires upon creation of new address point, reads the sequence named SiteAddressID and updates the field of choice with the next value.  The cool thing about sequences is the numbers are unique, and are never repeated.  So if you assign a value to a feature and later delete that feature, the value is retired.