Problems with SQL View in 10.1

[ErikEngstrom]

I have a SQL View that simply selects a few fields from a database table for a join to a feature class in another. The created view exists in the same database as the feature class, so it could be published to a feature service.

This works fine in ArcGIS 10.0, but when I try to return results from the view in ArcGIS 10.1, I'm not seeing any rows. Why might this be? This is my SQL statement. Nothing fancy:

USE [GDB_ANR_DDEV]
GO

/****** Object:  View [ANR_Admin].[ANRL_UVAInspectionsDue]    Script Date: 9/12/2013 7:26:31 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [ANR_Admin].[ANRL_UVAInspectionsDue] AS 


SELECT a.PSPAN, yearoflastinspection as [Year Of Last Inspection]
FROM [ANR_Admin].[CADASTRAL_UVAEDIT_POLY] as a

INNER JOIN lands.dbo.[anrvw_InspectionsDue] as b
ON a.PSPAN = b.SPAN

[VinceAngelo]

So, you're using SQL-Server?  Which release?  Are you using ArcSDE, or
is this question about Query Layers?

I'm somewhat mystified by your assertion that this would work at 10.0, since
a rowid column (NOT NULL integer which returns unique values) has always
been a requirement, in addition to the geometry, and I don't see either in
your column selection list.

- V

[ErikEngstrom]

Yes, I'm using SQL Server.
Simply put, in database a, I have a SQL view in SQL 2012 that retrieves a few fields from a table in database b. It's a table view, not spatial, therefore contains no geometry and thus I thought did not require a rowid. Perhaps I'm wrong here... I can see the rows in SQL Management studio, the view is valid.

What I want to do is join this view in database a, to a feature class in database a.

The problem-
I can see the records of the view in database a using ArcGIS 10.0 (ArcGIS Desktop/ArcCatalog). I can not see these records in ArcGIS 10.2 (anywhere).

Ok, so maybe that wasn't so simply put 😛

[VinceAngelo]

What command did you use to create the spatial view?  How did you register it (or is it a Query Layer)?
What is the geometry storage type of the source layer?  Are you using 10.1 or 10.2 in the instance
where it doesn't work?

Generally speaking, making views on views is unwise -- it's better to reference the original tables.

- V

[ErikEngstrom]

It's just a simple table view, it was created in SQL Server using the CREATE command in management studio.
Again, this is not a spatial view, it is NOT a view of spatial data. It pulls fields from another table in another database.

All I want to do is join a polygon featureclass to this view that sits in my Enterprise Database enabled SQL 2012 database, however I can't see any rows of this view in ArcGIS 10.1. I can see them in 10.0.

[RileyCotter1]

Did you ever figure out what was wrong? I am having a very similar issue. Using SQL Server 2012 and arc 10.2.

I have created several table views in SQL management studio which I am trying to join to a feature class. However, when I look at these tables in ArcCatalog, the record count is *0. I know that *2000 means there are more records than what is showing. I also can see that when I hover over the '*0' it says 'scroll down to see more records'. The problem is that I don't see any records, in ArcMap and Catalog. I know that these views work because I can see the data in SQL management studio. Any Ideas?

[MikeWilson1]

Perhaps this has been solved on a different thread by now, but I hit this while I was searching yesterday. The problem in my case, and likely in the example at the top of the thread, is the aliasing of the fields in the view. Spaces, symbols and reserved words cannot be used to alias the fields. In the example above, spaces are used. In my case, I was using symbols, and also had one field aliased as [Percent], a reserved word. I ran a test with spaces, and it failed, too. Once I changed the symbols to underscores and the reserved word to [Pct], it worked just fine.

The funny thing is that even though no rows are returned in the table view in ArcMap, the columns are aliased as specified in the view, so it is not readily apparent that the alias is the problem. This is still a problem at 10.3.

[Anonymous User]

 You just helped me fix a problem I have been working on all week. I had spaces in my field aliases and it was causing the exact same issues you described. I could see the column names but no data. 

[JamesLandwehr]

I too am having issues with ArcSDE 10.2.1. I create a spatial view at command line, then add columns and I get 15900 records in SSMS. When I look at it in ArcCatalog, I get the 1725 records (One per point). Why is it not showing the related records as it does in SSMS? The table has all the correct privileges and is in the same DB. Also, the points are Multipoint, but when I exploded them to PointM, I still get the same results.

Thanks.

[VinceAngelo]

This issue is due to lack of 1-to-Many support in Desktop.  All views which will be
accessed by ArcGIS must have a unique registered rowid column (this is
how clicking in the table is able to flash the geometry, and vice versa).  If the
table query returns non-unique values, undefined behavior results.

This 1:M restriction also applies to joins as well.

- V