QGIS now with 100% more MS SQL Server 2008 support

Posted on by Nathan

Ok the title is a bit of a lie. QGIS did support MS SQL Server 2008 before by using OGR but this is a native provider so it’s a lot more integrated..

Good news everyone!

QGIS now has a native MS SQL 2008 provider. The provider can found using the new toolbar button (purple icon) or in the MS SQL node in the QBrowser tree. The provider also supports drag and drop import.

The work was sponsored by Digital Mapping Solutions (Australia) and completed by Tamas Szekeres

Any bugs can be assigned to “tamas” on hub.qgis.org.

A big thanks to both Digital Mapping Solutions and Tamas.

This addition will open QGIS up to a whole new set of users who have to use MS SQL but love QGIS.

Currently this is only in master but I will be in the 1.8 release when it comes out.

Note: At the moment you have to have a geometry_columns table in the database in order to connect, this table is the same format used by PostGIS and can be made by importing a layer using the ogr2ogr method. There will be a fix coming for this at some stage.

20 thoughts on “QGIS now with 100% more MS SQL Server 2008 support”

    1. Yeah it was a bit of a PR thing to grab attention. QGIS did support SQL Server 2008 using the OGR driver but this is a native provider. The native provider gives speeds improvements and better integration into QGIS, things like drag and drop import. The OGR driver is still very file orientated.

  2. Hi Nathan

    This is fantastic news! Any way to get this before QGIS version 1.8? What’s the ETA on 1.8? Desperately need to get my hands on this!

    Cheers,
    C

  3. Great news Nathan! Been anxiously waiting for this for some time. What’s the ETA on its release? any chance of getting an advanced copy?… I have some pressing projects/timelines that this support would drastically effect.

    Cheers!
    C

  5. Finally!!! We can delete feature in MSSQL database… Tried in new 1.7.4 version. I guess this is because of new OGR driver 1.9 which came with new version…
    Now, when QGIS is fully qualified, I have to think again about my project… but this is sweet problems

  6. 1.9.90, native driver… 35.000 features (parcels) loaded in less than 2 seconds!! Great improvement, nice looking GUI… started to like pink color of DB can :)

  7. Question guys. I have SQL Server layers in QGIS now, but I can’t query them from within the layer properties menu. Any idea why? and where would I ask such a question in the future?

    thanks!
    C

  8. Hey Nathan…Trying to add a couple tickets for other things I’ve found but can’t seem to find a place to register at http://hub.qgis.org/. However it may be that these are not so much tickets as technical issues on my side. Any assistance would be greatly appreciated!

    Others I’ve found:
    – no primary key showing in the Add MSSQL Table window. Also there is no ability to manually select a primary key (primary keys are defined in my system so not sure why QGIS is not picking them up.)
    -Query Builder functionality missing within the Add MSSQL table window and the Layer Properties menu
    -Toggling into Edit mode and change metadata for a feature works great. However creating a new feature does not work and upon saving the feature is lost. (this may be a consequence of the primary key for the table not being recognized).

    thanks in advance!
    C

    1. Sorry your first comment got sent to spam for some reason and I was never told.

      Can you try one of the nightly builds again, there has been some fixes for the primary key detection.

      At the moment there is a bug that if the database rejects a insert statement then the MS SQL driver will just delete it without telling you, I have filled a ticket for this and it should be getting fixed soon.

      You can create a new login at https://www2.osgeo.org/cgi-bin/ldap_create_user.py and then log into hub.qgis.org using the Sign In link at the top right hand corner.

      1. Thank for the info Nathan. Just downloaded the latest build (115), and still have a problem with the primary keys, adding new features, and looks like the query builder is still being worked on as I still receive an error when trying to create a query on import. Let me know if it’s just me or the driver. I’ll post a bug report once I hear back from you. Hoping the query builder is up and going soon…have a project which would be much easier with this up and working. Big Thanks to the team who put the driver together!
        C

  9. Hi there,

    With the new MSSQL dialog to add tables qgis sends a very time consuming query to the DB to work out its geometry type…this seems to take forever on large tables…is there anyway to stop this? Also…rookie question why will it only support one geometry type by table…this is not a limitation of msSQL…I thought only ESRI worked that way? any help gratefully received…

    cheers

    Paul

    1. If you have a table called geometry_columns it will skip having to calculate the geometry for each table. You can see an example of the table and how to create one on this post: look at the bit talking about uploading via ogr:

      ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=.\MSSQLSERVER2008;database=geodb;trusted_connection=yes" "rivers.tab"

      Once you have a geometry_columns table you can add the table twice with different object types. QGIS can then open the layer as points, or lines. QGIS is limited to only having one data type per layer, so if you table has points and lines you will have to add it twice. It seems like a silly limitation but I find it makes it easier for people to understand, plus it makes operations a lot easier. MapInfo can mix data types in one layer and it just makes things a lot more complicated.

  10. Hi Nathan,
    thanks for your reply..I understand now…Q-GIS requires a ref table set up similar to Postgis…sort of… Might be good idea to provide just the required SQL to build the two tables for those who use other methods to load data in to SQL and dont want to start using OGR…but thats just an idea…thanks again

    p

    1. This is a really good idea. I’m just looking for these queries and can not find. I have all my data in MSSQL and I just want to use it in QGIS.
      Maybe somebody can help me?
      Thanks.

  11. We have upgraded to SQL Server 2014 to leverage some of the much needed new geospatial functionality. But, we discovered that it seems to have broken QGIS. Is this correct? If so, are there plans to support the newer version?

Leave a reply to Nathan Cancel reply