Bug report #10615

MSSQL table attributes are not correct in qgis

Added by matt veenstra about 6 years ago. Updated about 5 years ago.

Category:Data Provider/MSSQL
Affected QGIS version:2.2.0 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 #:19019


For MSSQL 2008 R2 point feature layer; the attribute values displayed in QGIS 2.2 are not correct.

My MSSQL table is defined as:
x(decimal(18,6), null)
y(decimal(18,6), null)
z(varchar(50), null)

where x and y contain the latitude and longitude.

Geometry column created and populated as follows:
ADD geom geometry
UPDATE [table]
SET geom = geometry::Point(x,y,4326)

test1.bak - MSSQL 2008 R2 test database backup file (2.79 MB) matt veenstra, 2014-06-19 06:52 PM


#1 Updated by Nathan Woodrow about 6 years ago

What isn't correct?

Have you tested master because I have changed some stuff since 2.2.

#2 Updated by matt veenstra about 6 years ago

The attributes in sql server are as follows:
z x y
a -122.347123 47.569034
b -122.346273 47.590282
c -122.357531 47.586916
d -122.358341 47.575575
e -122.360000 47.600000

The attributes table in qgis shows:
z x y
e -122.360000 47.6000000
e -122.360000 47.6000000
e -122.360000 47.6000000
e -122.360000 47.6000000
e -122.360000 47.6000000

When using the identify features tool, clicking on each point displays the following same attributes for each point:
a, -122.347123, 47.569034

The locations of the points are correct.

If I save the layer to a .csv file, the values in the csv file are correct.

I haven't had a chance to try qgis master.

#3 Updated by Jan Lippmann about 6 years ago

Do you use a primary key column in the table?
Can you send the create statements for the table or a complete mssql server bak backupfile? So that i could test.

#4 Updated by matt veenstra about 6 years ago

I did not use a primary key because I was just trying a quick and dirty trial...
Adding a primary key results in qgis displaying the correct attribute values.

I understand that not having a primary key is not acceptable database design; however, I didn't expect the resulting behavior in Qgis... I suggest either not allowing connecting to tables that do not have a primary key or provide a message window warning similar to "unexpected behavior can result from tables that do not have a primary key".

For what it's worth, I attached backup file with tables test_1 (no primary key) and test_3 (with a primary key).


#5 Updated by Nathan Woodrow about 6 years ago

We need a primary key because everything is done using the ID in QGIS. SQL Server doesn't provide a auto id for us so we have to hope that it's set. If it's not set we fall back to using a int column but it must be unique or else we can't follow what is going on.

A pain I know, would be a lot better if there was just a ROWID column we always knew was there as a fall back.

#6 Updated by Jürgen Fischer about 6 years ago

  • Target version changed from Version 2.2 to Future Release - Lower Priority

#7 Updated by Nathan Woodrow about 5 years ago

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

As of 2.8 having a unique key column is now enforced to stop this from happening. I will be adding better error messages in the future.

Also available in: Atom PDF