I am having issues registering an inner join SQL view with a spatial column in ArcSDE. Any help would be much appreciated.
Here is the background info:
ArcSDE 10, SQL Server Enterprise 2008 R2, SQL 'Geometry' Spatial Type, Non-Versioned database, DBO schema, My Windows login is a sysadmin user
Here is the goal:
The view I am trying to register is an inner join of a polygon feature class to a table. The left of the join is the table, which in the database has a ONE_TO_MANY relationship with the feature class, where the feature class is the primary key as ONE and the table is the foreign key as MANY. I need to display in ArcMap the polygon geometries of all the feature class records as joined to the table. Since this is a ONE_TO_MANY relationship, it will result in coincident/duplicate polygon geometries, but that's OK.
Initially I am adding the view to SDE using sdetable -o create_view:
sdetable -o create_view -T Int_Veg_TEST -t "Interaction,Vegetation" -c
"Interaction.ObjectID AS INT_OID, Interaction.INT_UUID, Interaction.EVENT_UUID,
Vegetation.ObjectID AS VEG_OID, Vegetation.VEG_UUID, Vegetation.Shape" -w
"Interaction.EVENT_UUID = Vegetation.VEG_UUID" -i -D
This finishes OK, but the resulting view only displays the count and geometries from the right of the join (feature class) and I need it to display the feature class geometry and count as joined to the left (table). I would love to set the Row_ID when creating the view but that option is not available in -o create_view. When using the sdetable -o describe option, it shows that ArcSDE has pulled the feature class ObjectID field as the Row_ID:
Table Int_Veg_TEST:
Column name Attribute type Null? Length,DPs RowID Column?
-------------------------------------------------------------------------------
INT_OID SE_INT32 NOT NULL 10
INT_UUID SE_UUID NULL 38
EVENT_UUID SE_UUID NULL 38
VEG_OID SE_INT32 NOT NULL 10
VEG_UUID SE_UUID NULL 38
SHAPE SE_SHAPE NULL 0
OBJECTID SE_INT32 NOT NULL 10 SDE Set
From here I am having trouble using command line sdetable or sdelayer to update the Row_ID of an existing spatial view. Anyone know if this is possible using command line?
I can update the Row_ID manually in the sde_table_registry rowid_column, and it works. All polygon geometries for the join are displaying in ArcMap.
2 problems here though:
A: SDE still thinks it is maintaining the Row_ID. And I can't find where in the sde registry to manually update this to USER.
Table Int_Veg_TEST:
Column name Attribute type Null? Length,DPs RowID Column?
INT_OID SE_INT32 NOT NULL 10 SDE Set
INT_UUID SE_UUID NULL 38
EVENT_UUID SE_UUID NULL 38
VEG_UUID SE_UUID NULL 38
VEG_OID SE_INT32 NOT NULL 10
SHAPE SE_SHAPE NULL 0
B. I'd rather not update the SQL tables directly.
I've also tried creating a new view using SQL Server Management Studio and registering it in SDE using sdelayer -o register, but this also does not work:
SQL:
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [Int_Veg_TEST]
WITH SCHEMABINDING
AS
SELECT dbo.INTERACTION.OBJECTID AS INT_OID, dbo.INTERACTION.INT_UUID,
dbo.INTERACTION.EVENT_UUID, dbo.VEGETATION.OBJECTID AS VEG_OID,
dbo.VEGETATION.VEG_UUID, dbo.VEGETATION.SHAPE
FROM dbo.INTERACTION
INNER JOIN dbo.VEGETATION ON dbo.INTERACTION.EVENT_UUID = dbo.VEGETATION.VEG_UUID
GO
CREATE UNIQUE CLUSTERED INDEX Ix_INT_OID_UNIQ_CLUSTERED
ON Int_Veg_TEST (INT_OID)
GO
command line:
sdelayer -o register -l Int_Veg_TEST,Shape -e a -t Geometry -C INT_OID,USER -i -D
Error: Underlying DBMS error (-51).
Error: Cannot Create Layer.
Microsoft SQL Server Native Client 10.0: Cannot alter 'INT_VEG_TEST' because it is not
a table.
More information is available.
Best Answer
This has been resolved.
sde_layer error is a bug in ArcSDE 10.0 service packs 1 & 3. Service pack 2 works fine and ESRI tech support claims 10.1 will work fine too.