4

All,

Can anyone tell me what the error is with OGR2OGR when trying to import a .SHP into MSSQLSPATIAL?

ERROR 1: MSSQL Spatial driver doesn't currently support database creation.
Please create database with the Microsoft SQL Server Client Tools.
MSSQLSpatial driver failed to create MSSQL:server=[]\[];database
=[];uid=[];pwd:[]

The import statement I'm using is this:

ogr2ogr -f MSSQLSpatial "MSSQL:server=[]\[];database=[];uid=[];pwd:[]" C:\[path to file]\police_districts_2877.shp -a_srs EPSG:2877 -lco "GEOM_TYPE=geometry" -lco "GEOM_NAME=geom" -nln "CCD_PoliceDistricts" -progress

As you can see, I'm not trying to create a database, and there is one already created. as per the 'creation issue' cited here: http://www.gdal.org/ogr/drv_mssqlspatial.html

I have followed several posts, including this: Shapefile to MSSQL with ogr2ogr fails to make connection, to no avail...

EDIT: I'm also getting a connection error, but I can't see why...I think my syntax is correct:

ERROR 1: Unable to initialize connection to the server for MSSQL:server=[]\[];database=[];uid=[];pwd:[],[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '[username]'.

Thanks!

-m

  • Have you tried using trusted_connection=yes? – Nathan W Jul 22 '13 at 22:20
  • @NathanW Yes but that won't work because I'm not logged in as the user who has access to our Dev database. – DPSSpatial Jul 22 '13 at 23:01
  • 1
    It sounds like a permission thing if it's trying to create a new database. – Nathan W Jul 22 '13 at 23:07
  • @NathanW why would it be trying to create a new database? What in that string specifies anything other than creating a new table? Got all this info from here: alastaira.wordpress.com/ogr2ogr-patterns-for-sql-server – DPSSpatial Jul 23 '13 at 16:04
5

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?

  • I just can't seem to get this to connect. Silly question for you all, but I'm using SQL 2012 64-bit...would that have an impact on this tool? – DPSSpatial Jul 29 '13 at 19:18
  • So ogrinfo is failing? What is the error message? No, it shouldn't matter what version of SQL Server you're running. – Alastair Aitchison Jul 30 '13 at 17:51
  • I get this with ogrinfo: ERROR 1: Unable to initialize connection to the server for MSSQL:server=[][];database=[];uid=[];pwd:[], [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '[]'. – DPSSpatial Aug 1 '13 at 20:48
  • And also this error: FAILURE: Unable to open datasource `MSSQL:server=[][];database=[] ;uid=[];pwd:[]' with the following drivers. (list of drivers, of which MSSQLSPATIAL is listed...) – DPSSpatial Aug 1 '13 at 20:50
  • Still not solved...but Alastair's answer is what I'll have to go with for now... – DPSSpatial Aug 9 '13 at 19:36
2

I've been having the same issue while migrating an existing script from a test machine (Win7) to a new server (Win Server 2012), therefore I knew the syntax and spelling were correct. On the non-working machine it would say:

ERROR 1: Unable to initialize connection to the server for
MSSQL:server=xxx;database=GIS;Uid=xxx;Pwd=xxx;tables=xxx

Try specifying the driver in the connection string from the list of available drivers:
SQL Server
Microsoft Access Driver (*.mdb, *.accdb)
Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)
Microsoft Access Text Driver (*.txt, *.csv)
ODBC Driver 11 for SQL Server

I then tried, as it says, adding the driver to the connection string. e.g

driver=SQL server;

It now looks like so:

ogrinfo -al "MSSQL:server=xxx;driver=SQL Server;database=GIS;Uid=xxx;Pwd=xxx;tables=xxx"

This is now working on both my machines so hopefully that will help someone else too.

0

I struggled with this too! I managed to solve the problem

ogr2ogr -f MSSQLSpatial "MSSQL:server=Servername\SQL2012;database=DatabaseName;UID= username;PWD=password" C:\Pathto\shape.shp

All you need to do is get rid of the [] and it should work...Hope this is still applicable to you.Just to show you I had the same issue here is a screensht of multiple attempts, until it finally worked...I had to spray out my information though.

enter image description here

  • You have masked the credentials from the command line, but forgot to mask them in the ogr2ogr debug output. – Fabio A. Jul 3 '18 at 12:27
0

I also struggled a lot with this, and then it was just a silly typo: I had to use use pwd= and not pwd: as in Alastair's answer which otherwise is very helpful. Btw, I am using MSSQL 2014 and GDAL 2.1.0

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.