4

I'm importing a shapefile into SQL Server '12 via the OGR2OGR tool and am getting a database connection error.

I followed the example from this article.

What I ran:

ogr2ogr -overwrite -f ESRI Shapefile "MSSQL:Server=.\MSSQLSERVER2012;database=ap;trusted_connection=yes" "C:\Users\johndoe\Desktop\myshapefile.shp"

The error:

SQL Server does not exist or access denied

I'm assuming I need to pass some type of credentials? and the server string is probably wrong where would it be located.

Question: Why am I getting this error?

8
  • how about omitting the .\ in the MSSQL connection string? Oct 28, 2016 at 20:09
  • @DPSSpatial hey! I just tried that no luck, same error. Oct 28, 2016 at 20:11
  • Hmm... then what about using uid and pwd instead of trusted connection? Oct 28, 2016 at 20:13
  • First thing to correct is the value of -f that is telling the outputformat. In your case it should point to SQL server.
    – user30184
    Oct 28, 2016 at 20:21
  • I just tried uid I'm assuming "user id" I've seen some examples that say username though. Oct 28, 2016 at 20:22

3 Answers 3

3

One thing that I found was that I was not able to upload shapefiles directly to an MSSQL instance that was on a separate server; my workaround was to download sql express to my local hard drive, do the OGR2OGR script there, then use SQL Server Management studio to import the data from my local computer to the server on the network domain. My syntax was (from the command prompt in the directory that ogr2ogr.exe is in):

ogr2ogr -progress -f "MSSQLSpatial" "MSSQL:server=(your server name here);database=(database name on server to publish shapefile to);trusted_connection=yes" "(directory and actual name of shapefile with .shp extension)" -a_srs "(the ESPG projection code of the shapefile)" -lco PRECISION=NO

Note: the "trusted connection" parameter is used when you are using windows authentication (e.g., when you are on a domain network) as MSSQL login credentials. You may want to check if your mssql server is set up to accept forms of login other than username/pw (you may need to install another instance of sql server; the only place I saw that option was when I ran the original setup.exe)

1
  • Yeah very close to what I ended up using. Nov 1, 2016 at 17:07
1

I've managed to import successfully on remote server. Here goes procedure

  1. Install QGIS Desktop 3.10.5..or whatever version is actual.
  2. How to find out what goes to parameter -a_srs, in my casa that is : -a_srs "EPSG:3765":

Open QGIS Desktop 3.10.5, open new project and drag-drop your shape file to Layers left sidebar. Right click on that layer and go to Properties. There you will be able to read your -a_srs information.

enter image description here

  1. Run command in CMD:

    C:\Program Files\QGIS 3.10\bin>ogr2ogr -progress -f "MSSQLSpatial" "MSSQL:server=xxx.xxx.xxx.xxx,yyyy;database=yourDB;UID=yourUsername;PWD=yourPassword" "C:\Users\Username\Desktop\ShapeFiles\Name_of_shape_file.shp" -a_srs "EPSG:3765" -lco PRECISION=NO

Output: 0...10...20...30...40...50...Warning 1: Ring Self-intersection at or near point 664451.64629999956 5053409.8562000087 60...70...80...90...100 - done.

Where xxx.xxx.xxx.xxx - IP address of your server, and

yyyy - port number of your server if you are running SQL Server on different port than standard (I think you can omit that in case you are)

0

To upload a shapefile to a local or remote Microsoft SQL Server database (or Azure SQL) using GDAL's ogr2ogr, open a new command prompt and use this:

ogr2ogr -f "MSSQLSpatial" "MSSQL:server=your_server_url,1433;database=your_database_name;uid=your_username;pwd=xxxxxxx" -lco SCHEMA=dbo -nln new_table_name "C:\path_to_file\shapefile.shp" -nlt POLYGON -lco GEOMETRY_NAME=geometry_column_name? -a_srs "EPSG:27700" -overwrite -progress -lco OVERWRITE=YES

Note that the default port for SQL Server databases is 1433. Also I have set the desitination schema as the default 'dbo' schema. As I am working with data within the British National Grid projection, I am using the EPSG/SRID of 27700. In my example I am uploading a polygon shapefile, so I set the -nlt flag to POLYGON. You can change all of these parameters to match your setup.

If you are a python user, you can pass the ogr2ogr command into a python variable and use subprocess to call it. It is much easier to modify GDAL processes within a script than within a cmd prompt:

import os
from subprocess import call

os.environ['PROJ_LIB'] = r'C:\Program Files\GDAL\projlib'  # not essential

in_file = r'C:\path_to_file\shapefile.shp'
out_schema = 'dbo'
out_nln = 'new_table_name'
out_gname = 'shape'
out_gtype = 'POLYGON'

host = 'your_server_url'
port = '1433'
db = 'your_database_name'
user = 'your_username'
pw = 'xxxxxxx'
command = fr'ogr2ogr -f "MSSQLSpatial" "MSSQL:server={host},{port};database={db};uid={user};pwd={pw}" -lco SCHEMA={out_schema} -nln {out_nln} "{in_file}" -nlt {out_gtype} -lco GEOMETRY_NAME={out_gname} -a_srs "EPSG:27700" -overwrite -progress -lco OVERWRITE=YES'

print(command)
call(command)

I find it useful to print the ogr2ogr command out in the python console so I can quickly copy it for use in command line if I so desired. In this example I 'f' string the variables (ie.{variable}), but you may wish to %s string them instead.

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.