Calling sde.next_rowid with ArcSDESQLExecute

KevinDunlop — Sun, 12 Dec 2021 10:49:25 GMT

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\n'''
SQL += '''SET @RowCount  = (SELECT COUNT(*) FROM GISTEST.VECTOR.ELEMBND_old WHERE GDB_TO_DATE > CONVERT(datetime,'2017-06-22 11:37:33', 20))\n'''
SQL += '''DECLARE @iterator INT\n'''
SQL += '''SELECT @iterator =  MIN(OBJECTID_1) FROM GISTEST.VECTOR.ELEMBND_OLD WHERE GDB_TO_DATE > CONVERT(datetime,'2017-06-22 11:37:33', 20)\n'''
SQL += '''WHILE @iterator is NOT NULL\n'''
SQL += '''BEGIN\n'''
SQL += '''DECLARE @newid int\n'''
SQL += '''DECLARE @gid uniqueidentifier\n'''
SQL += '''EXEC GISTEST.sde.next_rowid 'VECTOR', 'ELEMBND_H', @newid OUTPUT\n'''
SQL += '''EXEC GISTEST.sde.next_globalid @gid OUTPUT\n'''
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)\n'''
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\n'''
SQL += '''FROM GISTEST.VECTOR.ELEMBND_old\n'''
SQL += '''WHERE  @iterator = OBJECTID_1;\n'''
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;\n'''
SQL += '''END\n'''

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

Re: Calling sde.next_rowid with ArcSDESQLExecute

MetralAlexis — Thu, 03 Oct 2019 13:02:07 GMT

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.

Re: Calling sde.next_rowid with ArcSDESQLExecute

MetralAlexis — Thu, 03 Oct 2019 13:46:57 GMT

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.

topic Re: Calling sde.next_rowid with ArcSDESQLExecute in Geodatabase Questions

Calling sde.next_rowid with ArcSDESQLExecute

Re: Calling sde.next_rowid with ArcSDESQLExecute

Re: Calling sde.next_rowid with ArcSDESQLExecute