topic Updating Versioned Views Via SQL Management Studio Fails in Data Management Questions

Updating Versioned Views Via SQL Management Studio Fails

daniel__j — Thu, 26 Jan 2023 22:14:16 GMT

I am trying to update a single column of a versioned view using Sql. We are using a Microsoft SQL Server geodatabase. The production Geodatabase version is 10.0. The development Geodatabase version is 10.3.1.

The update query would look something like this:

UPDATE [Meter_evw] SET Zone_name = 'example' WHERE ObjectId = 1;

Running this query results in the following error:

Could not find stored procedure '<DatabaseName>.<Schema>.SDE_get_globals'.

If I run the same query on our development geodatabase (basically an exact copy, however it's version is 10.3.1) I have no issues, everything works as expected.

I know for a fact that I used ArcMap 10.3.1 to register the Meter FeatureClass (on the production Geodatabase) in question as versioned.

My suspicion is that the mismatch between the ArcMap version and the Geodatabase version when the versioned views were created has caused this issue. Is that correct? If so what is the best way to fix it?

I have two ideas:

- Upgrade the production database to 10.3.1
- Use ArcMap 10.3.1 to remove the versioned views, and use ArcMap 10.0 to add the versioned views back.

Are there better options and is this even the correct direction?

Thanks,

Daniel

Re: Updating Versioned Views Via SQL Management Studio Fails

George_Thompson — Fri, 27 Jan 2023 11:55:30 GMT

I would start with, you may want to upgrade to a newer (supported) version.

There were major changes between 10.0.x and 10.3.x enterprise geodatabases. I imagine that this may be part of the issue.

Is there a reason that you are not on a newer version, like at least 10.7.1?

Re: Updating Versioned Views Via SQL Management Studio Fails

daniel__j — Fri, 27 Jan 2023 13:31:00 GMT

Is there a reason that you are not on a newer version, like at least 10.7.1?

In my opinion, not a good one. My employer's IT department consisted of 1 person up until about two years ago. Basically keeping ArcGis software up to date has never been made a priority. Which is why we are stuck on 10.0 for the DB and 10.3.1 for ArcMap and ArcServer.

I have never updated enterprise DB before, how painful is the process? How much is going to break? I have multiple in house applications that require access to our production database.

Thanks for the help.

Re: Updating Versioned Views Via SQL Management Studio Fails

George_Thompson — Fri, 27 Jan 2023 14:17:27 GMT

Upgrading is usually a quick process on SQL Server. Here is the doc for going to 10.8.x: https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-sql-server/upgrade-geodatabase-sqlserver.htm

The biggest issue may be that you need to upgrade SQL Server to a supported release also.

Part of the issue would be that your ArcMap / Server are still on an unsupported version. Here is a compatibility doc: https://desktop.arcgis.com/en/arcmap/latest/manage-data/geodatabases/client-geodatabase-compatibility.htm

Hard to say 100% that nothing would change. But moving that many versions, it is possible that something happens.

My suggestion: Make sure you are on a supported SQL Server release. Take a backup and restore to a development environment and then run the upgrade process to become familiar with it. Once you have that completed, schedule a maintenance window, take a verified backup, then upgrade and see if you have any issues.

Re: Updating Versioned Views Via SQL Management Studio Fails

daniel__j — Fri, 27 Jan 2023 14:25:26 GMT

Alright sounds good. Thanks for the links and for all your help. I will get to work on doing this. As is probably obvious, it's long been overdue.

Re: Updating Versioned Views Via SQL Management Studio Fails

George_Thompson — Fri, 27 Jan 2023 20:44:41 GMT

Be sure to document your steps and take verified backups before everything!

Good luck and let us know how it goes.

Re: Updating Versioned Views Via SQL Management Studio Fails

daniel__j — Wed, 01 Feb 2023 21:01:00 GMT

So far I have only updated to 10.3.1, but it went well! Of course now I have a follow up question ....

https://community.esri.com/t5/data-management-questions/dangers-of-migrating-spatial-storage-types/m-p/1254065#M44400

Re: Updating Versioned Views Via SQL Management Studio Fails

George_Thompson — Thu, 02 Feb 2023 11:40:48 GMT

The following jumps should be easier. I will comment on that question also