2

I am connecting to an MSSQL database table which does not have a geometry type field, but has coordinates in separate columns. The ODBC connection works, but when I bring the table in to QGIS, it is filtered, with no way to unfilter it, so I only see one row in the attribute table.

Has anyone seen this and know how to see all features?

I have tried other tables from databases with the same schema and most show up fine, but occasionally others have the same issue.

enter image description here

1 Answer 1

1

Check the primary keys in 'problematic' SQL Server tables and if they don't exist, try to add it (as a unique integer).

Queries that QGIS created and sent to SQL Server sometimes do not choose a proper unique integer column in tables without a primary key defined. It simply chooses the first INT column available by order in a table definition (and sometimes that column could contain values that are not unique). As a result, it sends back weird results (only one row in your case).

3
  • I've done some more investigating. The primary keys are all okay. There appear to be some records with carriage returns or line feeds in them, and others with coordinates way out of range. For those tables only the last record is being shown in QGIS. Still looking into other causes.
    – Ian
    Commented Dec 4, 2020 at 0:16
  • We have the same problem with some of our database tables. For one table I created a new table and inserted all the data into it, then I truncated the old table and copied the data from the new table back to the old table, then I renamed the new table with the old table name and also renamed the old table. For each step of these tests the new table works without fault and the old table has this problem. I think my tests have eliminated possible problems with Primary Keys or hidden characters. I also ran the SQL Server DBCC CHECKDB and DBCC CHECKTABLE operations with no errors reported. Commented Nov 17, 2022 at 20:59
  • 1
    We have found that QGIS versions 3.18 and 3.22 (we haven't tested other versions) have these problems with float values from SQL Server. If you change the data type in SQL Server to numeric than the problem stops. Commented Nov 17, 2022 at 22:48

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.