nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Using a Python Script to Reconcile and post

LarryAdgate

We are a large Water Utility Company with 38 Enterprise Geodatabases and most of them employ a versioning setup. As A workflow, I am considering the use of a Python Script to automate the Reconcile and posting process. Can someone share with me there experience using this kind of workflow and is there a down side.

Encontre mais publicações marcadas com

Accepted answers

MitchHolley1

A good place to start: Reconcile Versions—Data Management toolbox | ArcGIS Desktop

All comments

CCWeedcontrol

Great info, thanks for sharing Cort!

CortWilson

Hi Larry!

You will need to install the pyodbc python module for the portion of the error handling that writes to a SQL log table. pyodbc basically allows you to access your ODBC database through Python. If you don't care about this portion of the script you can remove the import statement for pyodbc and all other referenced code.

Home · mkleehammer/pyodbc Wiki · GitHub

Are you wondering how to build the logging table in SQL Server? or something else?

LarryAdgate

Hi Cort, I apologize for my late response but I'm just now getting around at looking at your code and I have a question. When running your script, I'm getting an error message that the pyodbc module does not exist. I'm also a little confused about how the sql file structure log works. Your thoughts please

CortWilson

Rebecca Strauch, GISP‌ & Larry Adgate‌

There are a few variables at the beginning that you will need to tailor to your environment:

editDB = (your database connection)

sqlsvrname = (name of your sql server instance)

sqldbname = (name of your sql server db within your instance)

There are several other functions and cursors that will need to have my parameters replaced with your own. It should be pretty clear where those are though.

__author__ <SPAN class="operator token">=</SPAN> <SPAN class="string token">'wilson'</SPAN>
<SPAN class="comment token"># //////////////////////////////////////////////////////////////////////////////////////////////////////</SPAN>
<SPAN class="comment token"># Script Title: Rec_Post_GEONET</SPAN>
<SPAN class="comment token"><SPAN># Created by: Cort Wilson, </SPAN><A class="jive-link-email-small" href="mailto:dcortwilson@gmail.com" rel="nofollow noopener noreferrer" target="_blank">dcortwilson@gmail.com</A></SPAN>
<SPAN class="comment token"># Created Date: 6/1/17</SPAN>
<SPAN class="comment token"># Description:&nbsp; Blocks connections, disconnects users, reconcile/post versions, delete versions, unregisters tables and feature classes as versioned,</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; compresses database, registers tables and features classes as versioned, creates versions,</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rebuild indexes, analyze datasets, set the database to allow connections.</SPAN>
<SPAN class="comment token">#</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; pyodbc is used to log captured errors or success message into a sql table.</SPAN>
<SPAN class="comment token">#</SPAN>
<SPAN class="comment token">#</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ***VERSION TREE DIAGRAM***</SPAN>
<SPAN class="comment token">#</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DEFAULT</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QAQC</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; / | \</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; /&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; \</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; /&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp; MICHAEL&nbsp;&nbsp; COLLECTOR&nbsp;&nbsp; KAREN</SPAN>
<SPAN class="comment token">#</SPAN>
<SPAN class="comment token">#</SPAN>
<SPAN class="comment token">#</SPAN>
<SPAN class="comment token">#</SPAN>
<SPAN class="comment token"># Requirements</SPAN>
<SPAN class="comment token"># pyodbc for SQL connection and cursor to SQL log table</SPAN>
<SPAN class="comment token">#</SPAN>
<SPAN class="comment token">#//////////////////////////////////////////////////////////////////////////////////////////////////////</SPAN>


<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">import</SPAN> sys
<SPAN class="keyword token">import</SPAN> os
<SPAN class="keyword token">import</SPAN> time
<SPAN class="keyword token">import</SPAN> pyodbc
<SPAN class="keyword token">import</SPAN> traceback


<SPAN class="comment token"># \\\\Constant Vars\\\\</SPAN>
<SPAN class="comment token"># Database Connection</SPAN>
editDB <SPAN class="operator token">=</SPAN><SPAN class="string token">"Database Connections/DBO@CityworksGIS.sde"</SPAN>
<SPAN class="comment token"># Current Day</SPAN>
Day <SPAN class="operator token">=</SPAN> time<SPAN class="punctuation token">.</SPAN>strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"%m-%d-%Y"</SPAN><SPAN class="punctuation token">,</SPAN> time<SPAN class="punctuation token">.</SPAN>localtime<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># Current Time</SPAN>
Time <SPAN class="operator token">=</SPAN> time<SPAN class="punctuation token">.</SPAN>strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"%I:%M:%S %p"</SPAN><SPAN class="punctuation token">,</SPAN> time<SPAN class="punctuation token">.</SPAN>localtime<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>


