Spatial view - ArcCatalog-OID Error
I am finding I get this error too.
I am able to view and manipulate the data in ArcMap 10.3 but I am finding that it causes problems when I am trying to automate an arcpy.CopyRows_management script.
More info on my problem: arcgis desktop - Loading Data Error from MSSQL View? - Geographic Information Systems Stack Exchange
I have the extended error message:
Loading table data: There was an error initializing the table window. If the table does not have an oid field, ensure that a tempory file can be created in the TEMP directory, so the table can be copied locally. Each grid size must be at least three times larger than the preceding grid size. The workspace is not connected. Item not found in this collection.
I'll start off saying I'm not sure how big of an issue it is to create a spatial view and not have a unique identifier, but it seems like something that might cause issues down the road so I want to avoid it.
Ok I figured this out!
(EDIT -- NEVER MIND!! The query layer (as illustrated below) works in ArcMap, however, you can't publish a "complex" query layer (e.g. one that has joins or a where clause). You CAN publish a "simple" query layer (no joins, no where clause). But in my case I needed those joins and where clauses and I need to publish it so this doesn't help. Back to spatial views with no unique identifier!)
Instead I created a Query Layer in ArcMap. I'm using 10.3.1, and followed these steps:
1. In ArcMap, Click File > Add Data > Add Query Layer.
2. It will ask you to provide the database connection. (see image below)
3. Type in a name for your new layer.
4. Create a query. I used the same SQL that I would have used to create the spatial view (which I copied from SQL server management studio).
This gives you a new layer in your MXD that does exactly what your spatial view would have done, plus it adds an "ESRI_OID" field that has a true unique identifier.
You can save and publish the MXD as usual. You can even save the layer as a .lyr file to easily use in other documents.
So the downside is that you don't have a spatial view in your geodatabase to easily use, but since you can save .lyr files you don't have to rewrite the SQL every time you use the same data -- or even similar data, as you can edit the query in ArcMap whenever you need!
Ok so we finally got this sorted out. You can add an ObjectID field when you create the spatial view definition!
add something along the lines of:
cast(ROW_NUMBER() OVER (ORDER BY {FieldOfChoice}) AS int) AS OBJECTID
