Bug report #16247
mssql data provider: filtering layer doesn't work
| 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.
History
#1
Updated by Andre Jesus over 7 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 7 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 7 years ago
I am using QGIS 2.14.12 and SQL Server 2014.
#4
Updated by Giovanni Manghi over 7 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 7 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 7 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 7 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 7 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 7 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 7 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.