ESRI Shapefile not loading into SQL Server

Question

I have been loading a number of shapefiles into SQL server 2012 using ogr2ogr.exe and the MSSQLSpatial driver. Up until this point, I have been very successful with doing so.

However, I have now run into difficulty with certain files and the errors thrown by various tools are not terribly helpful in identifying what exactly is wrong with the shapefile.

The files in questions are between 270MB and 290MB big and most have a single layer with more than 80,000 features in that layer. However, size and feature count is not itself an issue, I believe, as a file that is part of this data set is 280MB big and has 111,000(ish) features in it's particular layer.

The problem files all load correctly and are viewable in QGIS, including any files/features thrown out by ogr2ogr.exe as causing an issue. They also load into MapInfo successfully and can be edited there just fine. Re-exporting the layer from QGIS as a shapefile does not fix the issue with loading via ogr2ogr.exe

I've also attempted to load it using Shape2SQL and that program just hangs outright after it reaches a certain point with no error messages thrown at all.

The specific message thrown by ogr2ogr.exe is:

ERROR 1: INSERT command for new feature failed. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).

ERROR 1: Unable to write feature < feature no> from layer < layer name>.

ERROR 1: Terminating translation prematurely after failed translation of layer < layer name> (use -skipfailures to skip errors)

The command line statement for ogr2ogr.exe I'm using is:

ogr2ogr.exe -overwrite -f MSSQLSpatial "MSSQL:server=...;database=...;trusted_connection=yes" "< filename>.shp" -t_srs "EPSG:4326" -lco "GEOM_TYPE=geography" -lco "SCHEMA=ETL" -lco "OVERWRITE=YES" -progress

The shapefiles are in WGS84 projection.

Does anyone know what tool I might be able to use from the GDAL toolkit, QGIS or MapInfo to fix the errors in these shapefiles so that they can be loaded by ogr2ogr.exe into SQL Server 2012? Or alternately is it my ogr2ogr.exe arguments?

Update:

I've managed to run the Vertices Counter plugin and the first feature that causes an issue has 5,558,760 vertices.

• Peter Horsbøll Møller: It could be basic issues with the spatial data such as very short lines, very small polygons or records without spatial data. Could you run a query on the dataset to see if it contains small features? Try having a look at the feature referred to in the error message
• toadflakz: The first feature that it crashes out on is quite large. I'm not sure how to view details of features in QGIS but I'll investigate and update with an edit.
• karl: That's a lot of vertices - I don't know if ogr2ogr or SQL would run into an issue with that. Maybe try running it with -skipfailures and see what happens - you should get a set of features imported, and you can compare that to the full set to see if the number of vertices, or something else, is a common feature of the skipped features.

Answer 1

This worked for me in the ogr shell.

First change your directory to the folder that contains your shapefile (cd full path to shapefile folder)

ogr2ogr -overwrite -f "MSSQLSpatial" "MSSQL:server=...;database=...;trusted_connection=yes" test.shp -a_srs "EPSG:4326" -nln "test"

• toadflakz: This did not work.Same error as before unfortunately.
• Rita A.: Do you have permission to write to the SQL server?
• toadflakz: Yes. As stated in the question, I have been using this method on a number of other files to this specific database and server without issues up until now. I think the issue is with the file, not the server. I'm looking for a way to diagnose why it's not loading.

Answer 2

I eventually gave up trying to load the large files - it does not seem to be possible with any tool. The other side of the coin was that the features I did manage to load took over 1 hr to do a simple STIntersects query.

Solution:

I cut up the large shapefiles using ogr2ogr based on county geometries and then uploaded them and associated them with the county data in order to create a smaller set of geography objects to query.

This method worked like a charm and STIntersects query performance for the most complex shapefile is still in the region of 600ms which is acceptable for our purposes.

Answer 3

I resolved with the command: -FieldTypeToString All

The problem in my case was a column with numeric conversion problem. So I turned all columns (except for the geography, of course), and it resolved.

Answer 4

for me resolved by adding --config MSSQLSPATIAL_USE_BCP NO

Answer 5

Want to improve this post? Provide detailed answers to this question, including citations and an explanation of why your answer is correct. Answers without enough detail may be edited or deleted.

I used to have the same problem, the fastest way was to use this tool.

http://www.dknezevic.com/blog/2014/10/import-shapefiles-into-sql-server.html

It's quick and easy to work with.

• Alexander Abakumov: Unfortunately, the link is dead now.
• bendecko: site has been taken down yes. Archive has a copy. web.archive.org/web/20160909033853/https://dknezevic.com/blog/… It references a GitHub project which remains up although not maintained for some time github.com/dknezevic/ShpToSqlServer

Answer 6

I found that I had this error with a map of the administrative areas of the world.

The solution that worked for me was to downgrade from QGIS 2.18 (GDAL Version 2.1.1) to GGIS 2.12 (GDAL Version 1.11.3). Then it just worked fine.