List Database Sequences for Enterprise Geodatabase

3291
8
07-28-2020 08:10 AM
JoeBorgione
MVP Emeritus

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

That should just about do it....
Tags (1)
8 Replies
KoryKramer
Esri Community Moderator

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

0 Kudos
JoeBorgione
MVP Emeritus

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!

That should just about do it....
Raj-Chavada
New Contributor III

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
MVP Emeritus

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

That should just about do it....
0 Kudos
IanAlexander
New Contributor II

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

0 Kudos
JoeBorgione
MVP Emeritus

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.

That should just about do it....
0 Kudos
IanAlexander
New Contributor II

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")

 

0 Kudos
JoeBorgione
MVP Emeritus

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.

That should just about do it....