1

I have a geodatabase with a feature layer of parcels and a scalar database of consumption data for those parcels. Using SQL Server 2012r2 and ArcGIS 10.5.

In ArcCatalog, I open the parcel database connection and create a view which queries the consumption data. The view creation completes without error, but ArcCatalog does not show the view (as a "table") in the connection. Refreshing the connection, disconnecting and reconnecting the DB connection doesn't help.

Yet, if I try to create the view again, I get error "table already exists", and if I open the geodatabase from SQL Server manager, I can see the view and even run it and see expected data.

Why won't ArcCatalog show the view?

I suspect it doesn't enumerate DB schema from the geodatabase that it doesn't have SQL level access to, but connection to the geodatabase has full owner privileges (using SQL rather than Windows authentication).

Once the view is visible in the geodatabase, I plan to use it to define a relationship class with which I hope to be able to visualize consumption by (groups of) parcels, showing numbers and bars on a map, or color-coding parcels by consumption.

closed as unclear what you're asking by PolyGeo May 17 at 4:37

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Did you try it with a Query Layer? – Kirk Kuykendall Apr 30 at 17:24
  • I create views this way regularly (MS SQL 2014 and ArcMap 10.5.1 rather than ArcCatalog), and I've never seen the view not be displayed. It can sometimes be displayed in a different order to what I was expecting which makes it look like it is not being displayed. Is this a possibility? – Son of a Beach May 1 at 5:22
  • @SonofaBeach what method are you referring to when you say 'I create views this way'? – DPSSpatial May 1 at 16:38
  • @DPSpatial - In ArcMap (or ArcCatalog) right-click on the SDE connection file and select ‘New’ -> ‘View’. Then enter the view definition into the dialogue. From the OP’s question, I assume this is what they are doing (but it would be good to have this clarified). – Son of a Beach May 1 at 20:49
0

I don't think a SQL server native view is an officially supported feature type in ArcGIS.

However, we once used them as a back-end data source for ArcGIS Server services.

You should ensure that the view has an OBJECTID column, preferably the first column in the view, and to do this you should use the ROW_NUMBER() function.

I believe that was the main constraint we identified in using SQL Server views with ArcGIS.

Keep in mind anything you do in the back-end of an ArcSDE Geodatabase (or whatever the current branding is) is not necessarily going to be recognized by ArcGIS - especially if you have versioning enabled on the front end.

  • 1
    SQL Server views are a valid feature source. Always have been, even before the onset of Query Layers (you just needed to register them). However, using a row_number() rowid column would make the view invalid for use with ArcGIS. – Vince Apr 30 at 16:13
  • @Vince we never had to register views, and we always had success with OBJECTID built with row_number(). And does 'valid' mean 'supported', or does it 'just kinda work'? – DPSSpatial Apr 30 at 17:05
  • 1
    Invalid as in unreliable and not to be used. You lose binding between the attribute table and the canvas. Imagine what happens with 10 features, with features 6-10 in the view field -- your row_number() shows 1-5; what happens when you flash feature 3? Which is why non-intrinsic attributes are only acceptable in materialized views. – Vince Apr 30 at 17:16
  • If I have built my row_number / objectid correctly, the OBJECTID column has 10 values for 10 records. If I see features 6-10 on the map, why a) would my row number show 1-5, and b) what is 'flashing' feature 3 and why would this matter if its not on the map? The row number isn't changing depending on what is on the map, so the values in that column are indeed bound to the attributes by the nature of the rownumber function. Materialized views are separate from this issue, as the views in question are not 'indexed views' that SQL server supports. – DPSSpatial Apr 30 at 17:40
  • He's not talking about a feature type, just a table, and he's talking about creating them in ArcCatalog. In my experience, creating views in ArcMap/ArcCatalog has always worked reliably, for both simple (table) views and for spatial (feature class) views. – Son of a Beach May 1 at 5:28

Not the answer you're looking for? Browse other questions tagged or ask your own question.