Writing features to MSSQL server (MSSQLSpatial) fails

[PetrPokorny]

When trying to use GDAL 2.0.0. to import data from shapefile to Microsoft SQL Server 2012, the import fails.

The following command:

ogr2ogr -f "mssqlspatial" -f MSSQLSpatial "MSSQL:server=.;database=Spatial;trusted_connection=yes" ..\Data\boundaries_polygon_with_proj.shp

causes that gdal creates the empty SQL table but fails on the first insert. When debugging this I noticed that the insert command is not shown in SQL server profiler so it looks like it fails when gdal tries to bind the WKB byte array to ODBC parameter.

We tried to reproduce this by attached c# program and we get the following error message:

ERROR 1: INSERT command for new feature failed. [Microsoft][ODBC SQL Server Driver]String data, right truncation

This is 64bit GDAL build, version 2.0.0

[PetrPokorny]

C# application that reproduces the issue

[jaburn]

I'm just wondering if a resolution has been found to this as I am experiencing the same or similar. I had shp files that I used a pre 2.0 build to upload to SQL Server 12 and it worked fine. I've upgraded to GDAL 2.1 and I'm getting the same issue as above. I've tried using the "Driver" variable to use just the SQL Server driver (as opposed to v 11) but I still get an error. If there is a setting or something that I can change, please let me know and I will test it. I'm not that familiar with these build type command applications, so your patience is appreciated. Thanks.

[gregersp]

I am experiencing the same on Windows 2008r2 using ogr2ogr from osgeo (64bit GDAL version 2.1.2).

[tamas]

What about using the MSSQLSPATIAL_USE_BCP=FALSE config option when using the "old" SQL Server driver?

[jaburn]

Replying to tamas: I just gave it a try using SET MSSQLSPATIAL_USE_BCP=FALSE option, but I still get a similar error with only 91 of my 700+ records uploading. I don't think it is the data that is the issue though as this is a set that I was able to upload previously without an issue. The specific error that I get is:

ERROR 1: INSERT command for new feature failed. [Microsoft][SQL Server Native Client 11.0]Memory allocation failure

Thanks.

[tamas]

I'll require some data to reproduce this problem. I've just tried to upload a dataset to MSSQL using the latest binaries from the stable branches from ​http://www.gisinternals.com with no issues (by using the gdal-core.msi installer).

Replying to jaburn:

Replying to tamas: I just gave it a try using SET MSSQLSPATIAL_USE_BCP=FALSE option, but I still get a similar error with only 91 of my 700+ records uploading. I don't think it is the data that is the issue though as this is a set that I was able to upload previously without an issue. The specific error that I get is:

ERROR 1: INSERT command for new feature failed. [Microsoft][SQL Server Native Client 11.0]Memory allocation failure

Thanks.

[gregersp]

Ok, so I managed to get things flowing, though it is weird, it worked once I used the MSSQL 2012 driver (shipped in osgeo4w) - eventhough the server is a 2008R2.

set driver=SQL Server Native Client 11.0

ogr2ogr "MSSQL:server=.;database=DBNAME;trusted_connection=yes;driver=%driver%" %%f -f "MSSQLSpatial" -oo AUTODETECT_SIZE_LIMIT=0 

doing the above with

set driver=SQL Server Native Client 10.0

breaks everything

[jaburn]

Thanks for your reply. I still can't seem to get it to work, but I think it may have to do with some poor understanding of some of the command instructions. This is what I put in. Can you assist me with adjustments? I'm not as familiar with this type of command line coding. My database is a local SLQExpress database (computer name is H-Win7-L44) and the shp file is sitting on my desktop.

Thank you.

set driver=SQL Server Native Client 11.0

ogr2ogr -f MSSQLSpatial "MSSQL:server=H-WIN7-L44\SQLExpress;database=SANDBOX;trusted_connection=yes;driver=SQL Server Native Client 11.0" "c:\users\jburn\desktop\temp02.shp"

[Even Rouault]

This ticket has been automatically closed because Trac is no longer used for GDAL bug tracking, since the project has migrated to GitHub. If you believe this ticket is still valid, you may file it to ​https://github.com/OSGeo/gdal/issues if it is not already reported there.