5

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?

Improve this question

4 Answers 4

Reset to default
4

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.

Improve this answer
3

Not sure, but this doc seems to imply this might work:

exec sde.set_current_version 'dbo.Default'

The docs for 10.0 look different.

Improve this answer
1
  • Thanks Kirk. The issue I'm having with running that statement is that I want to be able to access the view in ArcMap, just as I would with a non-spatial view. The view can run the select statement, but I wouldn't be able to put the EXEC statement in the view script. In that case, the view should be returning the data from the DEFAULT version, however in practice, it is looking for a version with name 'DEFAULT' and owner 'SDE'.
    – Britt Wescott
    Feb 2, 2011 at 2:29
2

What version of SDE? This was a bug that was supposedly fixed at 10 (9.4 aka 10).

Improve this answer
1
1

Thanks to everyone for your ideas. Ultimately, I decided to modify the SDE_get_view_state() function to remove the WHERE clause that checks the name of the owner. Now it is working for both SDE and DBO schemas.

I checked with Craig Gillgrass of the ESRI Geodatabase team and he said this was an acceptable solution.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.