Can't see new data in databases in SQL Server

1122
6
Jump to solution
05-24-2021 11:35 AM
LLCCG
by
New Contributor II

Please bear with me, I'm new to both my job and ArcGIS Enterprise.

I have a feature class with some polygons on it. When I right click on the layer in ArcGIS Pro, and select Properties, that window says the Data Source is an Enterprise Geodatabase Feature Class. It gives the server location, the instance (XYZ_Database), the Name (XYZ_Database.DBO.MyLayer), and some other data.

If I open Microsoft SQL Server Management Studio, I can go to Databases>XYZ_Database>Tables>DBO.MyLayer, right click and "Select Top 1000 Rows" and I can see some data. In it, the OBJECTIDs are nicely sequential and ordered. However, I only have 45 rows, but if I open the attribute table in ArcGIS Pro, that layer has 97 rows (in which the OBJECTID is sequential and ordered up to 45, but seemingly random after that). 

If I add a new polygon in that layer, it appears in ArcGIS Pro both on the map and in the attribute table. I can view it on the web map that is tied to the same database. I cannot, however, see it in SQL Server. 

On the other hand, if I go back to MS SQL Server Management Studio, and I open "Views" instead of "Tables", there is a  dbo.MyLayer_evw which has all 97 rows.

If I look at the query that is creating the View, it references some other tables like dbo.SDE_states, dbo.SDE_state_lineages, and some tables that have alphanumeric names in the format of DBO.a123 and DBO.d123. The SDE_states and SDE_state_lineages look like they might be created by ArcGIS, but I don't know what any of them do.

Can anybody shed a bit of light on this for me? Or at least point me in the right direction?

0 Kudos
1 Solution

Accepted Solutions
ReeseFacendini
Esri Contributor

It sounds as if the feature class is registered as versioned, which creates a view of that table in order to have multiple people edit at the same time.  What you are experiencing are the edits that are within what we call the A and D Tables (add and delete) haven't been pushed back to the parent table.  Running the Compress tool (link here) will push those edits back to the main table and the next time you pull up the table in SSMS it will show all 97 records.

View solution in original post

6 Replies
ReeseFacendini
Esri Contributor

It sounds as if the feature class is registered as versioned, which creates a view of that table in order to have multiple people edit at the same time.  What you are experiencing are the edits that are within what we call the A and D Tables (add and delete) haven't been pushed back to the parent table.  Running the Compress tool (link here) will push those edits back to the main table and the next time you pull up the table in SSMS it will show all 97 records.

LLCCG
by
New Contributor II

Ok, cool. I'll give that a shot and see if it fixes it. Thanks!

0 Kudos
ToddMetzler
Occasional Contributor II

Tips to remember:

1.  The construct of SQL Server DB enabled as FGDB is different than a "normal" SQL DB.  Best to interact with EGDB from within the ArcGIS Platform environment As you've discovered, when using another application to access those same data in the EGDB, confusion possible.

Thanks to @ReeseFacendini for the post.  I was initially thinking that, since you accessed the EGDB as DBO in SSMS, that you may have corrupted the EGDB somehow by using SSMS instead of access via ArcGIS apps.

 

0 Kudos
LLCCG
by
New Contributor II

I don't need to change any data via SQL Server, I just need to be able to share it with a non-GIS department. GIS is making the decision as to whether or not a location is serviceable, and marketing needs to be able to see that attribute. Is there a better way to be sharing those data? And is there any risk to just reading--as opposed to editing--data from within SQL Server?

0 Kudos
ReeseFacendini
Esri Contributor

There is no risk to the data by viewing it from the SQL Server tools, instead of ArcGIS Desktop / Pro.  

0 Kudos
ToddMetzler
Occasional Contributor II

Agree with @ReeseFacendini.  No risk to read only access.  We do what you want to do.  Our primary EGDB is set to allow connect/select read access by our knowledge/business workers using whatever method works for them via OS authentication from our AD.

0 Kudos