PROBLEM

Different results are returned with SQL queries on versioned data

Last Published: April 25, 2020

Description

SQL Queries are not returning the same results as ArcGIS Queries, when these queries are run on versioned data.

Cause

Edits to versioned data are written directly to the supporting delta (A&D) tables of the versioned objects. ArcSDE clients, such as ArcMap and ArcCatalog, use the combination of the entries in the business table and the delta tables to return results of queries.

Native SQL is not aware of these delta tables, so DBMS SQL queries query only the business tables of the versioned objects.

Note:
Only if all the edits are fully compressed to the business table will the same results be returned. See Related Information section for instructions on how to compress a versioned database to state 0.

Solution or Workaround

Use multiversioned views in SQL to return the correct results of the SQL queries. Multiversioned views look like database views of the attributes of the versioned objects. However, in addition to the database views, there are stored procedures and triggers that are also used to incorporate the delta table entries when queries are run against the multiversioned views from SQL.

Information on multiversioned views can be found at the following link:

Using multiversioned views.
Note:
The above information also applies when using either 2-Way or Full Model 1-Way replicas.

For Simple Model 1-Way replica, the data in the child replica geodatabase is assumed to be non-versioned; therefore, synchronized changes are placed directly into the business tables.

    Article ID:000009944

    Software:
    • Legacy Products

    Get help from ArcGIS experts

    Contact technical support

    Download the Esri Support App

    Go to download options

    Related Information

    Discover more on this topic