SQL Server record number not matching ArcCatalog

[LisaDygert]

Hello,

I manage a GIS enterprise environment using ArcGIS 10.2.2 where some of the feature classes are versioned databases.  We have discovered that the spatial views (created in SQL Server Management Studio 2008 R2) did not have the same number of records as the SDE feature classes.  Upon closer examination, I found that the same tables viewed in ArcCatalog and SQL Server did not have the same number of records.  One example had 3,867 records in ArcCatalog and only 3,068 records in SQL Server.  We only have the default database, so there is no issue of needing to reconcile to the default version.

It seems as if none of the records that have been added or edited since we recently migrated to a new server are coming through in SQL Server.  This doesn't make any sense since SDE has a SQL back-end.  The versioned data does have the editor tracking enabled, but I don't think that is the problem.  Why isn't SQL Server showing the changes and updates that are occurring in ArcMap, especially if ArcMap and ArcCatalog are recognizing the edits?  Is there something that is set up incorrectly or something I have to do to get SQL Server to match what ArcCatalog has?

They are both reading the same data on the same server.  If I import a new feature class, it does show up in SQL Server.  So, there is some connection there that is working as expected, but the fact that once the data has been loaded, none of the edits are coming through in the SQL Server software application is troubling.

Any help at all is greatly appreciated.

Thanks in advance.

Lisa

[JoshuaBixby]

When you say "in SQL Server," are you querying against the base tables or the associated multiversioned view or versioned view?  You may only have the Default version, but the base tables don't reflect what is in the Default version unless the geodatabase has been compressed.  Since you are using 10.2.2, versioned views should be created automatically when you register the layers as versioned.  Try querying against those views and see if your missing records are there.

[LisaDygert]

I forgot to mention that the database does get compressed every night.  So, that isn't the issue either.  SQL Server Management Studio still doesn't show any edits since the data was loaded into the SDE.

[Pieter_Geertvan_den_Beukel]

can you check the A- and D-tables where the versioning mechanism stores the Adds and Deletes? Although the database does get compressed every night, I suspect these tables still contain the edited data.

[JoshuaBixby]

Simply running compress on a geodatabase doesn't ensure all of the edits get pushed into base tables, unless running compress fully compresses the geodatabase.  Is the VERSIONS table showing the state ID for the Default version as 0?  Also, does the associated versioned view show all the records?

[BugPie]

Once we realized we were querying against the base table and not the versioned table all problems solved. SQL novice learning experience. This was helpful, thank you.

Make sure you query against the table_VW not the original table you loaded/ created.