<SPAN class="comment token"># \\\\pyodbc.connect vars\\\\</SPAN>
sqlsvrname <SPAN class="operator token">=</SPAN> <SPAN class="string token">'gis'</SPAN>
sqldbname <SPAN class="operator token">=</SPAN> <SPAN class="string token">'CityworksGIS'</SPAN>


<SPAN class="comment token"># Set workspace</SPAN>
workspace <SPAN class="operator token">=</SPAN> editDB

<SPAN class="comment token"># Set the workspace environment</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> workspace
arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>overwriteOutput <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN>



<SPAN class="keyword token">try</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># Start Time</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">'Process Started at '</SPAN> <SPAN class="operator token">+</SPAN> str<SPAN class="punctuation token">(</SPAN>Day<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">" "</SPAN> <SPAN class="operator token">+</SPAN> str<SPAN class="punctuation token">(</SPAN>Time<SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># block new connections to the working and prod database.</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"Blocking Connections..."</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>AcceptConnections<SPAN class="punctuation token">(</SPAN>editDB<SPAN class="punctuation token">,</SPAN> <SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># disconnect all users from the working and prod database.</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"Disconnecting Users..."</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>DisconnectUser<SPAN class="punctuation token">(</SPAN>editDB<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"ALL"</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># Get a list of all versions besides QAQC and DEFAULT to pass into the ReconcileVersions tool.</SPAN>
&nbsp;&nbsp;&nbsp; ver1List <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>ver1<SPAN class="punctuation token">.</SPAN>name <SPAN class="keyword token">for</SPAN> ver1 <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>ListVersions<SPAN class="punctuation token">(</SPAN>editDB<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">if</SPAN> ver1<SPAN class="punctuation token">.</SPAN>name <SPAN class="operator token">!=</SPAN> <SPAN class="string token">'DBO.QAQC'</SPAN> <SPAN class="operator token">and</SPAN> ver1<SPAN class="punctuation token">.</SPAN>name <SPAN class="operator token">!=</SPAN> <SPAN class="string token">'dbo.DEFAULT'</SPAN><SPAN class="punctuation token">]</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># Execute the ReconcileVersions tool with QAQC Target Version then delete all versions besides QAQC</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"Reconcile/post versions to QAQC...."</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>ReconcileVersions_management<SPAN class="punctuation token">(</SPAN>editDB<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"ALL_VERSIONS"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"DBO.QAQC"</SPAN><SPAN class="punctuation token">,</SPAN> ver1List<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"LOCK_ACQUIRED"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"NO_ABORT"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"BY_OBJECT"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"FAVOR_TARGET_VERSION"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"POST"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"DELETE_VERSION"</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># Extract QAQC version from the list of versions to pass to ReconcileVersions tool.</SPAN>
&nbsp;&nbsp;&nbsp; ver2List <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>ver2<SPAN class="punctuation token">.</SPAN>name <SPAN class="keyword token">for</SPAN> ver2 <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>ListVersions<SPAN class="punctuation token">(</SPAN>editDB<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">if</SPAN> ver2<SPAN class="punctuation token">.</SPAN>name <SPAN class="operator token">==</SPAN> <SPAN class="string token">'DBO.QAQC'</SPAN><SPAN class="punctuation token">]</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># Execute the ReconcileVersions tool with DEFAULT Target Version and delete QAQC version</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"Reconcile/post QAQC to DEFAULT..."</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>ReconcileVersions_management<SPAN class="punctuation token">(</SPAN>editDB<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"ALL_VERSIONS"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"dbo.DEFAULT"</SPAN><SPAN class="punctuation token">,</SPAN> ver2List<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"LOCK_ACQUIRED"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"NO_ABORT"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"BY_OBJECT"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"FAVOR_TARGET_VERSION"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"POST"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"DELETE_VERSION"</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"=============================="</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Unregister Tables Started"</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"=============================="</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; **** wildcards for tables I don't want to include ****</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> tbs <SPAN class="keyword token">in</SPAN> <SPAN class="punctuation token">[</SPAN>tb <SPAN class="keyword token">for</SPAN> tb <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListTables<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">if</SPAN> <SPAN class="string token">'_log'</SPAN> <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> tb <SPAN class="operator token">and</SPAN> <SPAN class="string token">'GenerateId'</SPAN> <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> tb <SPAN class="operator token">and</SPAN> <SPAN class="string token">'PYTHON_SCRIPT'</SPAN> <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> tb<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> tbs
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>UnregisterAsVersioned_management<SPAN class="punctuation token">(</SPAN>tbs<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"NO_KEEP_EDIT"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"COMPRESS_DEFAULT"</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># If you want to include all tables in the database use this instead:</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp; for tbs in arcpy.ListTables():</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; print tbs</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; arcpy.UnregisterAsVersioned_management(tbs,"NO_KEEP_EDIT","COMPRESS_DEFAULT")</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"=============================="</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Unregister Feature Classes is started"</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"=============================="</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp; datasets <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListDatasets<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"*"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Feature"</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> dataset <SPAN class="keyword token">in</SPAN> datasets<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; fcList <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListFeatureClasses<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"*"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">""</SPAN><SPAN class="punctuation token">,</SPAN>dataset<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> fc <SPAN class="keyword token">in</SPAN> fcList<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> fc
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>UnregisterAsVersioned_management<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"NO_KEEP_EDIT"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"COMPRESS_DEFAULT"</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># Run the compress tool.</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"Compressing database..."</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>Compress_management<SPAN class="punctuation token">(</SPAN>editDB<SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ script initiation, Rec/Post process error handling \\\\\\\\\\\\\\\\\\\\\\\\\\\\\</SPAN>

<SPAN class="keyword token">except</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">'An error occured'</SPAN>
&nbsp;&nbsp;&nbsp; failMsg <SPAN class="operator token">=</SPAN> <SPAN class="string token">'\nSCRIPT FAILURE IN SCRIPT INITIATION OR RECONCILE-POST PROCESS, \n'</SPAN>
&nbsp;&nbsp;&nbsp; failMsg <SPAN class="operator token">+=</SPAN> <SPAN class="string token">'Most recent GP messages below.\n'</SPAN>
&nbsp;&nbsp;&nbsp; failMsg <SPAN class="operator token">+=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>GetMessages<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN><SPAN class="string token">'\n'</SPAN>
&nbsp;&nbsp;&nbsp; failMsg <SPAN class="operator token">+=</SPAN> <SPAN class="string token">'\nTraceback messages below.\n'</SPAN>
&nbsp;&nbsp;&nbsp; failMsg <SPAN class="operator token">+=</SPAN> traceback<SPAN class="punctuation token">.</SPAN>format_exc<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>splitlines<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> failMsg
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">#write error log info</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">'Logging error to database....'</SPAN>
<SPAN class="comment token"># THIS SECTION FOR LOGGING ERRORS TO A DB TABLE</SPAN>
&nbsp;&nbsp;&nbsp; conn <SPAN class="operator token">=</SPAN> pyodbc<SPAN class="punctuation token">.</SPAN>connect<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Trusted_Connection=yes'</SPAN><SPAN class="punctuation token">,</SPAN> driver<SPAN class="operator token">=</SPAN><SPAN class="string token">'{SQL Server}'</SPAN><SPAN class="punctuation token">,</SPAN> server<SPAN class="operator token">=</SPAN>sqlsvrname<SPAN class="punctuation token">,</SPAN> database<SPAN class="operator token">=</SPAN>sqldbname<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; cursor <SPAN class="operator token">=</SPAN> conn<SPAN class="punctuation token">.</SPAN>cursor<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># You will need to create a table in sql to log to and add fields that you want to log into and pass them as params to this cursor</SPAN>
&nbsp;&nbsp;&nbsp; cursor<SPAN class="punctuation token">.</SPAN>execute<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"""INSERT INTO [dbo].[PYTHON_SCRIPT_TRACKER] (ScriptName,RunDate,Status,ErrorCode) VALUES ('ReconcilePost_AssetIDs_QAQC',GetDate(),'Failure',(?)); """</SPAN><SPAN class="punctuation token">,</SPAN>failMsg<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; conn<SPAN class="punctuation token">.</SPAN>commit<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; conn<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; sys<SPAN class="punctuation token">.</SPAN>exit<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># ////////////////////////////////////// REGISTER AS VERSIONED AND RE-CREATE VERSIONS /////////////////////////////////</SPAN>

<SPAN class="keyword token">try</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"=============================="</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">"register tables is started"</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"=============================="</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; **** wildcards for tables I don't want to version ****</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> tbs <SPAN class="keyword token">in</SPAN> <SPAN class="punctuation token">[</SPAN>tb <SPAN class="keyword token">for</SPAN> tb <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListTables<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">if</SPAN> <SPAN class="string token">'_log'</SPAN> <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> tb <SPAN class="operator token">and</SPAN> <SPAN class="string token">'GenerateId'</SPAN> <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> tb <SPAN class="operator token">and</SPAN> <SPAN class="string token">'PYTHON_SCRIPT'</SPAN> <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> tb<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> tbs
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>RegisterAsVersioned_management<SPAN class="punctuation token">(</SPAN>tbs<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"NO_EDITS_TO_BASE"</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># If you want to include all tables in the database use this:</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp; for tbs in arcpy.ListTables():</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; print tbs</SPAN>
<SPAN class="comment token">#&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; arcpy.RegisterAsVersioned_management(tbs, "NO_EDITS_TO_BASE")</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"=============================="</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">"register Feature Class is started"</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"=============================="</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp; datasets <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListDatasets<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"*"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Feature"</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> dataset <SPAN class="keyword token">in</SPAN> datasets<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; fcList <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListFeatureClasses<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"*"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">""</SPAN><SPAN class="punctuation token">,</SPAN>dataset<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> fc <SPAN class="keyword token">in</SPAN> fcList<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> fc
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>RegisterAsVersioned_management<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"NO_EDITS_TO_BASE"</SPAN><SPAN class="punctuation token">)</SPAN>


&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># create versions</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"Creating Versions..."</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>CreateVersion_management<SPAN class="punctuation token">(</SPAN>editDB<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'dbo.DEFAULT'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'QAQC'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'PRIVATE'</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>CreateVersion_management<SPAN class="punctuation token">(</SPAN>editDB<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'DBO.QAQC'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'MICHAEL'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'PUBLIC'</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>CreateVersion_management<SPAN class="punctuation token">(</SPAN>editDB<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'DBO.QAQC'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'KAREN'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'PUBLIC'</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>CreateVersion_management<SPAN class="punctuation token">(</SPAN>editDB<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'DBO.QAQC'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'COLLECTOR'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'PUBLIC'</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># /////////////////////////////////// ANALYZE DATASETS AND CALC STATISTICS /////////////////////////////////////</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># NOTE: Rebuild indexes can accept a Python list of datasets.</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># Get a list of all the datasets the user has access to.</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># First, get all the stand alone tables, feature classes and rasters.</SPAN>
&nbsp;&nbsp;&nbsp; dataList <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListTables<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListFeatureClasses<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListRasters<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># Next, for feature datasets get all of the datasets and featureclasses</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># from the list and add them to the master list.</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> dataset <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListDatasets<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"*"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Feature"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> os<SPAN class="punctuation token">.</SPAN>path<SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>workspace<SPAN class="punctuation token">,</SPAN> dataset<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dataList <SPAN class="operator token">+=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListFeatureClasses<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListDatasets<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># reset the workspace</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> workspace

&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># Concatenate all datasets into a list</SPAN>
&nbsp;&nbsp;&nbsp; datasetList <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>ds <SPAN class="keyword token">for</SPAN> ds <SPAN class="keyword token">in</SPAN> dataList<SPAN class="punctuation token">]</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"rebuilding indexes"</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># Execute rebuild indexes</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># Note: to use the "SYSTEM" option the workspace user must be an administrator.</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>RebuildIndexes_management<SPAN class="punctuation token">(</SPAN>workspace<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"SYSTEM"</SPAN><SPAN class="punctuation token">,</SPAN> datasetList<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"ALL"</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Rebuild Complete'</SPAN><SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"analyzing datasets"</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>AnalyzeDatasets_management<SPAN class="punctuation token">(</SPAN>workspace<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"SYSTEM"</SPAN><SPAN class="punctuation token">,</SPAN> datasetList<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"ANALYZE_BASE"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"ANALYZE_DELTA"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"ANALYZE_ARCHIVE"</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"analysis complete"</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="comment token">#Allow the database to begin accepting connections again</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"Set databases to allow connections..."</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>AcceptConnections<SPAN class="punctuation token">(</SPAN>editDB<SPAN class="punctuation token">,</SPAN> <SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ If Script Successful, log into database \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\</SPAN>

&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">'Data has been synched succesfully and logged in the tracking database'</SPAN>

&nbsp;&nbsp;&nbsp; conn <SPAN class="operator token">=</SPAN> pyodbc<SPAN class="punctuation token">.</SPAN>connect<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Trusted_Connection=yes'</SPAN><SPAN class="punctuation token">,</SPAN> driver<SPAN class="operator token">=</SPAN><SPAN class="string token">'{SQL Server}'</SPAN><SPAN class="punctuation token">,</SPAN> server<SPAN class="operator token">=</SPAN>sqlsvrname<SPAN class="punctuation token">,</SPAN> database<SPAN class="operator token">=</SPAN>sqldbname<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; cursor <SPAN class="operator token">=</SPAN> conn<SPAN class="punctuation token">.</SPAN>cursor<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># You will need to create a table in sql to log to and add fields that you want to log into and pass them as params to this cursor</SPAN>
&nbsp;&nbsp;&nbsp; cursor<SPAN class="punctuation token">.</SPAN>execute<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"""INSERT INTO [dbo].[PYTHON_SCRIPT_TRACKER] (ScriptName,RunDate,Status,ErrorCode) VALUES ('ReconcilePost_AssetIDs_QAQC',GetDate(),'Success','All Steps Completed Without Error'); """</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; conn<SPAN class="punctuation token">.</SPAN>commit<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">del</SPAN> cursor
&nbsp;&nbsp;&nbsp; conn<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">'Script is finished'</SPAN>


<SPAN class="comment token"># /////////////////////////////////Register as Versioned Error handling area////////////////////////////////////////////</SPAN>

<SPAN class="keyword token">except</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">'An error occured'</SPAN>
&nbsp;&nbsp;&nbsp; failMsg <SPAN class="operator token">=</SPAN> <SPAN class="string token">'\nSCRIPT FAILURE IN VERSIONING PROCESS\n'</SPAN>
&nbsp;&nbsp;&nbsp; failMsg <SPAN class="operator token">+=</SPAN> <SPAN class="string token">'Most recent GP messages below.\n'</SPAN>
&nbsp;&nbsp;&nbsp; failMsg <SPAN class="operator token">+=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>GetMessages<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN><SPAN class="string token">'\n'</SPAN>
&nbsp;&nbsp;&nbsp; failMsg <SPAN class="operator token">+=</SPAN> <SPAN class="string token">'\nTraceback messages below.\n'</SPAN>
&nbsp;&nbsp;&nbsp; failMsg <SPAN class="operator token">+=</SPAN> traceback<SPAN class="punctuation token">.</SPAN>format_exc<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>splitlines<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> failMsg
&nbsp;&nbsp;&nbsp; <SPAN class="comment token">#write error log info</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">'Logging error to database....'</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># THIS SECTION FOR LOGGING ERRORS TO A DB TABLE</SPAN>
&nbsp;&nbsp;&nbsp; conn <SPAN class="operator token">=</SPAN> pyodbc<SPAN class="punctuation token">.</SPAN>connect<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Trusted_Connection=yes'</SPAN><SPAN class="punctuation token">,</SPAN> driver<SPAN class="operator token">=</SPAN><SPAN class="string token">'{SQL Server}'</SPAN><SPAN class="punctuation token">,</SPAN> server<SPAN class="operator token">=</SPAN>sqlsvrname<SPAN class="punctuation token">,</SPAN> database<SPAN class="operator token">=</SPAN>sqldbname<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; cursor <SPAN class="operator token">=</SPAN> conn<SPAN class="punctuation token">.</SPAN>cursor<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="comment token"># You will need to create a table in sql to log to and add fields that you want to log into and pass them as params to this cursor</SPAN>
&nbsp;&nbsp;&nbsp; cursor<SPAN class="punctuation token">.</SPAN>execute<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"""INSERT INTO [dbo].[PYTHON_SCRIPT_TRACKER] (ScriptName,RunDate,Status,ErrorCode) VALUES ('ReconcilePost_AssetIDs_QAQC',GetDate(),'Failure',(?)); """</SPAN><SPAN class="punctuation token">,</SPAN>failMsg<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; conn<SPAN class="punctuation token">.</SPAN>commit<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; conn<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; sys<SPAN class="punctuation token">.</SPAN>exit<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Feel free to let me know if you have any questions or find room for improvement!

My email is under the author portion of the script if you need it.

RebeccaStrauch__GISP

Probably as a .zip or a .txt file. You probably can't do it as a response to an email or from the geonet inbox, so you will want to open the thread in a new brower tab. I'm not seeing the "attach" button right now either (hmmm), but if the code is not too long, use the ... At the top and the more but to pull up the syntax highlighter. Select the type and paste. A better explaination of this is https://community.esri.com/people/curtvprice/blog/2014/09/25/posting-code-blocks-in-the-new-geonet?sr=search&searchId=972c3550-f984-4493-9b0b-628d734d41b7&searchIndex=9‌

edit: you could also post as a new document (which allows you to show versions if/when you update) or in a blog post in you space. Then include the link here so others can find it.

CortWilson

LarryAdgate‌ & rastrauch‌

What's the best way to share this script? Can't seem to figure out how to attach to this post....

I could email it if that works?

CortWilson

LarryAdgate‌ & rastrauch‌

Sure! Give me a bit to strip out any sensitive information from the script and add in some more descriptive comments.

If you don't hear from me within a few days give me a shout.

LarryAdgate

I also echo Rebecca's request,

Larry

RebeccaStrauch__GISP

dcortwilsonpud Do you have any sample code that you can share?

LarryAdgate

Thank You Cort for your comments,

Larry

CortWilson

A little late to your post, but figured I'd put in my $0.02. Hopefully you've found a solution that will work for you!

We are a small-ish sized water utility: 27,000 service connections on over 600 miles of water main. I am using python to automate the reconcile-post process. It works well for us because there are only 3 of us editing at one time and rarely are there any conflicts. I perform QAQC edits in the DEFAULT version and favor all conflicts towards that version, and that's good enough for me. If the automated conflict resolution is a concern, you can set a parameter in the script process to abort if any conflicts are found, review the conflicts, then re-run the script. You might want to re-search the implications of aborting the process when a conflict is found. One problem of course is that usually you would want to run the script after-hours or on the weekends and now that possibly leaves you attached to reviewing conflicts after hours.

I built in error handling throughout the script and have it write to a SQL Server logging table the specific error that was found or if the script was run successfully. A SQL Server trigger on the logging table or function in python to send an email after an error is raised would take it to the next level; I have yet to do that....

In general, I have found automating the process to be a big help. There are many errors that can be introduced when doing this process manually, and hard-coding in all the steps in the right order has reduced a lot of headaches for me.

LarryAdgate

Hi Blake, Thank You for your comments,

Larry

blake.terhune

If you're automatically posting the versions, keep in mind that there is no opportunity to review conflicts and that someone's unfinished edits might get posted and interpreted as final.

MitchHolley1

A good place to start: Reconcile Versions—Data Management toolbox | ArcGIS Desktop

RebeccaStrauch__GISP

Tagging a few groups that may have more experiences with Water Utilities specifically ArcGIS for Water Utilities‌ Utilities and Communications‌

i am not in a water utility group, but do use Enterprise Geodatabases with versioning and topology. Our data is not in any logical network or tightly structured system.that is, our boundaries are typically based on drainages or sometimes seemingly random political boundaries. I mention that since, if a mistake was made but it didn't violate and topology rules or cause a conflict, if it was an automated process, we may not catch it. Luckily we only have two editors (me being the 2nd, and only on occasion), so manually doing a R&P it not a problem. ...and the control freak part of me likes to know what is being changed. With that said, I think there are many that probably do automate the process (and probably have many more editors).

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.