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

DionLiddell — Mon, 30 Aug 2010 09:15:00 GMT

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.

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

DionLiddell — Thu, 02 Sep 2010 01:04:34 GMT

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.

topic Re: How to access a MS SQL 2008 multiversion view from a remote database in Data Management Questions

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

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