2

I have an SQL Server database connected to QGIS 3.18. I am able to add tables including geography data type to the map without a problem. However, I want to be able to add a 'View' (Orn_View_spatial) with geography data type from SQL Server. It loads into the Browser panel successfully, but when I drag and drop it onto the map, QGIS returns an error message:

Layer is not valid: The layer dbname='MBA-LBA_Ornaments' 
  host=DESKTOP-DLA7UV8 estimatedmetadata=true 
  srid=4326 type=Point disableInvalidGeometryHandling='1' 
  primaryKeyInGeometryColumns='0' table="dbo".
"Orn_View_spatial" (Geo4) is not a valid layer and can not be added to the map.
Reason: No primary key could be found on table Orn_View_spatial

Apparently it is not possible to create a primary key on a View in SQL Server--is there any way around this issue?

1
  • 1
    Add your view definition. You're missing a valid primary key (which should be an int field, and I would suggest int4 unless your table is huge.)
    – Encomium
    Commented Oct 22, 2021 at 15:42

2 Answers 2

1

You're missing a primary key. Desktop GIS software will typically require any SQL table to have a primary key, which should be an int. ESRI products work best with a non-bigint format, such as int4 or smaller.

Do you have a primary key in one of the tables you're using in the view, whose rows are only returned once or less in the view? If so, you can use that field in the view definition. Otherwise, I would highly suggest starting off by creating primary keys on all rows.

1

Using MS SQL Server Express 12, I had success adding a view to QGIS 3.14 after I added

ROW_NUMBER() OVER (ORDER BY pk_ID ASC) AS FID

to my view definition, where pk_ID was the primary key of the main table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.