Reading versioned data in SQL Server using multiversioned views

You can execute SQL SELECT statements against multiversioned views to access versioned data.

Multiversioned views automatically access the DEFAULT version. Before issuing any queries against the view, you must ensure that you will be querying the required version.

To access a specific state of a specific version, execute the ArcSDE set_current_version stored procedure. This procedure validates the supplied version name and sets the corresponding database state internally. It can be executed directly from a SQL client. The syntax for the stored procedure to set the current version is as follows for an sde-schema geodatabase:

EXEC sde.set_current_version '<version_name>'

For dbo-schema geodatabases, the stored procedure is owned by dbo, so the syntax is as follows:

EXEC dbo.set_current_version '<version_name>'

This procedure may be called again to change to other versions as required and can be called each time the workspace is refreshed to return the current state of the versioned table to the calling application.

If you set the version to DEFAULT, the current state is cached and you only see that state of the DEFAULT version. If you do not set the version at all, a dynamic view of the DEFAULT version is returned. Therefore, as you edit, you see the changes in the DEFAULT version.

Caution:

Multiversioned views should not be used to access or modify complex features such as features that participate in geometric networks, topologies, terrains, cadastral fabrics, network datasets, or relationships or that have specific geodatabase behavior. You should use ArcGIS to view and modify these types of features.

Steps:

1. At an MS-DOS command prompt, execute the sdetable utility to create a multiversioned view.

   sdetable –o create_mv_view –T code_mv –t code_enf 
   –i sde:sqlserver:myserver\ssinst –D code –u sarja –p not4u

2. Open a Transact SQL query window and execute the set_current_version stored procedure.

   EXEC sde.set_current_version 'version2'

   Note:

   If you do not set the current version, each time you query you will see the latest state of the DEFAULT version. If you set the current version, the state is cached and your view of the version you specified remains static.

3. Issue a SELECT statement against the multiversioned view to read versioned data from the geodatabase.

   SELECT owner, site_address, region
   FROM code_mv
   WHERE region = 'b'