topic Re: Setting Current version in SQL Server in Data Management Questions

Setting Current version in SQL Server

ReubenNwokeforo — Fri, 08 Mar 2013 01:59:53 GMT

Hello,

I am trying to set current version in SQL server using the stored procedure:
EXEC sde.set_current_version 'DBO.residence'
But it doesn't seem to work because when I check my MV view, it seems to be referencing the DEFAULT version still.

Has anyone encountered this problem or know what the solution is?

Thanks,
Reuben

Re: Setting Current version in SQL Server

MuneebAnsari — Fri, 08 Mar 2013 17:14:31 GMT

Hi Reuben,
Do you have a DBO-schema geodatabase? If yes, then the stored procedure is owned by dbo, so try using the following syntax:

EXEC dbo.set_current_version '<version_name>'

Re: Setting Current version in SQL Server

ReubenNwokeforo — Fri, 19 Apr 2013 19:09:53 GMT

Hello muneeb_ansari
Sorry for the late response. I have been away.

The Stored procedure is actually owned by SDE. But I have noticed the the default version is owned by SDE while all other versions are owned by the dbo. Would that be the issue and is there a way around it.

Thanks,
Reuben

Re: Setting Current version in SQL Server

MarcoBoeringa — Fri, 19 Apr 2013 21:22:09 GMT

Hello,

I am trying to set current version in SQL server using the stored procedure:
EXEC sde.set_current_version 'DBO.residence'
But it doesn't seem to work because when I check my MV view, it seems to be referencing the DEFAULT version still.

Has anyone encountered this problem or know what the solution is?
Thanks,
Reuben

I recently ran into similar issues with SQL Server. I have a small test setup and test database using SQL Server Express with one versioned feature dataset with a few feature classes.. My instance is SQL Server 2012 Express SP1 upgraded from SQL Server 2008 R2 Express SP2, using ArcGIS 10.1 SP1. My database is DBO owned, and I can see the set_current_version stored procedure as dbo owned.

I also tried to set the current version of a multiversioned view, but kept getting the DEFAULT (which is still at state 0). I ran the code snippet below (which comes straight from the versioned view code except for replacing "SELECT l.lineage_id" with "SELECT s.state_id"), that returns the state after running the set_current_version command (you can modify it to show lineage_id as well). It consistently returned state 0 after running the set_current_version command, while the version I tried to set has state 12. The set_current_version procedure ran successfully according to the message displayed in the SQL Server Management Studio status window.

Change dbo/sde in the snippet to either dbo or sde depending on who owns it:

SELECT        s.state_id
    FROM            YOURDATABASENAME.dbo/sde.SDE_states s INNER LOOP JOIN
                                YOURDATABASENAME.dbo/sde.SDE_state_lineages l ON l.lineage_name = s.lineage_name
    WHERE        s.state_id = YOURDATABASENAME.dbo/sde.SDE_get_view_state() AND l.lineage_id <= s.state_id

In my case, the version pointing to state 12 is also another user than dbo, who owns the default. The non-dbo owned version I tried to switch too from the default owned by dbo, is set "public". I haven't been able to figure this one out yet looking at things like the contents of the SDE_versions, SDE_states, SDE_states_lineages tables etc.

I must admit I did make an edit through Quantum GIS (QGIS) against the multiversioned view, before actually attempting to set a version through SQL Server Management Studio and the set_current_version command. But this should not necessarily cause issues, the multiversioned views are designed for this purpose. I did notice one issue though where ArcGIS did not recognize the proper extent of the total dataset (it did display the new polygon, but didn't properly zoom out to it). This was solved with another edit in ArcMap itself, which seems to have updated the layer's extent.

QGIS also consistently shows state 0, the same if I look at the "Spatial results" window in SQL Server Management Studio.

Re: Setting Current version in SQL Server

MarcoBoeringa — Mon, 22 Apr 2013 19:25:06 GMT

Hmm... I now found this thread mentioning at least one known relatively recent (2012) issue with the set_current_version stored procedure (or the other functions it calls), although that thread mentions it being related to DEFAULT:

http://forums.arcgis.com/threads/51244-Multi-versioned-Views-Not-seeing-data-in-the-A-amp-D-tables?highlight=set_current_version

Still a real possibility there is an issue with my arcsde or geodatabase system tables, maybe related to the QGIS edit, but I don't see it at this point going through some of these tables (just a few records, since it is a small test database).

Re: Setting Current version in SQL Server

MarcoBoeringa — Mon, 22 Apr 2013 19:50:43 GMT

Don't know how to interpret this yet, but if I insert a:

-- BEGIN EDIT
DECLARE @i int
SET @i = @g_version_id

SET @error_string = 'g_version_id: ' + (SELECT LTRIM(STR(@i,10)))
RAISERROR (@error_string,16,-1)
-- END EDIT

just before the last code lines in the set_current_version stored procedure (before the comment line "-- Finally, set the global info"), than the "g_version_id" comes up as -1. It should be 2 for the version I set if I look in the "dbo.SDE_versions" table...

Re: Setting Current version in SQL Server

MarcoBoeringa — Sat, 11 Dec 2021 11:45:06 GMT

OK, I now think I have gotten one step further with this. It appears the order of commands described in the Help topic "Editing versioned data in SQL Server using SQL" is incorrect, or that the ArcSDE stored procedure "SDE_getglobals" falsely doesn't allow you to set the current version when not in edit mode (at least in SQL Server, I don't know in other RDBMSs).

