Bug report #12556

SQL Server Spatials views can not be laded any more

Added by Thomas Schneider almost 5 years ago. Updated almost 5 years ago.

Status:Closed
Priority:High
Assignee:-
Category:Data Provider/MSSQL
Affected QGIS version:2.8.1 Regression?:No
Operating System:Windows Easy fix?:No
Pull Request or Patch supplied:No Resolution:invalid
Crashes QGIS or corrupts data:No Copied to github as #:20697

Description

In QGIS version 2.8.1 spatial views of a SQL Server connection can not be loaded any more. This worked well at least up to version 2.2.0

Adding a spatial view, this error message is displayed:
dbname='NBNData_D-CTS' service='Recorder' srid=4326 type=POINT table="dbo"."bfn_sample_spatial_ref" (center_geom) sql= ist ein ung├╝ltiger Layer - nicht geladen.

So QGIS 2.8.1 failed loading projects created in 2.2.0 that have SQL server connections with spatial view layers.

I checked this using SQL Server 2014 and 2008 R2.

History

#1 Updated by Nathan Woodrow almost 5 years ago

Do you have a column which is unique int or long int?

You will need this in order to open in 2.8. This is enforced in 2.8 because we need the id for lookups.

#2 Updated by Thomas Schneider almost 5 years ago

The view has a primary key column, but the keys values are alphanumerical [char (16)]. So the view doesn't have an unique integer column.

Checking the issue, I have created a table with the same structure. This could be loaded.

#3 Updated by Nathan Woodrow almost 5 years ago

qgis only supports int or long id for ids on SQL Server at this current time

#4 Updated by Thomas Schneider almost 5 years ago

Thanks!

As a workaround a virtual ID column can be created by using the ROW_NUMBER() function:

CREATE VIEW [dbo].[test_view] AS
SELECT ROW_NUMBER() OVER AS ID,
MY_ALPHANUMERICAL_KEY_COL, COL2, SPATIAL_COLUMN
FROM TABLE1

QGIS will load this view.

But I wonder why can physical tables be loaded without having a numerical ID column?

#5 Updated by Nathan Woodrow almost 5 years ago

  • Resolution set to invalid
  • Status changed from Open to Closed

I am closing this as I will be adding non int based keys in a future version which will solve this issue.

Also available in: Atom PDF