Save Multi-Versioned Spatial Views

641
5
03-27-2020 11:14 AM
JayHodny
Occasional Contributor III

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.

0 Kudos
5 Replies
George_Thompson
Esri Frequent Contributor

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

--- George T.
JayHodny
Occasional Contributor III

Thank you George, I will review the document.

0 Kudos
JayHodny
Occasional Contributor III

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?

0 Kudos
George_Thompson
Esri Frequent Contributor

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.

--- George T.
0 Kudos
JayHodny
Occasional Contributor III

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

0 Kudos