idea arcpy.ListDatabaseSequences() for EGDB in ArcGIS Pro Ideas

arcpy.ListDatabaseSequences() for EGDB

JoeBorgione — Fri, 13 Jan 2023 17:14:12 GMT

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

Re: arcpy.ListDatabaseSequences() for EGDB

JoeBorgione — Sun, 12 Dec 2021 11:53:00 GMT

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.

Re: arcpy.ListDatabaseSequences() for EGDB

JohannesLindner — Sun, 12 Dec 2021 11:53:03 GMT

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

Re: arcpy.ListDatabaseSequences() for EGDB

JoeBorgione — Mon, 24 Aug 2020 17:14:05 GMT

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

Re: arcpy.ListDatabaseSequences() for EGDB

JoeBorgione — Sun, 12 Dec 2021 11:53:05 GMT

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.

Re: arcpy.ListDatabaseSequences() for EGDB

RayburnClipper — Fri, 18 Dec 2020 16:23:04 GMT

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

Re: arcpy.ListDatabaseSequences() for EGDB

Natalie_Runyan — Thu, 22 Sep 2022 21:54:08 GMT

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.

Re: arcpy.ListDatabaseSequences() for EGDB

philnagel — Fri, 21 Mar 2025 19:47:56 GMT

I figured out what was causing the error being thrown for certain columns in `sys.sequences` - the columns seem to be in some datatype that the ArcPy SQL interface can't handle. I ended up casting them to VARCHAR in my query and then just converting them back to integer later in my script. So this would give you everything you need to create the same sequence again for example:

def get_sequence_info(sde: str) -> list[dict]: cols = ['seq_name', 'seq_start_id', 'seq_inc_value', 'current_value'] sql = 'SELECT name, CAST(start_value AS VARCHAR(100)), CAST(increment AS VARCHAR(100)), CAST(current_value AS VARCHAR(100)) FROM sys.sequences' sql_exe = arcpy.ArcSDESQLExecute(sde) sequences = sql_exe.execute(sql) if sequences is True: return [] else: rules = [] for rule in sequences: rules.append( {i[0]: int(i[1]) if i[1].isdigit() else i[1] for i in zip(cols, rule)} ) return rules