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.
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';
Thank you George, I will review the document.
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?
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, thank you for the quick reply, and thanks for doing some digging on the issue! Regards, Jay