Seeking clarity regarding arcpy.ArcSDESQLExecute() results
Hello everyone,
I've got a confusing enterprise SQL situation that I'm presently stumped on.
I'm using arcpy.ArcSDESQLExecute() within a new custom tool I've built to select each database's (traditional) version state_id value from the SDE_versions table. We've got 16 enterprise databases that I'm comparing the Default version state_id against its child version state_id (for assisting us with identifying versions that our GIS editors may have forgotten to reconcile and post after their QC edit sessions).
For 15 of the 16 SDEs, it's working flawlessly. The state_id values are returned to me as expected, and the Python output matches what I'm seeing within SQL Server Management Studio table view.
However, I have one SDE that is returning a massive negative number (in the -17,000 range).
When I saw this, I manually looked at the SQL table directly and saw that the actual state_id values were 47,539!
I thought maybe the negative number indicated an error, so I edited, reconciled, and posted the versions again. I'm still getting a negative value as the result. I've double checked the connection strings to ensure I'm looking at the correct database with proper permissions, and yes everything looks good and consistent like the other 15 SDEs.
The interesting thing is, as the SDE's state_id values increased by 4 in the SQL table (currently 47,543), the Python output showed a decrease of 4 with the negative number (currently -17,993). So the state_id values are changing at matching numeric intervals, just within a different counting dimension somehow. Ha.
This one has me puzzled, and since 15 of 16 are working perfectly, I am beginning to assume a) a sickly database, or b) some sort of Esri bug. It's a new tool I've built though, so I have no previous Pro version to have a frame of reference (currently using Pro 3.2.1)
Before I get in contact with Esri customer support, has anyone ever experienced negative state_id values like this? Here's the function I built that's running the core logic, if it helps.
def func_sql_tables(sde_conn, sde_database_name, def_version, child_version):
schema_owner = def_version.name.split(".")[0]
string_SQL_Query = f"SELECT Owner + '.' + Name, State_ID FROM {sde_database_name}.{schema_owner}.SDE_Versions WHERE Owner + '.' + Name = '{def_version.name}'"
if child_version:
string_SQL_Query = string_SQL_Query + f" OR Owner + '.' + Name = '{child_version.name}'"
print(string_SQL_Query)
connect_2_SQL_Tables = arcpy.ArcSDESQLExecute(sde_conn)
sync_Results_SQL = connect_2_SQL_Tables.execute(string_SQL_Query)
temp_list_for_state_id = []
for each_result in sync_Results_SQL:
print(each_result)
temp_list_for_state_id.append(each_result[1])
if len(set(temp_list_for_state_id)) == 1:
print(sde_database_name + ": All state IDs match!")
else:
print(sde_database_name + ": Uh oh, the state IDs don't match!")
Thanks for any insight!
