Latest Contributions by KevinDunlop

Hi, I saw that there is a new patch for data reviewer (patch 6).  However, the download under the link points to patch 5.  Does anyone have the proper download link for it? http://support.esri.com/en/Products/Desktop/data-and-workflows-extensions/arcgis-data-reviewer/10-4-1#downloads?id=7526  ... View more

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ ... View more

So, you been using geodatabase archiving to keep a history of your data's changes.  But you need to make a change to the feature class that requires archiving/versioning to be turned off or worst, you need to change a different feature class in the same data set.  You turn off, archive, make the change, and go to turn it back on.  Well poopy, your old archive history can't be reconnected and it is in a separate feature class.  And there is no tool to restore it. That was the issue I was facing with several of my feature classes.  In addition, with a near future database vendor switch, I was looking at losing all my archive history.  So I wrote a this tool, the Archive Restore, to put the records in the old archive history back into the current archive history.   Both the old archive feature class and parent feature class must resided in the same SDE database under the same data owner. Versioning and archiving on the parent feature class must be enabled for this tool to work. Old archive records with no end (current features at the time archiving was turned off), will receive the end date of when the archive was turned back on.   This tool allows for the addition and removal of fields. In the case of where a field is removed from the parent feature class, the field data from the old archive will not be copied over. In the case of an addition, the new field will be left null. The old archive feature class and records inside it are not deleted during this process.  It must be manually deleted after the tool has finished.  This way you can keep it if you want. This tool was written in python using Arcpy for ArcGIS 10.4.1.  I turned it into a ArcToolbox script so people who have limited python experience can still use it.  It has been tested against Oracle and Postgres.  I hope to test it against SQL Server in the near future.  If you use any other databases, feel free to provide any changes you need to make them work. I strongly suggest testing this on some test data before running on your live production database.  While I have tested in our environment, I can't guarantee that it will work in every environment.  Once the old archive records are added to the new archive, they can't be removed without using SQL on the back end.  Use at your own risk. Please let me know how it worked for you and your data.  I hope it helps. ... View more