Calling sde.next_rowid with ArcSDESQLExecute

Question

Hi all, I am having trouble updating one of my scripts to work with SQL Server 2016. The problem lies when I try to call the sde.next_rowid procedure using the arcpy.ArcSDESQLExecute method. I am using the code that is described in How To: Insert geometry from XY coordinates using SQL and it works just fine in SSMS. However, when it try in it my python, I get the following message. I believe the problem likes with the SQL but it could be with https://community.esri.com/community/developers/gis-developers/python?sr=search&searchId=94336a51-e622-4a11-98a0-c617c9df218e&searchIndex=0‌. Anyone see anything I am missing? Thanks, Kevin Error Message: ArcSDESQLExecute: StreamPrepareSQL ArcSDE Extended error 11514 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The metadata could not be determined because statement 'EXECUTE sp_executesql @sql, N'@newid INTEGER OUTPUT', @newid = @rowid OUTPUT' in procedure 'next_rowid' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set. Python #Snippet of code with issue. import arcpy strSDE = 'C:\WorkDoc\Dev\ArchiveRestore\ArchiveRestore\G02TLSNR_VECTOR.sde' conn = arcpy.ArcSDESQLExecute(strSDE) SQL = '''DECLARE @RowCount int ''' SQL += '''SET @RowCount = (SELECT COUNT(*) FROM GISTEST.VECTOR.ELEMBND_old WHERE GDB_TO_DATE > CONVERT(datetime,'2017-06-22 11:37:33', 20)) ''' SQL += '''DECLARE @iterator INT ''' SQL += '''SELECT @iterator = MIN(OBJECTID_1) FROM GISTEST.VECTOR.ELEMBND_OLD WHERE GDB_TO_DATE > CONVERT(datetime,'2017-06-22 11:37:33', 20) ''' SQL += '''WHILE @iterator is NOT NULL ''' SQL += '''BEGIN ''' SQL += '''DECLARE @newid int ''' SQL += '''DECLARE @gid uniqueidentifier ''' SQL += '''EXEC GISTEST.sde.next_rowid 'VECTOR', 'ELEMBND_H', @newid OUTPUT ''' SQL += '''EXEC GISTEST.sde.next_globalid @gid OUTPUT ''' SQL += '''INSERT INTO GISTEST.VECTOR.ELEMBND_H (GDB_ARCHIVE_OID, GDB_TO_DATE, GLOBALID, OBJECTID, NAME, NAME_CODE, SCHLNUM, CREATED_USER, CREATED_DATE, LAST_EDITED_USER, LAST_EDITED_DATE, GDB_FROM_DATE, SHAPE) ''' SQL += '''SELECT @newid, CONVERT(datetime, '2017-06-22 11:37:33',20), @gid, OBJECTID, NAME, NAME_CODE, SCHLNUM, CREATED_USER, CREATED_DATE, LAST_EDITED_USER, LAST_EDITED_DATE, GDB_FROM_DATE, SHAPE ''' SQL += '''FROM GISTEST.VECTOR.ELEMBND_old ''' SQL += '''WHERE @iterator = OBJECTID_1; ''' SQL += '''SELECT @iterator = MIN(OBJECTID_1) FROM GISTEST.VECTOR.ELEMBND_OLD WHERE GDB_TO_DATE > CONVERT(datetime,'2017-06-22 11:37:33', 20) AND @iterator < OBJECTID_1; ''' SQL += '''END ''' try: sqlResult = conn.execute(SQL) except Exception as err: print err sqlResult = False if sqlResult: print "SQL insert command 2 successful" else: print "SQL insert command 2 failed" #Clean up del conn‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ SQL DECLARE @RowCount int SET @RowCount = (SELECT COUNT(*) FROM GISTEST.VECTOR.ELEMBND_old WHERE GDB_TO_DATE > CONVERT(datetime,'2017-06-22 11:37:33', 20)) DECLARE @iterator INT SELECT @iterator = MIN(OBJECTID_1) FROM GISTEST.VECTOR.ELEMBND_OLD WHERE GDB_TO_DATE > CONVERT(datetime,'2017-06-22 11:37:33', 20) WHILE @iterator is NOT NULL BEGIN DECLARE @oid int DECLARE @gid uniqueidentifier EXEC GISTEST.sde.next_rowid 'VECTOR', 'ELEMBND_H', @oid OUTPUT EXEC GISTEST.sde.next_globalid @gid OUTPUT INSERT INTO GISTEST.VECTOR.ELEMBND_H (GDB_ARCHIVE_OID, GDB_TO_DATE, GLOBALID, OBJECTID, NAME, NAME_CODE, SCHLNUM, CREATED_USER, CREATED_DATE, LAST_EDITED_USER, LAST_EDITED_DATE, GDB_FROM_DATE, SHAPE) SELECT @oid, CONVERT(datetime, '2017-06-22 11:37:33',20), @gid, OBJECTID, NAME, NAME_CODE, SCHLNUM, CREATED_USER, CREATED_DATE, LAST_EDITED_USER, LAST_EDITED_DATE, GDB_FROM_DATE, SHAPE FROM GISTEST.VECTOR.ELEMBND_old WHERE @iterator = OBJECTID_1; SELECT @iterator = MIN(OBJECTID_1) FROM GISTEST.VECTOR.ELEMBND_OLD WHERE GDB_TO_DATE > CONVERT(datetime,'2017-06-22 11:37:33', 20) AND @iterator < OBJECTID_1; END select * from GISTEST.VECTOR.ELEMBND_H‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

MetralAlexis · Answer

I did some searches and figure out how to fix. I added ' WITH RESULT SETS NONE' to the SQL passed to ArcSDESQLExecute : EXECUTE My_SP 'arg1', 'arg2' WITH RESULT SETS NONE

Here is a good post about how to play with the WITH RESULT SETS clause.

MetralAlexis · Answer

Hi Kevin,

Were you able to fix that issue ? I've got exactly the same problem. My code runs fine in SSMS but not with ArcSDESQLExecute which returns the 11514 errors.

Besides, I did some tests with the WITH RESULT SETS clause in SSMS and I get the error 11536 mentioning that the statement only sent 0 result set(s) at run time ...

Thanks.

Alex.