2

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?

Improve this question
8
  • how about omitting the .\ in the MSSQL connection string? – DPSSpatial Oct 28 '16 at 20:09
  • @DPSSpatial hey! I just tried that no luck, same error. – Jordan Davis Oct 28 '16 at 20:11
  • Hmm... then what about using uid and pwd instead of trusted connection? – DPSSpatial Oct 28 '16 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 '16 at 20:21
  • I just tried uid I'm assuming "user id" I've seen some examples that say username though. – Jordan Davis Oct 28 '16 at 20:22
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)

Improve this answer
1
  • Yeah very close to what I ended up using. – Jordan Davis Nov 1 '16 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)

Improve this answer

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.