QGIS » QGIS Application

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.

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.

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

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.

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.

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.

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.

what is the status of this issue?

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

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

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!!