arcpy.ListDatabaseSequences() for EGDB

[JoeBorgione]

As of Pro 2.5.1 arcpy.ListDatabaseSequences() only works for fgdb.  I'd like to deploy them on an EGDB and would rather use the arcpy method than a t-sql approach

[JoeBorgione]

I got this from ESRI Tech Support:  

ArcSDESQLExecute—ArcGIS Pro | Documentation

Based on that, I created a python script that looks like thes:

import arcpy

egdb = r'\\path\to\sde_connectionFile.sde'
egdb_conn = arcpy.ArcSDESQLExecute(egdb)

sql = '''
      select *
      from sys.sequences
      '''
egdb_return = egdb_conn.execute(sql)
for i in egdb_return:
    print('{}: {}'.format(*i))‍‍‍‍‍‍‍‍‍‍‍‍

It tosses an error for me:

Traceback (most recent call last):

  File "<ipython-input-1-516d1c09b234>", line 10, in <module>
    egdb_return = egdb_conn.execute(sql)

  File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\arcobjects\arcobjects.py", line 43, in execute
    return convertArcObjectToPythonObject(self._arc_object.Execute(*gp_fixargs(args)))

AttributeError: ArcSDESQLExecute: StreamBindOutputColumn ArcSDE Error -65 Invalid pointer argument to function‍‍‍‍‍‍‍‍‍

I suspect I need the actual sql db name and instance, but I have to get that from the IT-DB admin guy.  I'll see if that fixes it once I do.

[JohannesLindner]

I agree this should be available in arcpy.

In the meantime, this works for SQL Server:

sql = "select name from sys.objects where type_desc='SEQUENCE_OBJECT'"

sql_exe = arcpy.ArcSDESQLExecute(r"path\to\connection.sde")

for s_name in sql_exe.execute(sql):
    print(s_name)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

More stuff to search for:

sys.objects (Transact-SQL) - SQL Server | Microsoft Docs

EDIT:

The above only returns the sequence names. If you want to get things like the current value, you have to use sys.sequences. And some queries on that throw errors in arcpy.

sys.sequences (Transact-SQL) - SQL Server | Microsoft Docs 

fields = ["start_value", "increment", "minimum_value", "maximum_value", 
    "is_cycling", "is_cached", "cache_size", "system_type_id", "user_type_id", 
    "precision", "scale", "current_value", "is_exhausted", "last_used_value"]

for f in fields:
    try:
        result = sql_exe.execute("select {} from sys.sequences".format(f))
        print("{}:\tsucceeded".format(f))
    except:
        print("{}:\tfailed".format(f))
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

# start_value:     failed
# increment:     failed
# minimum_value:     failed
# maximum_value:     failed
# is_cycling:     succeeded
# is_cached:     succeeded
# cache_size:     succeeded
# system_type_id:     succeeded
# user_type_id:     succeeded
# precision:     succeeded
# scale:     succeeded
# current_value:     failed
# is_exhausted:     succeeded
# last_used_value:     failed
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[JoeBorgione]

If it fails on any of the *_value fields, there isn't any value in it for me.  I've tried several different iterations and it fails each time.  I may try something from SSMS, but I don't think the security team is gonna let be do a back door approach like that...

Come on ESRI: help us out here....

[JoeBorgione]

In Sql Server Management Studio it's pretty easy:

select name,start_value,increment,current_value
from YOUR_DATABASE_NAME.sys.sequences‍‍‍‍

I tried using pydobc to connect but there must be a security issue with the particular database;  SSMS is fine but pyodbc won't/can't make a connection.

[RayburnClipper]

Joe is this all related to your post I encountered from Jan-2020. Joe's post

What is your DB platform, I'm curious to know what others are using. I'm using Oracle.

I mentioned in a reply to your Jan Post, that I am just now having issues with my Unique ID auto-incremental column.  I am looking for solutions.

Ray

[Natalie_Runyan]

In SQL Server Management Studio (SSMS), you can find the list of sequences by connecting to the database instance > Database > Programmability > Sequences > and the sequences are listed there.