topic Re: SQL Script runs successfully but changes not visible in Geodatabase Version in Data Management Questions

SQL Script runs successfully but changes not visible in Geodatabase Version

AdelaideZumwalt1 — Wed, 27 Sep 2017 15:34:55 GMT

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!

Re: SQL Script runs successfully but changes not visible in Geodatabase Version

Asrujit_SenGupta — Wed, 27 Sep 2017 15:38:59 GMT

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

UPDATE ElectricNetwork.dbo.Regulators

Re: SQL Script runs successfully but changes not visible in Geodatabase Version

George_Thompson — Wed, 27 Sep 2017 15:40:14 GMT

Adding a broader audience: https://community.esri.com/groups/geodatabase?sr=search&searchId=a5565455-889d-4f1d-b7e3-3fed728ddfe6&searchIndex=0‌

Re: SQL Script runs successfully but changes not visible in Geodatabase Version

AdelaideZumwalt1 — Wed, 27 Sep 2017 15:49:01 GMT

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.

Re: SQL Script runs successfully but changes not visible in Geodatabase Version

Asrujit_SenGupta — Wed, 27 Sep 2017 15:50:38 GMT

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...

Re: SQL Script runs successfully but changes not visible in Geodatabase Version

AdelaideZumwalt1 — Wed, 27 Sep 2017 16:13:32 GMT

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.

Re: SQL Script runs successfully but changes not visible in Geodatabase Version

Asrujit_SenGupta — Wed, 27 Sep 2017 16:21:02 GMT

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

Re: SQL Script runs successfully but changes not visible in Geodatabase Version

AdelaideZumwalt1 — Wed, 27 Sep 2017 16:23:02 GMT

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;

Re: SQL Script runs successfully but changes not visible in Geodatabase Version

Asrujit_SenGupta — Wed, 27 Sep 2017 16:39:43 GMT

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 Stack Exchange

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...

Re: SQL Script runs successfully but changes not visible in Geodatabase Version

Asrujit_SenGupta — Sun, 12 Dec 2021 01:52:24 GMT

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;‍‍‍‍‍‍‍‍‍

Re: SQL Script runs successfully but changes not visible in Geodatabase Version

AdelaideZumwalt1 — Wed, 27 Sep 2017 17:25:52 GMT

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...

Re: SQL Script runs successfully but changes not visible in Geodatabase Version

Asrujit_SenGupta — Wed, 27 Sep 2017 17:33:35 GMT

Well, only thing I can think of is that I hope the join is valid and has some records matching...

I will try to test with a sample data at my end whenever I have some time to test this and get back..

Re: SQL Script runs successfully but changes not visible in Geodatabase Version

Asrujit_SenGupta — Wed, 27 Sep 2017 18:13:44 GMT

Well, I tried with sample data and it worked for me. Make sure:

1. There are records matching for the Join that you are creating, for the Update to happen (I know this is obvious, but still confirm)

2. You are checking the correct version in ArcMap\ArcCatalog for the updates after executing the SQL.

Re: SQL Script runs successfully but changes not visible in Geodatabase Version

AdelaideZumwalt1 — Wed, 27 Sep 2017 20:17:11 GMT

Thank you Asrujit SenGupta! It was an issue with the records matching. Now that I sorted that out it works perfectly. Thank you so much for your time!!

Addie