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-e6.... 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
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.
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.