How do I use a sequence ID from a SQL Server view as a primary key in ArcMap?

2358
5
05-24-2018 09:42 AM
TimLangner
Occasional Contributor III

Hi there

I created a view in SQL Server and unfortunately the only unique differences between the rows is an ID number in combination with the geometry [polygon]. Unfortunately there is nothing else that can be used to make them unique.

In order to make them unique I then created a row id. I use the following in my view statement:

select ROW_NUMBER() OVER (ORDER BY LPI_KEY ASC) AS Primary_Key,

That is working fine in SQL Server but when I open the table in ArcMap 10.6, the Primary_Key field is not displaying in the fields to select to make it unique.

If there anything I can do, as I can't use the geometry field in combination with the LPI_KEY field and nothing else is unique enough.

I cannot add in any additional columns to the underlying tables as they come are used by another application, which generated them in the first place.

Would what I'm trying to achieve work in ArcGIS Pro 2.1?

I'm only wishing to view the data and take exports of it to use in ArcMap 10 [yes that application from 8 years is still having to be used where I work].

This may be the wrong forum to post this as it's mainly referring to ArcMap but I couldn't find an ArcGIS Desktop forum or an ArcMap forum.

0 Kudos
5 Replies
ThomasColson
MVP Frequent Contributor

Try adding an index on that view. Create Indexed Views | Microsoft Docs 

With luck, you can create it on that select as statement. I had the same (but not exact) problem when creating views so we could look at stuff in Access DB, needed a primary key, indexed views saved the day. If I remember, you need to create the view with Schema Binding, which should be possible if you're not throwing crazy left joins and unions in your view definition. 

0 Kudos
TimLangner
Occasional Contributor III

I wasn't sure about using Schema Binding as the underlying tables are used by another application which generated them and I didn't wish to do anything that impacted on that. I'm read only viewing the data.

However my view has 5 left joins so I guess it rules it out. Those are needed in order for rows that don't match to still be returned. If I didn't a lot of my data wouldn't show up as required.

I don't have any problems in SQL Server with this, just ArcMap.

0 Kudos
TimLangner
Occasional Contributor III

When making sure the table only has unique LIP_KEY values and making that the primary key for ArcMap I get the below error:

Error reading OID from table. Reading rows has been stopped. Check that the datasource is valid.

The database in question is running as a 2008 compatible SQL Server database but it held on a later version of SQL Server. I forget which though.

The full results are returned in SQL Server with no errors.

0 Kudos
ThomasColson
MVP Frequent Contributor

you don't need to schema bind the underlying tables, just the view. You "could" do it with 5 joins, won't know till you find out. You could also pull a view from the view, add your select as to it, schema bind and index it. Your geometry error indicates some error condition in how you defined the joins. SQL will return the results correctly, because all that's doing is showing the contents of the geometry column(s?) in SSMS, which is Well Known Text. When you attempt to render the results of the geometry column in a client application like Arc, that's where the invalid error will show up if you've violated a condition. Start deconstructing your join, start with two tables and march thru the indexed view process till you find the table that is causing the problem. I would suggest you post your query syntax here, but on SQL Spatial, I've had more luck with Stack Overflow. If you do go to SO, make sure your post is very detailed, and you clarify the result is "An Indexed Spatial View". You'll get flamed if you're not concisely clear, rough crowd there. Keep us posted! I've done this with two tables in complicated joins, both spatial or one spatial one not. You might have to use CTE's to clean up the spatial column problem. One though, are your 5 tables all spatial? All same geom? (Point line or poly)? All same SRID? All same indexing strategy on the spatial column? Does the query execution plan show anything unusual? 

0 Kudos
TimLangner
Occasional Contributor III

Thanks Thomas

Useful help, I don't have time to continue this further at the moment but I do hope to come back to it.

Not all of my tables are spatial. Only one is.

0 Kudos