2

I am trying to import a plain text file with approx. 8Mio rows without geometry into SQL Server 2016 via the following ogr2ogr command

ogr2ogr -progress -append -nln dgi.te_import -f MSSQLSpatial "MSSQL:server=%dbhost%;Driver=ODBC Driver 17 for SQL Server;database=%db%;uid=%dbuser%;pwd=%dbpw%" -relaxedFieldNameMatch --config MSSQLSPATIAL_USE_BCP YES --config MSSQLSPATIAL_BCP_SIZE 10000 --config GDAL_DRIVER_PATH "C:\Program Files\QGIS 3.16\bin\gdalplugins" --config MSSQLSPATIAL_LIST_ALL_TABLES YES /vsizip/%input_file%.zip\te.txt TurnEdge

The error message is

ERROR 1: SQL Error SQLState=HY000, NativeError=0, Msg=[Microsoft][ODBC Driver 17 for SQL Server]Connection is not enabled for BCP

I`ve downloaded the MSSQLSpatial OGR Plugin from https://download.osgeo.org/osgeo4w/x86_64/release/gdal/gdal-mss/ which should have BCP Support and put the DLL into the QGIS gdalplugins folder. I am using GDAL/OGR 3.1.4 which is bundled with QGIS 3.16.8.

I´ve used the "ODBC Driver 17 for SQL Server" as well as the "SQL Server Native Client 11.0", both with the same result.

I've also fired up a simple bcp ... queryout ... command which worked perfectly well, so i assume the database itself is BCP enabled.

I found quite some advice in the GDAL maillist, but nothing which helped so far...

1
0

BCP probably does not function in OGR2OGR when connecting to MSSQL. In my test in version 3.2.0 and also 3.3.1

When I added:

--config MSSQLSPATIAL_USE_BCP FALSE

The data loaded, if the DATABASE is local, on the same machine, do not feel the slowness.

With the "--debug ON" parameter you see nothing,

In Event Viewer has an error:

Faulting application name: ogr2ogr.exe, version: 0.0.0.0, time stamp: 0x60e9b6a9

Faulting module name: msodbcsql17.dll, version: 2017.177.2.1, time stamp: 0x604133b5

Exception code: 0xc0000005

Fault offset: 0x000000000004a679

Faulting process id: 0x460

Faulting application start time: 0x01d77a09413840db

Faulting application path: C:\OSGeo4W\bin\ogr2ogr.exe

Faulting module path: C:\WINDOWS\system32\msodbcsql17.dll

Report Id: 0b3e780d-42a9-4ab9-9a5e-a774446a2825

Faulting package full name:

Faulting package-relative application ID:

0

Thanks to @ca0v who got me into the right direction, I figured out that neither the ogr Version bundled with QGIS nor the plugin I posted above are able to work together (likely not compiled for BCP support).

The GISInternals Build indeed is bundled with an MSSQLSpatial Plugin and BCP enabled, so its the way to go if you want to Bulk Load via ogr2ogr into MS Sqlserver Spatial.

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.