VersionedVIew update

[vijaybadugu]

We've registered datasets as versioned with the traditional type. This generated versioned views with default triggers on them: v[versionnumber]_insert, _update, and _delete. Recently, we updated a few more columns in the existing feature class with GLOBAL_ID. When I try to open those views in the SQL client, the new columns do not appear in the view. What needs to be done to update the view with the latest columns? How can I see those updated columns in the view and triggers?

[MarlonAmaya]

@vijaybadugu ,

How did you add the global ID field? Was it done with our ArcGIS Pro using Fc properties ? (manage --> add global IDs) ?

I did a quick test adding the Global ID using Pro and the version view is updated with new column.

Marlon

[vijaybadugu]

What we did was, after creating the view with _vw (earlier version), one of our administrators renamed the view to a simpler name. That could be one of the reasons. Here are the steps I need to follow:

1. Compress the database.
2. Unregister the dataset (which has topology).
3. Register it again.

I have another question: Will this change all version IDs associated with the previous feature classes?

[MarceloMarques]

if you unregister as traditional version then the edits in the geodatabase versions are lost, before that post and reconcile all versions child of sde.default, then delete the child versions, then execute a geodatabase sde compress multiple times, then gather new statistics, then rebuild indexes, this will bring the geodatabase to state lineage 0, then is safe to unregister as traditional version, then after schema changes are completed, register as traditional version again, create new child versions and resume editing.

Recommended Enterprise Geodatabase Training for Database Administrators
Deploying and Maintaining a Multiuser Geodatabase | Esri Training Instructor-Led Course
Implementing Versioned Workflows in a Multiuser Geodatabase | Esri Training Instructor-Led Course

[MarceloMarques]

@vijaybadugu 

if you make schema changes to the featureclass that is registered as traditional version, e.g. add a new field, then the ArcCatalog and ArcGIS Pro shall have updated the multi versioned view behind the scenes, however I ran into situations that if you had many connections opened at the time you make the schema change then the proper code to update the multi versioned view and evw view triggers might not have executed, hence always when making schema changes you need to close Pro and connect only with the data owner user, make the schema change and close Pro and reopen.

You can unregister as traditional version and register again, this shall recreate the multi versioned view and the evw view triggers.

If you cannot do that because you will lose data in geodatabase versions, then these are the alternatives.

Drop the _EVW view in the database and use ArcCatalog 10.8.2 to Enable SQL Access to recreate the multi versioned view. Enable SQL Access 

For more information about “Enable SQL Access” open the link below
Enable SQL access on geodatabase data from ArcGIS Desktop—ArcMap | Documentation
Remarks: “Enable SQL Access” is only available via the context menu, there is no Geoprocessing Tool in ArcCatalog.

“Enable SQL Access” is not available in Pro, neither via the context menu nor via a Geoprocessing Tool.

Remarks:

Pro GP Tool - Create Versioned View (Data Management)

This is a deprecated tool. This functionality is no longer needed because versioned views are automatically created when data is registered as versioned.

Geodatabase administrators still need this tool; it should not have been removed, there are many customers still working with traditional versioning in Pro.

Although the tool is deprecated, it is still possible to use it via python in Pro.

arcpy.management.CreateVersionedView (see the Appendix A below)

##Appendix A:

NOTE:  if you open in Pro the toolbox gdb_tools_pro.tbx, see zip attached at the bottom, then you will find the "CreateVersionView" GP tool that I created, you can add multiple featureclasses and tables as the input.

#How to execute this tool using Pro:
#open a command window and run
#"C:\Program Files\ArcGIS\Pro\bin\Python\Scripts\propy.bat" CreateVersionedView.py

#Import arcpy module
import arcpy
#Local Variable
fcname = r"C:\temp\gdb_cnn_files\mcsdboralnx7_mcs1_topo1_gis.sde\GIS.Streets"

#Process: Create Versioned View
try:
  print ('create versioned view...')
  arcpy.CreateVersionedView_management(fcname)

except arcpy.ExecuteError:
  #Get the geoprocessing error messages
  msgs = arcpy.GetMessage(0)
  msgs += arcpy.GetMessages(2)
  #Return gp error messages for use with a script tool
  arcpy.AddError(msgs)
  #Print gp error messages for use in Python/PythonWin
  print (msgs)
except:
  #Get the traceback object
  tb = sys.exc_info()[2]
  tbinfo = traceback.format_tb(tb)[0]
  #Concatenate information together concerning the error into a message string
  pymsg = tbinfo + "\n" + str(sys.exc_type)+ ": " + str(sys.exc_value)
  #Return python error messages for use with a script tool
  arcpy.AddError(pymsg)
  #Print Python error messages for use in Python/PythonWin
  print (pymsg)

I hope this clarifies the question.

[vijaybadugu]

Thanks a lot.

[vijaybadugu]

When I try to run python script, I am getting "Table already exists" 

[MarceloMarques]

Use the toolbox from the attached zip above instead, there you will find the "CreateVersionView" GP tool that I created using a python script and the "arcpy.management.CreateVersionedView", you can add multiple featureclasses and tables as the input.

[vijaybadugu]

I am getting this error

[MarceloMarques]

copy the geodatabase connection file to a folder like c:\temp, close/open Pro, connect using the c:\temp gdb connection file and try again, if still get an error you will need to troubleshoot the python script to find out the cause of the error.