Opening MSSQL Spatial View in QGIS [closed]

Question

I have created a view in MSSQL Server 2012 that does a spatial join as below:

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS PID, DS.Dip_No, PB.SP_GEOMETRY
FROM dbo.Dipsites AS DS INNER JOIN
  dbo.Parcel_Base AS PB ON PB.SP_GEOMETRY.STContains(DS.SP_GEOMETRY) = 1

I can view the resulting data in SQL Mgmt Studio but I cannot see it in QGIS v3.4. Is it possible to open a view in QGIS? Or am I missing something in my SQL that is required for this to happen?

---

I have added the following SQL now:

    SELECT DS.MI_PRINX AS OBJECTID, DS.Dip_No, PB.Pcl_num, geometry::STGeomFromText(PB.SP_GEOMETRY.STAsText(), 28356) AS GEOMETRY
FROM dbo.Dipsites AS DS INNER JOIN dbo.Parcel_Base AS PB ON PB.SP_GEOMETRY.STIntersects(DS.SP_GEOMETRY.STBuffer(200)) = 1

I have pulled a unique ID field and called it OBJECTID. I have made sure the GEOMETRY field is actually geometry. But this still won't come through to QGIS.

Any other suggestions I may be missing?

Answer 1

I have upgraded SQL Server from 2012 to 2017 and now all views are visible in QGIS. Not sure as to why this is the case, but 2017 seems to allow the access and viewing of views within QGIS.