Unable to Add Spatial Reference to Database View

[MGAL]

I'm trying to create a 1:M database view of land parcels to owners. My goal is to publish the result as a registered feature layer in Portal. I am running Pro 3.6, accessing data from a SQL database, and publishing to Enterprise 11.5. In short, here was my original workflow. 

1. Run the Create Database Tool. Here's a truncated version of my View Definition:

SELECT 
    t.OBJECTID, 
    t.SHAPE, 
    t.gistractnum,
    t.datasource,
    t.<some other fields...>,
    r.TRACTNUMBER,
    r.<some other fields...>
FROM DBA.Tracts_evw t
LEFT JOIN DBA.TRACTSDB r ON t.gistractnum = r.TRACTNUMBER

2. After this runs, I ran the Register With Geodatabase tool. For the OID & Shape parameters, I used the first two fields from the above query. Geometry type is polygon, and the coordinate system matches the Tracts dataset. 

3. Publish new polygon feature class to Portal as a registered feature layer.

Currently, I am running into two issues (unsure if they're related, so I'm posting the two together)

1. At step 2, the GUI won't allow me to enter a Coordinate system. As you can see below, the spatial reference quickly disappears as soon as I select. GIF of projection parameter issue

I also tried copying the python command & injecting the projection as an arcpy spatial reference object. 

albers = arcpy.SpatialReference(102003)
arcpy.management.RegisterWithGeodatabase(
    in_dataset=r"C:\Users\p\a\t\h.sde\dba.SpatialROWDB",
    in_object_id_field="OBJECTID",
    in_shape_field="SHAPE",
    in_geometry_type="POLYGON",
    in_spatial_reference=albers,
    in_extent=None
)

Successful run, but the newly registered FC does not have a spatial reference. The coordinate system is a custom reference, as I've read in another post that custom projections for views can be problematic. 

2. After registering the view, the data published to Portal without issues. I can see all the data in the web map, and the fields/attributes appear to be correctly joined in the table (despite the fact that the parcels appear at the origin point of my PCS). 

However, when I open the table in Pro, I get the following error "Failed to load data - invalid partition".

Again - unsure if the two are related, but figured I'd include. 

Hoping that OP & contributor @Thomas_Puthusserry & @VenkataKondepati from the linked post above might see this and could potentially provide some insight...

Cheers 

-Mike

[VenkataKondepati · Accepted solution]

Hi @MGAL,

The "Invalid Partition" error might occur because your 1:M join duplicates the OBJECTID, violating the requirement for every row in a registered feature class to have a unique ID.

You can fix this by either generate a truly unique ID in your view using ROW_NUMBER() or publish the data as a Feature Layer and Table linked by a Relationship Class instead of a SQL join.

[MGAL]

@VenkataKondepati this cleared up the partition issue. thanks! 

fwiw, i needed to explicitly cast to INT, as the view tried to use bigint instead, which come to find out, is not supported by my SQL instance. 

SELECT 
    CAST(ROW_NUMBER() OVER (ORDER BY t.OBJECTID) AS INT) AS RowID,
    t.OBJECTID AS TractsOID, 

 I'm just getting done solving the projections issue, and I thought I'd share. 

The core issue was a disconnect between the SQL database engine and the ESRI Geodatabase registry. To fix this, I performed a manual override of the layer's metadata system.

First, we created the view in SSMS and then registered it using geoprocessing tools in Pro (accepting the "Unknown" projection). Next, we ran a direct SQL update on the sde.SDE_layers system table to forcibly change the view's SRID from 0 to 6 (where 6 refers to the ID holding the spatial reference for my shape layer. I found this value by first querying the sde.SDE_layers table...

SELECT layer_id, table_name, srid 
FROM sde.SDE_layers 
WHERE table_name = 'TRACTS';

...where srid returned 6)

Finally, I restarted Pro in order to clear the local cache, which allowed the application to correctly read the updated spatial reference.

Still not sure why the Register with Geodatabase was refusing to let me add a spatial reference in the tool pane. Oh well...

[VenkataKondepati]

Hi @MGAL,

I am glad to know it worked and you are able to fix it.