How to access a MS SQL 2008 multiversion view from a remote database

[DionLiddell]

Hi,

I can create a multiversion view and then use 'set_current_version' to change to the version that I require.  And this is all good if I am accessing ArcSDE programmatically or through the interface presented to me by SQL 2008 Manager.  But how can I set up a view that can be accessed via a remote connection such as through a 'linked table' in Microsoft Access?   I need to be able to serve the view in a standard (think ODBC connection) way to the client that will be using the view.

Because the set_current_version stored procedure must be executed before the correct version can be seen in the multiversion view, it appears to be difficult to present the view as an easy-to-use ODBC connection that can be consumed by a remote client.   Any help on how to get around this problem would be very much appreciated.

Regards,
Dion Liddell.

[DionLiddell]

In answer to my own question, I've created the following 'hack':

1. I created my own copy of the 'set_current_version' stored procedure.  My 'force_set_current_version' stored procedure removes the owner and protected checks that exist in the original.

2. Grant the Public role the Execute permission on the 'force_set_current_version' stored procedure.

3. The 'force_set_current_version' is then put into a logon trigger so that it is called whenever someone connects to the ArcSDE database.   I specify the version to use in the logon trigger.

4. Grant my users the Select permission on the multiversion views.