Bug report #15218

MSSQL columns with default values not ignored

Added by Damien Smith over 3 years ago. Updated about 1 year ago.

Status:Closed
Priority:Normal
Assignee:Tamas Szekeres
Category:Data Provider/MSSQL
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:23155

Description

Many MSSQL tables use unique identifiers (UUID) for columns. These often have a default value such as newid(). QGIS currently attempts to overwrite these values when saving which results in an error.

QGIS needs to completely ignore MSSQL columns that have a default value when doing updates and inserts. Setting the fields to "Hidden" works for inserts but then fails for updates.

History

#1 Updated by Damien Smith over 3 years ago

Actually, setting to "Hidden" or "Text" (Not editable) allows inserting and updating but copying and pasting fails if there is a unique constraint because the UUID is duplicated rather than using the default newid() function.

So, the only way to fix this is to reset fields which have a default value to their default value when copying and pasting. Also, using a view is not an option for large MSSQL tables as QGIS does a full table extents scan on views as it's not aware of the spatial index on the base table.

#2 Updated by Damien Smith over 3 years ago

If the UUID has a unique constraint, splitting the geometry also causes a duplicate but the original is committed to the database when saving causing lost geometry.

#3 Updated by Giovanni Manghi over 2 years ago

  • Easy fix? set to No
  • Regression? set to No

#4 Updated by Jürgen Fischer about 2 years ago

  • Category set to Unknown

#5 Updated by Nathan Woodrow over 1 year ago

  • Description updated (diff)

Damien can you check if this is still the case in 3. I have a feeling I have already fixed it.

#6 Updated by Nyall Dawson about 1 year ago

  • Category changed from Unknown to Data Provider/MSSQL

#7 Updated by Nyall Dawson about 1 year ago

  • Status changed from Open to Closed

Closed due to lack of feedback

#8 Updated by Damien Smith about 1 year ago

Confirmed issue has been fixed in QGIS 3.

Also available in: Atom PDF