[GIS] Import shapefile in SQL Server (OGR2OGR)

ogr2ogrsql server

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?

Best Answer

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)

Related Solutions

Convert Shapefile to MSSQL with Ogr2ogr – Connection Error Fix

The ODBC documentation suggests keywords UID and PWD instead of User ID and Password.

[GIS] Why does ogr2ogr SHP import error out trying to create a database

From past experience, I wouldn't trust that error message to strictly relate to database creation - I would treat it as just implying that there is a permissions issue of sorts with that connection string.

The first thing I'd do is use ogrinfo to check whether a connection can be made at all to that server/db using those credentials. Try running the following command (inserting your details into the placeholders as before):

ogrinfo "MSSQL:server=[]\[];database=[];uid=[];pwd=[]"

You should get a success message similar to "INFO: Open of MSSQL:server=...;database=...;uid=...;pwd=...' using driverMSSQLSpatial' successful. If you don't get a successful connection, you need to double-check basic things - is the SQLServer configured to allow remote connections, is there a firewall blocking access, are there any typos in the server/db/uid/pwd?

Assuming that you can connect ok to the database, the next thing to do would be to check the permissions of the user that you are using to connect. Although you correctly point out that you are not creating a new database, you are creating a new table (via the -nln option) - and OGR2OGR also creates two additional metadata tables ("geometry_columns" and "spatial_ref_sys") if they don't already exist in the target database - does the user have permission to create tables?

Best Answer

Related Solutions

Convert Shapefile to MSSQL with Ogr2ogr – Connection Error Fix

[GIS] Why does ogr2ogr SHP import error out trying to create a database

Related Question