I have made a successful connection with MS SQL Server in QGIS 3. I can see all of the tables. I know with QGIS 2 and PostgreSQL you had to use DB Manager to import a shapefile as a table. Well, DB Manager doesn't have the option for MSSQL... Is there an alternative plugin?

  • To load requires a bit more work - I will post a python script I use – DPSSpatial Dec 18 '18 at 21:31

You can use the in-built browser panel. Just drag and drop a layer (either from your project, or from somewhere else in the browser) onto a schema in the mssql connection to copy the data across.

  • Thank you Nyall! I've spent the last 2 days refining my fme/python/ogr2ogr function when I could have done this. Any which way the fme/python/ogr2ogr logic is still required to resolve my automations – woodwa Dec 16 '20 at 4:09

I've shared this before on this forum, but please have a look at the following and let me know if it works for you. It should only use

import os

#Define input SHP and database parameters:
#location of OSGEO4W:
osgeo4w = 'C:\Program Files\QGIS 3.4\OSGeo4W.bat'
servername = 'servername'
databasename = 'databasename'
sourceshp = 'shp\shapefile_name.shp'
crs_source = '4326'
crs_dest = '4326'
#Define MSSQL Destination Table Name
desttable = 'plangis.temp_pyogr2ogr_trusted'

### Script runs with the above parameters:
#build OGR2OGR statement

#as trusted connection
cmd = 'call "' + osgeo4w + '" ' \
      'ogr2ogr -skipfailures -overwrite -progress -nln ' \
       + desttable + ' -f MSSQLSpatial "MSSQL:driver={SQL Server};server=' \
      + servername +';database=' + databasename + ';trusted_connection=yes" ' \
      + sourceshp + ' -s_srs EPSG:' + crs_source + ' -t_srs EPSG:' + crs_dest + \
      ' -lco geom_name=shape -lco UPLOAD_GEOM_FORMAT=wkt -lco FID=objectid'


#test OGR2OGR statement
print cmd

print "Loading PGSQL table to MSSQL"

#run command

print "Table Loaded"

Basically I have this script sitting in a folder, and whatever SHP I want to load, I put it in a SHP folder at the same level I have the python script (see the SHP/shapefile_name.shp above).

I run the script and its good to go!

Let me know what you think!

  • Thankyou. I have tried a few things with OGR2OGR and ran into a few problems so far. For some reason it isn't liking a certain shapefile. I'll give it a try though. Thanks. – Petey Dec 18 '18 at 21:49
  • I've found more issues trying to build the OGR2OGR statement than I have with the shapefiles - but one thing that people have responded with is that certain fields that usually come with shared shapefiles - that usually come from an eSRI GeoDatabase - is the need to remove fields such as SHAPE_AREA and SHAPE_LENGTH prior to loading with OGR2OGR... just a heads up! – DPSSpatial Dec 18 '18 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.