SQL View using Multi versioned views not returning all rows

MeleKoneya1 — Tue, 01 Oct 2024 14:21:11 GMT

We have a view that uses several multi versioned views to export our parcels. We are joining a number of attributes from different sources to the parcel shape.

For years, this view was working fine, but now, it is not returning all the shapes for the parcels.

Are there any tips or tricks for performing such a join?

We are using the multiversioned view as we don't have the attributes in the base tables.

SELECT Addr.OBJECTID,

Addr.asset_id,

Addr.parcel_code,

Addr.insert_date,

Owner.owners_name,

Addr.fulladdr,

Addr.zip_code,

Addr.qs_num,

subdiv_name.subdiv_name,

Addr.mcr_number,

Addr.lot_number,

Addr.tract_name,

Addr.full_zoning,

Addr.full_fema,

Addr.char_area,

Addr.use_code,

addr.juris_code,

Owner.owners_deed_number,

Owner.owners_deed_date,

Owner.owner_use_code,

Owner.legal_class,

Owner.mail_address,

Owner.mail_city,

Owner.mail_state,

Owner.mail_zip,

Parcels.mslink,

parcels.sq_ft,

Parcels.acres,

Parcels.Shape

FROM

(

(SELECT OBJECTID, parcel_centroid_id, asset_id, apn AS parcel_code, creation_date AS insert_date, site_address AS fulladdr, zip_code, qs_num,

mcr_num AS mcr_number, lot_num AS lot_number, tract_name, full_zoning, full_fema, char_area, use_code, jurisdiction AS juris_code

FROM LMS_SDE.lms.PARCEL_ADDRESS_POINTS_EVW

WHERE (use_code NOT IN ('R', 'S')) AND (feature_state = 'ACTIVE')) AS Addr

LEFT OUTER JOIN

(Select MSLINK, Shape, Shape.STArea() AS sq_ft, Shape.STArea() / 43560 AS acres

from lms_sde.lms.Fabric_parcels_polys_evw) AS Parcels ON Addr.parcel_centroid_id = Parcels.MSLINK LEFT OUTER JOIN

(SELECT DISTINCT

OWNER_NAME AS owners_name, APN, deed_num AS owners_deed_number, DEED_DATE AS owners_deed_date, property_use_code AS owner_use_code, Legal_Class, CASE WHEN mail_addres_2 IS NULL THEN mail_address_1 ELSE mail_address_1 + ' ' + mail_addres_2 END AS mail_address,

mail_addres_2, MAIL_CITY, MAIL_STATE, MAIL_ZIP

FROM LMS_SDE.lms.COUNTY_PARCELS) AS Owner ON Addr.parcel_code = Owner.APN LEFT OUTER JOIN

(SELECT DISTINCT mcr_num

FROM LMS_SDE.lms.FABRIC_SUBDIVISION_BOUNDARY_POLYS_EVW) AS Subdiv OUTER APPLY

(SELECT TOP 1 name as subdiv_name, last_edit_date

FROM LMS_SDE.lms.FABRIC_SUBDIVISION_BOUNDARY_POLYS_EVW

WHERE Subdiv.mcr_num = mcr_num) Subdiv_Name ON Addr.mcr_number = subdiv.mcr_num

)

Re: SQL View using Multi versioned views not returning all rows

George_Thompson — Tue, 01 Oct 2024 14:35:47 GMT

Was there any update on the SQL Server machine or the Enterprise Geodatabase before the issue occurred?

Re: SQL View using Multi versioned views not returning all rows

MeleKoneya1 — Tue, 01 Oct 2024 20:22:13 GMT

George, the SQL instance was upgraded to 2019. The combability mode is at 2017. The Geodatabase is 10.6.1 Geodatabase. We use ArcMap clients still. Should we upgrade the Geodatabases?

Re: SQL View using Multi versioned views not returning all rows

George_Thompson — Wed, 02 Oct 2024 10:24:22 GMT

Where any of the changes above made before the behavior started?

I have seen something similar when we completed an upgrade of the EGDB from version x --> y. It gave an error when you opened the view in SSMS.

Usually do not see something working one day and the next not working without a patch / update / etc.

topic Re: SQL View using Multi versioned views not returning all rows in Geodatabase Questions

SQL View using Multi versioned views not returning all rows

Re: SQL View using Multi versioned views not returning all rows

Re: SQL View using Multi versioned views not returning all rows

Re: SQL View using Multi versioned views not returning all rows