topic Re: VersionedVIew update in Geodatabase Questions

VersionedVIew update

vijaybadugu — Wed, 07 Aug 2024 18:34:29 GMT

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?

Re: VersionedVIew update

MarlonAmaya — Wed, 07 Aug 2024 18:50:13 GMT

@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

Re: VersionedVIew update

MarceloMarques — Wed, 07 Aug 2024 19:03:32 GMT

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

Re: VersionedVIew update

vijaybadugu — Wed, 07 Aug 2024 19:56:00 GMT

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:

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

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

Re: VersionedVIew update

vijaybadugu — Wed, 07 Aug 2024 20:07:55 GMT

Thanks a lot.

Re: VersionedVIew update

MarceloMarques — Wed, 07 Aug 2024 20:33:24 GMT

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

Re: VersionedVIew update

vijaybadugu — Wed, 07 Aug 2024 21:45:15 GMT

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

Re: VersionedVIew update

MarceloMarques — Wed, 07 Aug 2024 21:53:44 GMT

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.

Re: VersionedVIew update

vijaybadugu — Wed, 07 Aug 2024 22:00:23 GMT

I am getting this error

Re: VersionedVIew update

MarceloMarques — Wed, 07 Aug 2024 22:07:57 GMT

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.

Re: VersionedVIew update

vijaybadugu — Thu, 08 Aug 2024 12:47:28 GMT

I did copy to the temp folder ,/close/open pro. i am getting the error

Re: VersionedVIew update

vijaybadugu — Thu, 08 Aug 2024 13:00:01 GMT

I tried with another database. after enabling Registered as versioned, it ran without any issues for the first time. after that, it starts throwing same error. does view register anywhere in database ? like GDB_ITEMS

Re: VersionedVIew update

vijaybadugu — Thu, 08 Aug 2024 13:30:33 GMT

It worked. The problem is, we have to drop the view from SQL Server and run the tool to regenerate. At the same time, the name is updated in registry table.

is there a way to rename existing one and regenerate again ?

CreatingVersionedView, does it only creates a view in sql server ? After creating a view, can we rename and update manually in registrytable with an update statement?

Re: VersionedVIew update

MarceloMarques — Thu, 08 Aug 2024 15:31:46 GMT

You cannot register multi versioned views with the Geodatabase Repository. The multi versioned views are created by ArcGIS software to work exclusively with Traditional Versions. We use the multi versioned views to select and edit data via SQL statements in the Traditional Versions. You shall not rename multi versioned views, if becomes invalid, then drop the view and try to create it again. Also, you shall not try to edit the view information directly in the ArcSDE Repository Tables or you risk corrupting the Geodatabase. Note, multi version views are different from regular database views that you can register with the geodatabase, for more info about database views see the ArcGIS documentation. I hope this clarifies.

Views in an enterprise geodatabase—ArcGIS Pro | Documentation

Re: VersionedVIew update

vijaybadugu — Thu, 08 Aug 2024 18:05:41 GMT

Thanks. There is an extra argument to pass name of the view to the CreateVersionedView method. Firstly, i will drop the view from SQL Server and re create using python script that you shared.

Re: VersionedVIew update

vijaybadugu — Thu, 08 Aug 2024 20:04:30 GMT

I successfully created a versioned views. I am trying to add a trigger on A' table , using update statement of versioned view, I am getting below error while editing the data in ArcGIS Pro.

Re: VersionedVIew update

MarceloMarques — Thu, 08 Aug 2024 20:17:42 GMT

this type of changes to the multi version view are not supported, the multi version view code cannot be modified manually because you will break the multi version view functionality and behavior to work with geodatabase traditional versions, you can look into adding a trigger to the featureclass base table or to the Adds Table, but that also can lead into issue when editing the data in traditional versions using ArcGIS Pro, and you will need to test the trigger extensively and make sure no issues happens after geodatabase version reconcile and post. The overall recommendation is to not tamper with the geodatabase and not try to implement logic via triggers, you can corrupt the geodatabase behavior and cause issues with traditional versioning, instead you need to use ArcGIS Pro Attribute Rules.