ArcMap – How to Set Up an ArcMap Relate with a Table from MS SQL Server 2012

arcmaprelatessql server

This is my first time working with data directly from MS SQL Server 2012, and I'm unsure whether I can use it in a Relate in ArcMap.

I have a table in MS SQL Server, which I brought into ArcMap via the Database Connections option. When I add this table to ArcMap using the standard Add Data button, a New Query Layer dialog appears:

enter image description here

ArcMap requires a unique identifier field, however this table doesn't have one. I chose one of the existing fields, even though it's not unique (the first problem?).

I can now view the table in ArcMap, and I'm able to set up a Relate between this table and a featureclass, based on the matching BoreholeID field.

However, when I select a borehole from the featureclass, or a row in the table, the Relate is displayed but greyed out:

enter image description here

  • Can I use a table from MS SQL Server Management Studio in an ArcMap
    relate?
  • If so, is the cause of the problem the lack of a unique ID
    field?
  • If not, what else could be causing the problem?

Best Answer

ArcGIS makes frequent use of "registered rowid columns". These are

  • INTEGER (32-bit) columns with
  • non-zero positive values (1 - 2^31-1),
  • which are defined to be NOT NULL,
  • contain unique values, and
  • are reproducible on subsequent queries

ArcGIS uses these columns to maintain the relationship between rows in tables and the graphics in the map. Whenever you register a table with an enterprise geodatabase, ArcGIS will create an SDE-set rowid column if one is not previously available.

Query Layers are not required to be registered with geodatabase, but they are required to have a rowid column with the same characteristics. Many folks try to cheat the rules by using a row number computed column in place of a rowid, but this will only create what the documentation refers to as "inconsistent behavior" when attempting to map spatial objects to table rows (and vice versa, as in an identity operation).

You'll need to add a rowid column to this table to use it in a query layer.

Related Question