I just downloaded QGIS a few days ago (version 2.8.2-Wien) but I am not able to get it to work as I thought it would. I tried pulling in spatial data from sql server (a "geometry" column containing points). I click the button that says "Add MSSQL Spatial Layer" and find my point table but when I click the "Add" button it gives me this error (after a minute):
dbname='myDb' host=localhost\sqlserver2014 srid=4326 type=POINT table="dbo"."Locations_Sample"
(spatial_data_point) sql= is an invalid layer and cannot be loaded. Please check the message log for further info.
The "log message" words in that error were a link in QGIS so I clicked it and it opened a "Log Messages" panel and on that panel (on the "General" tab), it just had the same error message I typed above (with one inconsequential text difference… it says "is an invalid layer – not loaded" instead of "and cannot be loaded").
I tried making that source table ("Locations_Sample") smaller (it was around 120,000 points so I tried just using 100 points) and that smaller table imported with no problems. I then tried increasing the amount of data in that table and hitting the "Refresh" button in QGIS to find what the row limit was but it succeeded in refreshing every time I tried all the way up to the full 120,000 rows (showing all the points on the map as far as I can tell). This may seem like a workaround but I don't think it really is because every time I close and re-open the project, it errors when trying to load that layer…also other things (like the heatmap raster generator) are failing possibly due to this "invalid layer" issue.
FYI, I subsequently tried loading the table with 100 records at first and then putting all the records in the source table "Locations_Sample" and that always worked. Also I think possibly the issue with the heatmap raster generation is that it only works with the initial records I load (100 points).
How can I figure out what the problem is with this layer (and ideally fix it)?
Best Answer
Thank you for your help! (Nathan helped me on the QGIS issue tracking page with the same advice he posted here and that mapBaker also posted). I added a unique int column to this table and then it loaded the layer with no problem.
Thanks again.