Bug report #16247
mssql data provider: filtering layer doesn't work
|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|
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.
#5 Updated by Thomas Schneider over 4 years ago
You need to install SQL Server Express with Tools. A link to the setup in Microsoft download center:
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
-f "MSSQLSpatial" "MSSQL:server=[server name];database=[database name];trusted_connection=yes"
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.
#9 Updated by Giovanni Manghi over 4 years ago
Thomas Schneider wrote:
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 4 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.