[GIS] Creating a multiversioned view with ArcSDE and SQL Server 2008 R2

enterprise-geodatabasespatial-databasesql serverversioning

I am using the administration command sdetable -o create_mv_view to create a multiversioned view on a table in SQL Server 2008 R2.

Everything appears to be working correctly with one exception: The SDE_get_view_state() scalar function that was included with ArcSDE will only return data for version with name set to 'DEFAULT' and owner set to 'sde'. This is hard-coded in the function.

I have some clients who are using the DBO schema for ArcSDE instead of the SDE schema, and therefore they will not have any version data that meets the criteria.

Does anyone know why the function is checking for the schema owner to be set to 'sde' and more importantly, what is the best way to get this to work for the DEFAULT layer regardless of the owner?

Best Answer

We reported this as a bug in 9.2 a couple of years ago...

Our way around it was to create a multi version view using sdetable, then take the view definition and create a new view in SQL with the calls to SDE_get_view_state() replaced by:

(SELECT state_id FROM sde.sde.SDE_versions WHERE name = 'DEFAULT' AND owner = 'dbo')

It must be replaced at three places in 9.2. (In 8.3 it was 6 occasions). I have not tested this in version 10 yet.

Beware, if you create your own view and change the table definition, your view will not reflect the changes, as it is the sdetable created view that is registred as multiversion view for that particular table.

Related Solutions

[GIS] ArcSDE – Registering table with Geography spatial column fails with error -138 (SQL Server 2008)

The geography data type only supports SRIDs that appear in the view sys.spatial_reference_systems. On my server, the SRID 1047 did not appear in that view so I suspect you are attempting to use a spatial reference that is incompatible with the geography data type.

Use the following query to see what SRIDs are supported:

select * from sys.spatial_reference_systems;

Bill

[GIS] Register SDE view and Layer

What you are looking for is a Spatial-View, now the only major issue you will see here is that you need to be careful with the fields you are joining on. This is a pretty nice process, I use it all over the place where I have generalized my DB to talk with thin spatial data.

    sdetable -o create_view -T emp_region_view -t "employees,world.regions" 
-c "employees.name,employees.emp_id,world.regions.reg_id,world.regions.rname,world.regions.region"
-a "employee,eid,rid,region,area" -w "employees.emp_id = world.regions.emp_id"
-s myodbserver -i sde:oracle11g:ora1 -u gdb -p gdb.bdg

This should get you well on your way to what you want. The key is getting your fields to map correct. I tend to just do the view with the minimum I need, then use a UI to edit that view once registered in SDE to do my more fine-grain tweaks.

Best Answer

Related Solutions

[GIS] ArcSDE – Registering table with Geography spatial column fails with error -138 (SQL Server 2008)

[GIS] Register SDE view and Layer

Related Question