3

I have a table called [stg].[Stops] in SQL Server that I would like to load in QGIS for data analysis. This is the first time I use the MSSQL Server connection option in QGIS. Unfortunately, even though QGIS recognizes the table and its geography data type. When I attempt to connect, I get an invalid layer error.

Here is a screenshot of the menu:

enter image description here

and here is the error:

2017-01-25T11:07:37 1 dbname='TK463' host=******* srid=4326 type=Point table="stg"."Stops" (GEOM) sql= is an invalid layer - not loaded

What could possible cause that?

  • 4
    Do you have a primary key field or a unique OBJECTID or ROWNUM field in your data? – DPSSpatial Jan 25 '17 at 16:49
  • 1
    Thanks I thought I had a PRIMARY KEY, but for some reason I did not! This fixes the issue thanks again! – Xavier Jan 25 '17 at 17:01
  • Great! Keep in touch with any mssql / qgis issues - we use it every day! – DPSSpatial Jan 25 '17 at 17:04
  • This also worked for me. I wonder why this is not mentioned as an answer! – mohsen hs Mar 22 '18 at 9:39
  • The issue for me was that the sql server table name contained a full stop – Ronnie Apr 6 '18 at 9:04
1

Posting DPSSpatial's comment as an answer:

Check you have a primary key field or a unique OBJECTID or ROWNUM field.

You can add this to a view by:

ROW_NUMBER() OVER(ORDER BY label ASC) AS ID#

where label is any column you choose.

0

Following reply worked for QGIS 3.4

The case: On the same server, created an exact copy of a polygon table on a different db

SELECT * INTO db2.dbo.geom_table_copy FROM db1.dbo.geom_table 

While QGIS was reading the original table normally, when attempting to insert the copied table I would get

WARNING    dbname='db2' host=www.myhost.com user='user' password='pwd' srid=4326 type=Polygon disableInvalidGeometryHandling='0' table="dbo"."geom_table_copy" (POLYGON) sql= is an invalid layer - not loaded

The key seems to be in the last argument sql=. For some reason, on the specific db, the sql field (filter) was not recognised as blank (even though it seemed so) but as something invalid. I fixed the error by editing filters, setting something simple as filter i.e. ID <=100 and then erasing it. This reset the sql field to empty and the layer was properly loaded.

  • How did you add this filter? – mapping dom Nov 27 '18 at 10:15
  • Three ways to do so: 1. double click on the layer and wait for the filter window to pop up ("Query Builder" 2.Select the table and click "set filter" on the lower right of the data source manager window 3.Click the SQL column, at the row that is the table you are interested in and directly type an SQL argument – Zannis K Nov 28 '18 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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