7

I've got a shapefile which contains, at the moment, roughly 1500 polygons. The polygons all display fine using whatever GIS I throw it in (ArcGIS, QGIS).

However, I'm trying to use ogr2ogr to convert it to a table on SQL Server and it gives the following error:

ERROR 1: INSERT command for new feature failed. [Microsoft][SQL Server Native Client 11.0]Memory allocation failure ERROR 1: Unable to write feature 8 from layer PARCEL_MP. ERROR 1: Terminating translation prematurely after failed translation of layer PARCEL_MP (use -skipfailures to skip errors)

Of course, I can use -skipfailures but when I do, it manages to export roughly 120 of the 1500 records (it differs each time - sometimes it's 115, sometimes 120, a bit more or less).

Obviously what I'm shooting for is 100%. The server is SQL Server 2014.

My code (running this in OSGeo4W) is:

ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=MY_SERVER;database=MY_DATABASE;trusted_connection=yes" "PARCEL_MP.shp"

EDIT - also, the first feature that throws an error changes each time. The shapes aren't complicated and are quite small, with a small number of vertices.

Also, just tried using ogr2ogr to convert it to another shapefile which has worked fine. Think it must be in my server. Code I used was:

ogr2ogr -overwrite -f "ESRI Shapefile" "PARCEL_MP_2.shp" "PARCEL_MP.shp" -skipfailures
Improve this question
5
  • 1
    I just wanted to add to this that I too am experiencing the exact same problem. I know that doesn't help you, but it does show that it isn't an isolated issue. The shp file I'm using had worked previously with the ogr2ogr command. I will be filing a ticket w the GDAL folks.
    – jburn
    Nov 30, 2016 at 1:27
  • I too am having this issue. I'm going from a WFS point feature into SQLExpress. italic bold ogr2ogr -skipfailures -overwrite -f MSSQLSpatial "MSSQL:server=localhost\SQLEXPRESS;database=Import;trusted_connection=yes" wfs:"xxx.com.au/gis/…" Versions:sql server 13.0.4001 and OSgoe4W shell version that comes with QGIS 2.18.3 (GDAL 2.1.2, released 2016/10/24)
    – Colin
    Mar 4, 2017 at 2:04
  • 1
    I was having the same problems and just had success using -lco UPLOAD_GEOM_FORMAT=wkt
    – chris
    May 3, 2017 at 14:40
  • Is it solved now? what did you do to make it work? downgrading to previous versions?
    – Saeed Neamati
    Jun 6, 2017 at 7:16
  • Suggestion by @chris also works for me: ogr2ogr -lco UPLOAD_GEOM_FORMAT=wkt -f MSSQLSpatial "MSSQL:server=XXXXX;database=YYYYY;trusted_connection=yes" "ZZZZZ.geojson"
    – tristanm
    Jun 16, 2017 at 5:19

4 Answers 4

Reset to default
3

I had similar problem, I used Chirs's solution and worked for me -

    ogr2ogr -f MSSQLSpatial -overwrite  -lco UPLOAD_GEOM_FORMAT=wkt "MSSQL:server=XXXXXXX;database=XXXXXX;trusted_connection=yes" -nln "XXXXX" "XXXX.Shp"

I just used "-lco UPLOAD_GEOM_FORMAT=wkt"

Hope this helps !

Improve this answer
2

As @Calanus and @jburn mention, this appears to be a bug (#6589).

ogr2ogr appears to fail when trying to convert to MSSQL Spatial. I have some shp files that I have uploaded previously to SQL Server with a pre 2.0 build and it worked fine. After upgrading to 2.1 I get an error about the native drive having a memory allocation issue. Nothing has changed on my system though and as mentioned, it worked previously with these same files. I have attempted to use the "Driver" variable to specify using just "SQL Server" driver as opposed to the "Native 11" version, but I still get an error.

I get the same issue importing a WFS point feature.

Improve this answer
4
  • Wish that ogr2ogr folks would look into this issue....
    – Calanus
    Apr 20, 2017 at 14:13
  • I agree! I left a comment on the ticket but that did raise any attention. I'm not sure how else to highlight the issue to the org2ogr people!
    – Colin
    Apr 21, 2017 at 6:07
  • What does he mean by pre 2.0 build? I downloaded version 1.3.0.3 from download.osgeo.org/qgis/windows and it does not have ogr2ogr.exe file.
    – Saeed Neamati
    Jun 6, 2017 at 7:27
  • I had success by installing an old version of QGIS (2.10) with ogr2ogr as part of the install. This worked for me. I checked the GDAL ticket and there appears to be no activity against it so I do not think it has been fixed yet.
    – Colin
    Jun 7, 2017 at 22:29
1

This is almost certainly a bug in the recent versions of ogr2ogr as installing a pre-version 2 version imported a 3gb shapefile with no issues

Improve this answer
2
  • Still failing with GDAL 2.1.3, released 2017/20/01
    – ca0v
    Mar 31, 2017 at 15:32
  • Installed download.gisinternals.com/sdk/downloads/… and problem was resolved. It reported a warning which might relate to the failure: Warning 1: Failed to get extent for spatial index.
    – ca0v
    Mar 31, 2017 at 15:51
0

you likely need to repair the geometry of at least one the polygons. You can view them if you have illegal geometry but not convert to a table if you have polygon geo issues.

In ArcGIS use repair geometry.

In Grass use v.clean

Improve this answer
5
  • Just tried that and still having issues. Looking into whether I'm doing it right.
    – user25730
    Aug 1, 2016 at 22:03
  • Nope, still not working. Apparently there are no geometry problems.
    – user25730
    Aug 1, 2016 at 22:05
  • do me a favor. bring it into a file geodatabase feature class and then back to a shapefile. and then try the new shapefile. Something it up with feature 8. so you can just remove this one as well (probably id 7 if it starts at 0) and just do the rest.
    – If you do not know- just GIS
    Aug 1, 2016 at 22:07
  • Still the same issue. And unfortunately I can't afford to remove features, and it changes each time (sometimes it's 8, sometimes 10, sometimes 2). If I use -skipfailures it brings up over 1000 failures in a shapefile of only 1500.
    – user25730
    Aug 1, 2016 at 22:14
  • Just added a bit more info - confident that it's not a problem with the shapefile as a shapefile to shapefile has just worked fine.
    – user25730
    Aug 1, 2016 at 22:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.