0

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?

1

2 Answers 2

4

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.

1
  • 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, 2020 at 4:09
1

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
os.system(cmd)

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!

2
  • 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, 2018 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! Dec 18, 2018 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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