I am working in a versioned database (SQL Server 2012, ArcMap 10.4). When I view the table in ArcMap or Catalog I see 17 rows of data. When I create a query layer based on this table, it returns 15 rows of data AND the data that it returns is old data. Specifically, its returning 1 row of data that has been removed, and is not returning 3 new rows of data that have been created. The table with the correct data is the same database and version that I am creating the query layers against.
Correct Data: Connected to the database with transaction version 'Edits'
Incorrect Data and Inputs:
Connected to the same database inwhich the properties of that DB are set to point to the 'Edits' version.
NOTE: I have also done the query so that the table name is appended with the view (uusd.dbo.das_antenna_info_evw) and I get the same results. I have also tried creating the query layer by going to File > Add Data > Add Query Layer
If I go into SMSS and query the table view (table_name_evw) I get the correct records back, but if I query table directly I get the incorrect results - the results that I'm getting back with the query layer
Whats going on here?
When you are using Versioned Data, query the Versioned Views for the latest records.
The Base\Business Tables will not have all the recent edits unless you have perform a Compress. The compress moves the recent edits from the Delta Tables (a- & d- Tables) into the Base table.
When you query a versioned view, you can see the data in the base (business) table and the edits that are stored in the delta tables.
Thanks Asrujit - but I was querying the versioned view. Yet the query layer was still creating a layer based on the base table. I've figured out that if I unregister the table as versioned it works but that's starting to creating some other issues when viewing data on AGOL. I'm still problem solving that part of it.