Feature request #12324
QGIS 2.8 Wien - Crashed on SQL Server Spatial Layers
|Pull Request or Patch supplied:||No||Resolution:||duplicate|
|Easy fix?:||No||Copied to github as #:||20502|
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.
#8 Updated by Nathan Woodrow over 4 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 4 years ago
- File SQLTablePlusdata.sql added
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
#11 Updated by Ali Diba over 4 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.
#17 Updated by Robert Slivinsky almost 4 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 about 1 year 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])
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
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!!