topic Re: Save Multi-Versioned Spatial Views in Geodatabase Questions

Save Multi-Versioned Spatial Views

JayHodny — Fri, 27 Mar 2020 18:14:28 GMT

We are having trouble creating a spatial view on a versioned dataset in attempt to link the most recent inspection (stored in a SDE related table) with an Asset (SDE Feature Class). The relationship class is a one to many as there can be multiple inspections over time occurring at the same asset location. We can create SQL queries in SQL Server using the setcurrentversion() stored procedure; however, we can’t figure out how to incorporate that stored procedure into creating and saving a new View (if this is even possible?). Our current SDE database structure is multi-versioned, e.g., Default – QA – Edit. Ideally, we would have a spatial view linked with the edit version so that we can dynamically symbolize (i.e. days since last inspection) for field staff to prioritize locations and see real-time updates. We use ArcGIS for Server 10.7.1 and our SQL Server geodatabases at are 10.7.1, as is our ArcMap clients. Pro is at the latest released version.

Re: Save Multi-Versioned Spatial Views

George_Thompson — Fri, 27 Mar 2020 18:20:18 GMT

Take a look at this doc: Read versioned data in SQL Server using versioned views—Help | Documentation

EXEC sde.set_current_version 'version name'
SELECT filed1,field2,field3 FROM versioned_viewname WHERE region = 'b';

Re: Save Multi-Versioned Spatial Views

JayHodny — Fri, 27 Mar 2020 18:31:54 GMT

Thank you George, I will review the document.

Re: Save Multi-Versioned Spatial Views

JayHodny — Mon, 30 Mar 2020 18:54:52 GMT

George, I understand and can replicate when it comes to reading versioned data within an active T-SQL window but saving it as a view accessible from ArcCatalog is where the problem lies. Sql Server rejects having exec as the first line when saving as a new View in the database. “Incorrect syntax near exec”. Any thoughts?

Re: Save Multi-Versioned Spatial Views

George_Thompson — Mon, 30 Mar 2020 19:48:10 GMT

To be honest, I have not saved that type of view before. Just searching around and it you are correct that SQL Server views do not like the "EXEC" in the view.

Re: Save Multi-Versioned Spatial Views

JayHodny — Mon, 30 Mar 2020 19:51:20 GMT

George, thank you for the quick reply, and thanks for doing some digging on the issue! Regards, Jay