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!
Solved! Go to Solution.
Update: I'm not seeing anything specific regarding this issue in the Pro 3.3.0 release notes, but our negative number problem resolved itself in Python once we upgraded to 3.3.0.
Providing an update:
My original tests were happening on Windows Server 2022 with Pro 3.2.1.
I was able to find a machine that had Windows Server 2016 with Pro 3.1.3. Its State ID output was matching the actual SQL tables, no negative numbers!
I was also able to find a machine that had Windows Server 2022 with Pro 3.1.3. Its results were also matching the actual SQL tables, no negative numbers....but.....the State ID values were being converted to double and no longer integers. Um, wut.
My testing seems to conclude that there is a bug between Pro 3.1.3 and Pro 3.2.1. I'll be submitting a ticket to Esri and will follow up here if I learn anything relevant about my situation.
Hello AG.
Did you ever get an answer back from ESRI? I just updated from python 2.7 to python 3.0 and I'm getting this negative result back:
2024-06-13 15:51:31 INFO SELECT SDE_ID, SERVER_ID, DIRECT_CONNECT, TO_CHAR(COMPRESS_START,'DD-Mon-YYYY HH24:MI:SS') as COMPRESS_START, START_STATE_COUNT, TO_CHAR(COMPRESS_END,'DD-Mon-YYYY HH24:MI:SS') as COMPRESS_END, END_STATE_COUNT, COMPRESS_STATUS FROM SDE.COMPRESS_LOG WHERE SDE_ID IN (SELECT MAX(SDE_ID) FROM SDE.COMPRESS_LOG)
2024-06-13 15:51:31 INFO SDE_ID, SERVER_ID, DIRECT_CONNECT, COMPRESS_START, START_STATE_COUNT, COMPRESS_END, END_STATE_COUNT, COMPRESS_STATUS
2024-06-13 15:51:31 INFO -6974, 14992, Y, 07-Jun-2024 04:22:52, 1, 07-Jun-2024 04:22:52, 1, SUCCES
In python 2.7 it was returning the correct value of 1040058
I'm working with Oracle and with SQL Developer and if I run the SQL Select statement all is correct.
para = str(parameters[0]) + ", " + str(parameters[1]) + ", " + str(parameters[2]) + ", " + str(parameters[3]) + ", " + str(parameters[4]) + ", " + str(parameters[5]) + ", " + str(parameters[6]) + ", " + str(parameters[7])
AddPrintMessage(str(para),0)
logging.info(str(para))
It seems that in python 1040058 is getting converted to -6974 - I have no idea why.
Any ideas?
Hello AG
Did you ever get an answer back from ESRI or discover the reason for the -17,000 number?
Thanks.
@JamesMorrison1- Unfortunately not yet. I worked with Esri on this for a couple of weeks, but I ended up needing to close the ticket. Our agency is a wee bit behind on modernization, lots of really old enterprise database versions on Enterprise 10.8.1 while trying to use modern versions of Pro and its scripting libraries. Esri had us test things by upgrading our enterprise databases from 10.8.1 to 11.2. That didn't solve it. Esri then advised us to upgrade our Enterprise installation and test again. Ha! Like that's something we can do on a whim. That's the roadblock right now. We can't try that until our Data Admin is ready (hopefully later this year). Esri couldn't rule out a compatability issue between Enterprise 10.8.1 and Pro 3.2, but also couldn't rule out some sort of database corruption in the tables since it's only occuring with one database. The fact that I'm getting different results on different versions of Windows Server is a red flag to me as well. Esri didn't have an answer to that.
I plan to upgrade to Pro 3.3 today or early next week and will test again. Based on my previous research, it does seem like a bug introduced at Pro 3.2, but maybe that's because Esri is inching the database support further and further from our setup. I'm not sure.
Thankfully, the State IDs are working for our needs (as in, the database's versions' State IDs will match when all versions are properly Reconciled and Posted...they'll just show a negative number).
What is the setup like on your side? Database version? Enterprise version? My apologies for not being more helpful with news!
Thanks for the reply AG.
we have 19c and enterprise 11.1 and arcgis Pro 3.1
I have made a separate forum entry describing my issue in a little more detail. Showing my code.
I’m hoping it maybe a Python formatting issue since the data in the SDE tables is correct.
Just to confirm did you actually have any minus numbers in the SDE tables?
Only in Python, the SQL tables were showing the correct (positive) numbers. I suspect you'll have better luck with Esri Support because you are on supported versions of everything and still seeing the behavior. I've tracked down your post and am now following it
Thanks AG.
Update: I'm not seeing anything specific regarding this issue in the Pro 3.3.0 release notes, but our negative number problem resolved itself in Python once we upgraded to 3.3.0.