In addition, it appears any set version, only applies to the current active Transact SQL window, meaning you can't use it to change the way the view appears to the world outside SQL Server Management Studio (e.g. in Quantum GIS). Other applications accessing the Versioned View will only see the DEFAULT. Probably not entirely illogical, as setting the version as a kind of global for the entire database, would cause all users connected to the same Versioned Views to see the same version, and get confronted with subsequent changes. It needs to be user tied, or in this case limited to the context of the Transact SQL window.

It essentially means that edits against a specific version of a Versioned View is only supported straight in the Transact SQL command window, otherwise the ArcSDE C/Java API or ArcObjects is required, like for example the FDO Provider for ArcSDE in AutoCAD Map 3D does (uses C API) that does allow version switching on a user connection basis.

According to the Help the order of the commands is:

1) Open a Transact-SQL query window and execute the create_version procedure to create a named version in which to perform your edits.

EXEC sde.create_version 'sde.DEFAULT', 'myedits12111', 1, 2, 'version for my mv edits';

2) Set the version for the edit session to the child version you just created.

EXEC sde.set_current_version 'myedits12111';

3) Start an edit session by executing the edit_version stored procedure and specifying 1.

EXEC sde.edit_version 'myedits12111', 1;

However, I discovered the "sde.setcurrent_version" command fails and returns -1 for the version_id if NOT IN EDIT MODE already, even though the command is reported back as having run successfully.

This means you must run the text line:

EXEC sde.edit_version 'myedits12111', 1;

before running:

EXEC sde.set_current_version 'myedits12111';

E.g. if I enter (please note my ArcSDE is "dbo" owned, replace with "sde" if SDE owned):

EXEC MyDatabaseName.dbo.set_current_version 'MyUser.MyVersionName'

than this fails...

If I enter:

EXEC MyDatabaseName.dbo.edit_version 'MyUser.MyVersionName', 1
EXEC MyDatabaseName.dbo.set_current_version 'MyUser.MyVersionName'
EXEC MyDatabaseName.dbo.edit_version 'MyUser.MyVersionName', 2

with a start and end of an edit session, the command does run successfully. How do I know this? Well, first I entered some debugging code in the stored procedures for the "set_current_version" and "SDE_set_globals" and "SDE_get_globals". The results show the version_id is returned as a false -1 if not in edit mode, but the correct version_id for the "MyUser.MyVersionName" version if IN edit mode when setting the version.

In addition, I added a SELECT statement against the Versioned View in the same Transact SQL window, and the result showed the correct, NON-Default, version data with all the edits I did.

E.g.:

EXEC MyDatabaseName.dbo.edit_version 'MyUser.MyVersionName', 1
EXEC MyDatabaseName.dbo.set_current_version 'MyUser.MyVersionName'
EXEC MyDatabaseName.dbo.edit_version 'MyUser.MyVersionName', 2

SELECT * FROM MyDatabaseName.MyUser.MyDatasetName_VW

does show the proper Spatial Result of the Versioned View in SQL Server Management Studio.

Re: Setting Current version in SQL Server

MarcoBoeringa — Wed, 24 Apr 2013 09:32:10 GMT

Another observation:

I had attempted to edit an EXISTING version by skipping the "dbo/sde.create_version" stored procedure and specifying an already existing version created in ArcMap that wasn't being edited by anyone else. It appears this is the primary cause of the issue, as adding a

EXEC MyDatabaseName.dbo.create_version 'MyUser.MyVersionName', 'MyEditVersion', 1, 2, 'version for my mv edits';

line in the first step, DOES allow you to run "dbo/sde.set_current_version" command with good results when pointing to the newly created version. It seems the "create_version" stored procedure sets some vital context for running the "set_current_version". In addition, ESRI seems to recommend to always create you own named version in a SQL edit session. From the Help:

Create a named geodatabase version.

If you are editing named versions (versions other than DEFAULT), always edit your own, separate version; multiple editors cannot edit the same named version using versioned views.