Feature request #12324

QGIS 2.8 Wien - Crashed on SQL Server Spatial Layers

Added by Ali Diba about 9 years ago. Updated over 5 years ago.

Status:Closed
Priority:High
Assignee:Nathan Woodrow
Category:Data Provider/MSSQL
Pull Request or Patch supplied:No Resolution:duplicate
Easy fix?:No Copied to github as #:20502

Description

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, 2015-03-05 05:41 PM

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


Related issues

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

History

#1 Updated by Jürgen Fischer about 9 years ago

  • Category changed from DB Manager to Data Provider/MSSQL

#2 Updated by Giovanni Manghi about 9 years ago

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

isn't this duplicate of #9007-3?

#3 Updated by Ali Diba about 9 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 about 9 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 about 9 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 about 9 years ago

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

#7 Updated by Nathan Woodrow about 9 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 about 9 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 about 9 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 about 9 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 about 9 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 about 9 years ago

  • Assignee set to Nathan Woodrow

Hi Nathan

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

Regards,

-ali

My Contact:

#13 Updated by Giovanni Manghi almost 9 years ago

what is the status of this issue?

#14 Updated by Nathan Woodrow almost 9 years ago

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

#15 Updated by Nathan Woodrow almost 9 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 almost 9 years ago

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

#17 Updated by Robert Slivinsky over 8 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

#18 Updated by Liam Caffrey over 5 years ago

Ali Diba wrote:

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

This is still a problem with your test.sql file on QGIS 3.2 (Bonn). I'm running...
Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) Aug 15 2017 10:23:29 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Here is a simple MRE, run this code against your database. Navigate to it in QGIS and "Add selected layer(s) to canvas". Just pan around a bit and QGIS crashes.
IF OBJECT_ID('dbo.[qgis_temp_2]') IS NOT NULL drop table dbo.[qgis_temp_2];
CREATE TABLE dbo.[qgis_temp_2]
(
[OBJECTID] [int] NOT NULL
,[the_geom] [geometry] NULL
,CONSTRAINT [PK_qgis_temp_2_OBJECTID] PRIMARY KEY CLUSTERED ([OBJECTID])
);

with
the_points as
(
select cast(1 as int) as OBJECTID, geometry::STPointFromText('POINT (-8.317927 53.301678)', 4326) as the_geom union all
select cast(2 as int) as OBJECTID, geometry::STPointFromText('POINT (-8.317833 53.301747)', 4326) as the_geom union all
select cast(3 as int) as OBJECTID, geometry::STPointFromText('POINT (-8.24239 53.313442)', 4326) as the_geom
)
insert into dbo.qgis_temp_2 (OBJECTID, the_geom)
select OBJECTID, the_geom
from the_points;

I am snookered now. Most of my spatial is in MSSQL. No option in DBManager for MSSQL and as a workaround I am having trouble connecting to my SQL Server instance from Postgres via Foreign Tables and via FreeTDS/tsql!!

Also available in: Atom PDF