I had python script tool that accessed a sequence value and got the
value via arcpy.ArcSDESQLExecute call. We are porting our SDE environment
from Oracle 11g to SQL Server 2012 and as such changed the sequence select
from ALL_SEQUENCES to sys.sequences as shown:
select_list = 'SELECT DISTINCT current_value ' # SELECT LAST_NUMBER '
from_clause = 'FROM sys.sequences ' # ' FROM ALL_SEQUENCES '
where_clause = 'WHERE NAME = \''+seq_name+'\' ' # 'WHERE SEQUENCE_OWNER = \'' + schema + '\' AND SEQUENCE_NAME
This SQL works in SSMS or SQL_CMD but not in arcpy (it did work correctly in Oracle 11g)
is there now an arcpy call that will get a sequence value (CURRVAL) directly without
building SQL and using ArcSDESQLExecute?
Are you receiving any kind of error from arcpy.ArcSDESQLExecute ? Looking in SQL Server just now, I notice that current_value is type sql_variant. Have you tried casting the value to see if this is a data type mismatch or unsupported data type?
I'm already casting the value:
sde_return = connection.execute(sql_txt)
# If the return value is a list (a list of lists), display
# each list as a row from the table being queried.
seq_val = int(sde_return)
Should it be done earlier?
After doing a bit of testing and looking at your code, I am wondering whether your SQL is generating an error, which is then having sde_return get set to False, and False is getting cast to 0. What if you call arcpy.ArcSDESQLExecute outside of the try statement, what do you get?
For me, I have to cast current_value in SQL in order to avoid generating an error:
>>> sde_file = # path to SDE connection file >>> sde_conn = arcpy.ArcSDESQLExecute(sde_file) >>> # Run SQL without casting >>> sql_nocast = "SELECT current_value FROM sys.sequences WHERE name = 'CountBy1'" >>> sde_conn.execute(sql_nocast) Runtime error Traceback (most recent call last): File "<string>", line 1, in <module> File "c:\program files (x86)\arcgis\desktop10.4\arcpy\arcpy\arcobjects\arcobjects.py", line 27, in execute return convertArcObjectToPythonObject(self._arc_object.Execute(*gp_fixargs(args))) AttributeError: ArcSDESQLExecute: StreamBindOutputColumn ArcSDE Error -65 \ue45c >>> >>> # Run SQL with casting >>> sql_cast = "SELECT CAST(current_value AS INT) FROM sys.sequences WHERE name = 'CountBy1'" >>> sde_conn.execute(sql_cast) 1 >>>