PROBLEM
Different results are returned with SQL queries on versioned data
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
- Legacy Products
Get help from ArcGIS experts
Download the Esri Support App
Related Information
Discover more on this topic
Search for related information
Find training related to this topic
Explore ideas and give feedback