Bug report #16247

mssql data provider: filtering layer doesn't work

Added by Thomas Schneider over 3 years ago. Updated over 3 years ago.

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

Description

Hallo,

MSSQL Layers cannot be filtered at least in QGIS version 2.14. To reproduce: I added a point table (geometry data type, SQL Server 2014). Table has a primary key column (bigint, Not NULL). It was added properly.
When I apply a filter to the layer an error message returned: "Ein Fehler trat beim Ausführen der Abfrage auf. Der Datenanbieter meldet:" (~"An error occurred while running the query. The data provider reports"). There are no error detail shown. See attached screenshot.

QGIS_mssql_filter_issue.jpg (147 KB) Thomas Schneider, 2017-02-28 05:58 AM

History

#1 Updated by Andre Jesus over 3 years ago

Tested with 2.14.12 and 2.18.4 using MSSQL 2008 R2 and MSSQL 2016

The filter worked properly.

#2 Updated by Giovanni Manghi over 3 years ago

  • Category set to Data Provider/MSSQL
  • Status changed from Open to Feedback

The filter worked properly.

do you remember when? 2.8?

#3 Updated by Thomas Schneider over 3 years ago

I am using QGIS 2.14.12 and SQL Server 2014.

#4 Updated by Giovanni Manghi over 3 years ago

sorry for the question: is there any way I can get a "test" copy/installer of mssql server with spatial support (I know nothing about it)?

#5 Updated by Thomas Schneider over 3 years ago

You need to install SQL Server Express with Tools. A link to the setup in Microsoft download center:
https://www.microsoft.com/en-us/download/details.aspx?id=42299
I recommend to download the version "Express with Tools" that includes SQL Server Mangement Studio. After setup, start Management Studio, connect to the server (please take a note of your server name, you need it later) and create a new database.

If it will help, I can post some SQL to create some test tables in the Database. However, a simple solution is to use ogr2ogr to connect to the server and import a shape file. Here a command (broken up into several lines, please concatenate) to import a shape file (epsg:4326) as geometry table to SQL Server

ogr2ogr
-f "MSSQLSpatial" "MSSQL:server=[server name];database=[database name];trusted_connection=yes"
"Path/to/Shape File/Raster/myshapefile.shp"
-s_srs "EPSG:4326"
-t_srs "EPSG:4326"
-overwrite
-lco "GEOM_TYPE=geometry"
-lco "GEOM_NAME=geo4326"

Replace [server name] with your server name eg. MYCOMPUTER/SQLEXPRESS, that you have noted when you connect with Management Studio before, and [database name] with the name of the database you have created before.

Hope that help to create a test environment.

#6 Updated by Andre Jesus over 3 years ago

Giovanni Manghi wrote:

do you remember when? 2.8?

I didn't understand.

@Thomas Schneider
How is your usage with MSSQL layers?
I get constant Force Close.

#7 Updated by Giovanni Manghi over 3 years ago

Andre Jesus wrote:

Giovanni Manghi wrote:

do you remember when? 2.8?

I didn't understand.

forget, I misunderstand your statement.

#8 Updated by Thomas Schneider over 3 years ago

@Thomas Schneider
How is your usage with MSSQL layers?
I get constant Force Close.

Sorry, I didn't understand your question - can you be so kind and give me further details?

#9 Updated by Giovanni Manghi over 3 years ago

Thomas Schneider wrote:

@Thomas Schneider
How is your usage with MSSQL layers?
I get constant Force Close.

Sorry, I didn't understand your question - can you be so kind and give me further details?

there is another ticket where is reported that working with msql layers/projects leads to several crashes. We are looking for confirmations from other mssql users.

#10 Updated by Giovanni Manghi over 3 years ago

  • Resolution set to worksforme
  • Status changed from Feedback to Closed

Got finally a copy of MSSQL server and tried tthis. Used ogr2ogr to load a bunch of OSM data, no problems with filtering using QGIS 2.18.7, which anyway is what should be tested/used now as it will be soon the next LTR.

Also available in: Atom PDF