Joining/merging from versions in two different databases is broken

[regan-sarwas]

When selecting from two different versions in two different SDE database, the one specified first with the exec {database_name}.sde.set_current_version 'owner.version' command, is not just ignored, but the values returned from that database are from the base table, not the versioned view specified, nor the versioned view for default. It does not matter what the "using" database is (it could be master) when running the commands. It doesn't work even if the versions have the same name. The only currently known work around is to post the relevant version to default and then compress. Once compressed, the version's changes should be in the base table and the issue is moot.

Other possible un-tested work arounds are:

• Upgrade the akr_socio database from version 10.2 to version 10.8 (like akr_facility2)
• Submit a support ticket with esri
• Set the version in DB#1 and select values into a temp table, then set the version in DB#2 and do the join or merge update with the version in DB#2 and the temp table from DB#1.

Impacted Stored Procedures:

• akr_socio.dbo.Create_POI_Points
• akr_socio.dbo.Delete_POI_PTs_no_longer_linked_to_facilities
• akr_socio.dbo.Sync_POI_Pt_with_Buildings
• akr_socio.dbo.Sync_POI_Pt_with_TrailFeatures

Impacted Views:

• akr_socio.dbo.QC_ISSUES_POI_PT
• akr_facility2.dbo.QC_ISSUES_AKR_BLDG_CENTER_PT
• akr_facility2.dbo.QC_ISSUES_TRAILS_FEATURE_PT

Generic steps to reproduce (assuming you are dbo in SQL Server)

-- Look at the base tables
select field1 from {db1}.{fc1} where OBJECTID = X  -- remember value 
select field2 from {db2}.{fc2} where OBJECTID = Y  -- remember value 
-- Create a version and make a change in database #1
exec {db1}.sde.create_version 'sde.default', 'v1', 2, 2, ''
exec {db1}.sde.set_current_version 'dbo.v1'
exec {db1}..sde.edit_version 'dbo.v1', 1
update {db1}.{fc1}_evw set field1 = 'A' where OBJECTID = X  -- 'A' must be different than base table
exec {db1}..sde.edit_version 'dbo.v1', 2
-- Do the same in database 2
exec {db2}.sde.create_version 'sde.default', 'v2', 2, 2, ''
exec {db2}.sde.set_current_version 'dbo.v2'
exec {db2}..sde.edit_version 'dbo.v2', 1
update {db2}.{fc2}_evw set field2 = 'B' where OBJECTID = Y -- 'B' must be different than base table
exec {db2}..sde.edit_version 'dbo.v2', 2
-- Look at the base tables again (should be the same as before edits)
select field1 from {db1}.{fc1} where OBJECTID = X
select field2 from {db2}.{fc2} where OBJECTID = Y
-- Look at the versions independently (not possible when doing a join or merge)
exec {db1}.sde.set_current_version 'dbo.v1'
select field1 from {db1}.{fc1}_evw  where OBJECTID = X  -- RIGHT ('A')
exec {db2}.sde.set_current_version 'dbo.v2'
select field2 from {db2}.{fc2}_evw  where OBJECTID = Y  -- RIGHT ('B')
-- Set v1, then v2, then select from the versions
exec {db1}.sde.set_current_version 'dbo.v1'
exec {db2}.sde.set_current_version 'dbo.v2'
select field1 from {db1}.{fc1}_evw  where OBJECTID = X  -- WRONG ( base table value not `A`)
select field2 from {db2}.{fc2}_evw  where OBJECTID = Y  -- RIGHT ('B')
-- Set v2, then v1, then select from the versions
exec {db2}.sde.set_current_version 'dbo.v2'
exec {db1}.sde.set_current_version 'dbo.v1'
select field1 from {db1}.{fc1}_evw  where OBJECTID = X  -- RIGHT ('A')
select field2 from {db2}.{fc2}_evw  where OBJECTID = Y  -- WRONG  ( base table value not `B`)
-- Cleanup - Drop the versions
exec akr_facility2.sde.set_default
exec {db1}.sde.delete_version 'dbo.v1'
exec akr_socio.sde.set_default
exec {db2}.sde.delete_version 'dbo.v2'

Here is a real example

[regan-sarwas]

NOTE: In all the impacted stored procedures, which are in akr_socio and are editing akr_socio data, the facility version is set first, so the akr_socio versioned view will always be correct. Only akr_facility needs to have the needed data in the base table. For the QC views, the order is set in the Do Quality Control Check.sql file. When running the QC queries, the related version is set first since that should be easier to get into the base tables (certainly the version being checked cannot be pushed to the base tables (posted and compressed) until it has completed CQ).