Error registering feature class with non binary shape field type

[PedroAbreu]

Hi all,

Back in the good old 10.0 I was creating a spatial view between one Table and two Feature Classes defined as follow:
sdetable
-o create_view
-T "spatialView_ROUTES_EVENT"
-t "PODS.DBO.GEOM_LINEAR_EVENT,PODS.DBO.EVENT_RANGE,PODS.DBO.GEOM_ROUTES"
-c "PODS.DBO.GEOM_LINEAR_EVENT.SHAPE,PODS.DBO.GEOM_LINEAR_EVENT.OID,PODS.DBO.EVENT_RANGE.FEATURE_ID,PODS.DBO.GEOM_ROUTES.STATUS,PODS.DBO.EVENT_RANGE.EVENT_GUID"
-w "PODS.DBO.GEOM_LINEAR_EVENT.EVENT_GUID=PODS.DBO.EVENT_RANGE.EVENT_GUID AND PODS.DBO.GEOM_LINEAR_EVENT.GEOM_ROUTES_OID=PODS.DBO.GEOM_ROUTES.OID"
-i sde:sqlserver:[instance]
-s [server]
-D PODS
-u [user]
-p [pass]

Now that I am migrating the project to 10.1 the view is registered but it just doesn't work. ArcMap dosn't recognize it as a spatial view.
After some research i come across with this bit of information:
http://resources.arcgis.com/en/help/main/10.1/index.html#/Creating_a_database_view_in_ArcGIS_for_Des...
"You cannot create a spatial view if the spatial column is a binary geometry data type"

I went back and checked the way I was registering the Feature Class and I realize that yes, I was registering the Shape field as a binary type:
sdelayer -o add -l dbo.GEOM_ROUTES,SHAPE -t SDEBINARY -e nl+M -C OID,SDE -g GRID,5700 -x 0,0,200 -z 0,200 -G 30731 -P HIGH -L off -i sde:sqlserver:[instance] -s [server] -D PODS -u [user] -p [pass]

So, I thought, let's change the Shape field type to something else that SQL Server 2012 can recognize:
1st attempt:
sdelayer -o add -l dbo.GEOM_ROUTES,SHAPE -t GEOMETRY -e nl+M -C OID,SDE -g GRID,5700 -x 0,0,200 -z 0,200 -G 30731 -P HIGH -L off -i sde:sqlserver:[instance] -s [server] -D PODS -u [user] -p [pass]
Error: Invalid layer storage type (-196)

2nd attempt:
sdelayer -o add -l dbo.GEOM_ROUTES,SHAPE -t GEOGRAPHY -e nl+M -C OID,SDE -g GRID,5700 -x 0,0,200 -z 0,200 -G 30731 -P HIGH -L off -i sde:sqlserver:[instance] -s [server] -D PODS -u [user] -p [pass]
Error: Invalid layer storage type (-196)

Can anybody help me out with this one...?

Thanks,
Pedro

[VinceAngelo]

The best way to define a native SQL geometry column is with SQL (CREATE TABLE ...), which you
would then register with ArcSDE via 'sdelayer -o register'). 

If you do use 'sdelayer -o add' for native geometry, you can't specify SDEBINARY-specific options
like grid size.  I'm also not sure if GEOMETRY supports measures, but I know you can't use a geodetic
GEOGRAPHY type with UTM data.  You should use a DBTUNE keyword that corresponds to the native
type.

- V

[PedroAbreu]

Hi all,

First of all: Thanks for the reply, Vince 😉

Second, here's how I solve the issue:

Create Table 1:
CREATE TABLE [MyFeatClass] (
[OID] int NOT NULL,
[SHAPE] geometry NULL,
[MyTable_Guid] uniqueidentifier NOT NULL
);

Create Table 2:
CREATE TABLE [MyTable] (
[Guid] uniqueidentifier NOT NULL,
[Name] nvarchar(50) NOT NULL,
);
GO

Create View:
CREATE VIEW [MyView]
AS
SELECT
MyFeatClass.OID,
MyFeatClass.SHAPE,
MyTable.Name,
FROM MyFeatClass INNER JOIN
MyTable ON MyFeatClass.MyTable_Guid = MyTable.Guid

Register Table 1 as FeatureClass:
sdetable -o register -t MyFeatClass -c OID -C SDE -u [user] -p [pass] -D [database] -i sde:sqlserver:[instance]
sdelayer -o add -l MyFeatClass,SHAPE -t GEOMETRY -e nl+M -C OID,SDE -g GRID,5700 -x 0,0,200 -z 0,200 -G 30731 -P HIGH -L off -i sde:sqlserver:[instance] -s [server] -D [database] -u [user] -p [pass]

Register View:
sdetable -o create_view -T "MyView" -t "MyFeatClass,MyTable" -c "MyFeatClass.SHAPE,MyFeatClass.OID,MyTable.Name" -w "MyFeatClass.MyTable_Guid=MyTable.Guid" -i sde:sqlserver:[instance] -s [server] -D [database] -u [user] -p [pass]
sdelayer -o add -l MyView,SHAPE -t GEOMETRY -e np3 -C OID,SDE -g GRID,5700 -x 0,0,200 -z 0,200 -G 30731 -P HIGH -L off -i sde:sqlserver:[instance] -s [server] -D [database] -u [user] -p [pass]

[VinceAngelo]

You've got something wrong if you can both create the view with SQL and "register"
it with 'sdetable -o create_view'.  Views are registered exactly the same as native
tables, with 'sdelayer -o register'. The create_view option of 'sdetable' should only
be used with SDEBINARY/SDELOB storage.

- V

[PedroAbreu]

To be honest, I don't know what dark magic runs behind the scene... But it was the only way that I found for ArcCatalog to recognize the Views.
About the SDEBINARY on the initial post, I showed a link to a esri page that claims that views won't work with Binary storage type.... And they weren't working with SDE 10.1 (they were working with SDE 10.0)

[VinceAngelo]

If you need dark magic to make ArcSDE work, you're probably using DBO and/or
schemas named other than the same as the login user.

I doubt support for SDEBINARY has been removed, but haven't had the time
to post-install 10.1 on my laptop to demonstrate it.

- V