Feature request #12324

QGIS 2.8 Wien - Crashed on SQL Server Spatial Layers

Added by Ali Diba over 2 years ago. Updated almost 2 years ago.

Assignee:Nathan Woodrow
Category:Data Provider/MSSQL
Pull Request or Patch supplied:No Resolution:duplicate
Easy fix?:No


2.8 crashed on SQL Server Spatial tables and views. This has been a bug since version 2.0.1. 2.0.1 didn't support GUID fields but didn't crash - see #9007. 2.8 simply crashes even if they are no GUID fields. It simply does not work! I think we have regressed a bit when it comes to vector layers from SQL Server Spatial.

test.sql - SQL Script File to create and populate a SQL Spatial Table (22.3 KB) Ali Diba, 03/05/2015 05:41 PM

SQLTablePlusdata.sql (180 KB) Ali Diba, 03/06/2015 03:29 AM

Related issues

Related to QGIS Application - Bug report #9007: MS SQL uniqueidentifier data type is not supported Closed 11/04/2013
Related to QGIS Application - Feature request #10946: Non-integer key columns in MSSQL Open 07/24/2014


#1 Updated by Jürgen Fischer over 2 years ago

  • Category changed from DB Manager to Data Provider/MSSQL

#2 Updated by Giovanni Manghi over 2 years ago

  • Status changed from Open to Feedback
  • Priority changed from Normal to High
  • Crashes QGIS or corrupts data changed from No to Yes

#3 Updated by Ali Diba over 2 years ago

Not really. 2.8 does not allow you to add the SQL Server Spatial layer. 2.0.1 works fine with SQL Server Spatial layers as long as there is no GUID field in them. I don't think there was much testing of 2.8 with MSSQL Data Provider.

#4 Updated by Nathan Woodrow over 2 years ago

In order to help you will need to post some sample data. Make a sample database that crashes and script out the commands to create and insert the data.

#5 Updated by Ali Diba over 2 years ago

Here is a sample script to help you. You can start with create table statement. I was using SQL Server 2008 R2.

#6 Updated by Ali Diba over 2 years ago

You can remove the GUID column and check the behavior that I am seeing as well.

#7 Updated by Nathan Woodrow over 2 years ago

Changing the primary key to OBJECTID and not the GlobalId doesn't seem to crash it. Before 2.8 QGIS would just ignore the GUID column but now it treats it like a string and something is breaking because of it.

#8 Updated by Nathan Woodrow over 2 years ago

Ok yes it is that. If you have the uniqueidentifier as the primary key all the feature ids come out as 0, but if set to OBJECTID you will get correct ids.

Currently QGIS doesn't handle having GUID columns as a key column, it will need to be a int or bigint.

#9 Updated by Ali Diba over 2 years ago

Please use this attached SQL table and data. It has no GUIDs. Data types are datetime, nvarchar, varchar, int, and geometry. 2.8 Returns the following error:

dbname='FM_SpatialViews' host=sw-demo3 user='xxx' password='xxxx' srid=3857 type=POINT table="dbo"."test1" (Shape) sql= is an invalid layer - not loaded

#10 Updated by Nathan Woodrow over 2 years ago

You need to have a int/bigint based primary key for it to load. This is enforce in 2.6> as it broke things not having a ID column QGIS could use.

#11 Updated by Ali Diba over 2 years ago

I realized that after I posted my last update. I added an int column and things started working. I now know the pattern that I need to use to bring SQL Server Spatial layers into QGIS. I need an int/bigint based primary key and avoid GUID as key. It would be nice to support GUIDs as key columns in the future. This is important if we start using QGIS to collect features in the field in disconnected mode and merge them with existing features in the office. GUID can be used as the unique internal id in this case.

Thank you for your response and attention to my issue.

#12 Updated by Ali Diba over 2 years ago

  • Assignee set to Nathan Woodrow

Hi Nathan

We desperately need this bug to be fixed. How do we go about it?



My Contact:

#13 Updated by Giovanni Manghi about 2 years ago

what is the status of this issue?

#14 Updated by Nathan Woodrow about 2 years ago

  • Tracker changed from Bug report to Feature request
  • Target version set to Future Release - Nice to have

#15 Updated by Nathan Woodrow about 2 years ago

QGIS is in feature freeze for 2.10 but it can be added into a feature version. I will be in touch.

#16 Updated by Nathan Woodrow about 2 years ago

  • Resolution set to duplicate
  • Status changed from Feedback to Closed

#17 Updated by Robert Slivinsky almost 2 years ago

I have had similar issues editing MSSQL tables in QGIS. Seems to work fine for so many saves. Then after a while, when clicking save table, it takes longer to save. Then trying to save edits again, it freezes. While I assumed this was only a MSSQL editing issue, I believe I noticed a similar thing happen when editing .shp files so I wonder if it is purely related to MSSQL saves. Thanks, Rob

Also available in: Atom PDF