SQL Script runs successfully but changes not visible in Geodatabase Version

1960
13
Jump to solution
09-27-2017 08:34 AM
AdelaideZumwalt1
Occasional Contributor II

SQL Script runs successfully but the changes are not visible in the geodatabase. The version gets created successfully but it seems like the version is either not saved or something else is going on...

EXEC dbo.create_version 'dbo.DEFAULT', 'WMMtoESRI', 2, 1, 'This version contains data pushed from WMM to ESRI';
EXEC dbo.set_current_version 'WMMtoESRI';
EXEC dbo.edit_version 'WMMtoESRI', 1;
BEGIN TRANSACTION
UPDATE ElectricNetwork.dbo.Regulators
SET esPhasing = WMM_Export.dbo.REGULATOR.wmPhasing
FROM WMM_Export.dbo.REGULATOR
WHERE
ElectricNetwork.dbo.Regulators.GUID=WMM_Export.dbo.REGULATOR.wmextVoltageRegulator__GUID_ESR
COMMIT;
EXEC dbo.edit_version 'WMMtoESRI', 2;

Thank you!

0 Kudos
1 Solution

Accepted Solutions
Asrujit_SenGupta
MVP Regular Contributor

Try the below and see if this works:

EXEC dbo.create_version 'dbo.DEFAULT', 'WMMtoESRI', 2, 1, 'This version contains data pushed from WMM to ESRI';
EXEC dbo.set_current_version 'WMMtoESRI';
EXEC dbo.edit_version 'WMMtoESRI', 1;
MERGE ElectricNetwork.dbo.Regulators_evw
USING WMM_Export.dbo.REGULATOR
ON ElectricNetwork.dbo.Regulators_evw.GUID = WMM_Export.dbo.REGULATOR.wmextVoltageRegulator__GUID_ESR
WHEN MATCHED THEN
UPDATE SET ElectricNetwork.dbo.Regulators_evw.esPhasing = WMM_Export.dbo.REGULATOR.wmPhasing;
EXEC dbo.edit_version 'WMMtoESRI', 2;

View solution in original post

13 Replies
Asrujit_SenGupta
MVP Regular Contributor

You are updating the Feature Class\Table directly? Or the Versioned View for the feature class?

UPDATE ElectricNetwork.dbo.Regulators

0 Kudos
AdelaideZumwalt1
Occasional Contributor II

I was able to run the middle portion of the script and edit the geodatabase using SQL when it wasn't registered as versioned, now I wan't to do the same thing in a version.

0 Kudos
Asrujit_SenGupta
MVP Regular Contributor

You seem to be updating the Feature Class directly, which you should never do using SQL...

Check this link: Edit versioned data in SQL Server using SQL—Help | ArcGIS Desktop 

UPDATE ElectricNetwork.dbo.Regulators

The bold part should represent your Versioned View name, which is automatically created when you register as versioned a feature class. Check in Views in SQL Server Management Studio.

Also you won't need the below:

BEGIN TRANSACTION

COMMIT;

See the link provided for an example...

AdelaideZumwalt1
Occasional Contributor II

OK, I update the script with the versioned view name. Now I'm getting the error "UPDATE is not allowed because the statements updates view "ElectricNetwork.dbo.Regulators_evw" which participates in a join and has an INSTEAD OF UPDATE trigger. 

0 Kudos
Asrujit_SenGupta
MVP Regular Contributor

Post the complete SQL you are using.. and I hope you changed the below as well

WHERE
ElectricNetwork.dbo.Regulators.GUID=WMM_Export.dbo.REGULATOR.wmextVoltageRegulator__GUID_ESR

0 Kudos
AdelaideZumwalt1
Occasional Contributor II

EXEC dbo.create_version 'dbo.DEFAULT', 'WMMtoESRI', 2, 1, 'This version contains data pushed from WMM to ESRI';
EXEC dbo.set_current_version 'WMMtoESRI';
EXEC dbo.edit_version 'WMMtoESRI', 1;
UPDATE ElectricNetwork.dbo.Regulators_evw
SET esPhasing = WMM_Export.dbo.REGULATOR.wmPhasing
FROM WMM_Export.dbo.REGULATOR
WHERE
ElectricNetwork.dbo.Regulators_evw.GUID=WMM_Export.dbo.REGULATOR.wmextVoltageRegulator__GUID_ESR
EXEC dbo.edit_version 'WMMtoESRI', 2;

0 Kudos
Asrujit_SenGupta
MVP Regular Contributor

The SQL looks good to me, but check the following links:

Update Versioned View with JOIN 

arcgis 10.3 - Edit default version table view using SQL Server - Geographic Information Systems Stac... 

Seems to be a known issue in MS SQL Server. So try using MERGE and check as suggested...

You will get multiple MS links for same issue, not even related to Esri, for this error...

Asrujit_SenGupta
MVP Regular Contributor

Try the below and see if this works:

EXEC dbo.create_version 'dbo.DEFAULT', 'WMMtoESRI', 2, 1, 'This version contains data pushed from WMM to ESRI';
EXEC dbo.set_current_version 'WMMtoESRI';
EXEC dbo.edit_version 'WMMtoESRI', 1;
MERGE ElectricNetwork.dbo.Regulators_evw
USING WMM_Export.dbo.REGULATOR
ON ElectricNetwork.dbo.Regulators_evw.GUID = WMM_Export.dbo.REGULATOR.wmextVoltageRegulator__GUID_ESR
WHEN MATCHED THEN
UPDATE SET ElectricNetwork.dbo.Regulators_evw.esPhasing = WMM_Export.dbo.REGULATOR.wmPhasing;
EXEC dbo.edit_version 'WMMtoESRI', 2;
AdelaideZumwalt1
Occasional Contributor II

Thank you, I had that typed up just as you sent it. Unfortunately, while it creates a version and runs successfully, when I go the open the version the data is not there... 

0 Kudos