[Qgis-user] MSsql odbc connected gespatial view not loading in qgis 3.28

[damien stephens]

never mind I figured it out.
I needed to add an objectid field to the view using row-number() as
described here:
https://gis.stackexchange.com/questions/327701/viewing-mssql-data-in-qgis

the first line looks like
SELECT        ROW_NUMBER() OVER (ORDER BY c.Hole_ID ASC) AS OBJECTID,
C.Hole_ID,

cheers
Damien

On Tue, Feb 14, 2023 at 10:55 AM damien stephens <damien.stephens at gmail.com>
wrote:

> Hi All;
> I am having trouble bringing a geospatial  sql view into qgis using the
> add  mssql  server layer dialogue.
>
> I have a geospatial view that was created from two tables in a separate
> database.
> One of the two tables has a geospatial field. This table can be brought
> into qgis without issues using the add mssql server layer dialogue
> The combined view can be viewed spatially in server management studio with
> no errors.
> I can see the view in the mssql server layer dialogue and it displays as a
> spatial table.
> However when I try to filter the layer nothing happens, ie the filter
> dialogue does not appear.
> When i try to add the layer without filtering i get the following error:
>
> dbname='dbase' service='dbase' user='user' password='pasword' srid=28351
> type=Point disableInvalidGeometryHandling='1'
> primaryKeyInGeometryColumns='0' table="dbo"."view" (SP_geometry) is an
> invalid layer - not loaded
> I connect to the sql server using sql server authentication, which I have
> checked the password etc for several times and is correct.
> I can also open the view via the add vector layer - database odbc route as
> a non spatial table.
>
> I assume that I am missing a step to make the geospatial aspect of the
> view compatible with qgis?
>
> Any assistance would be greatly appreciated.
> sincerely
> Damien
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20230214/38d60ec2/attachment.htm>