4

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...

Improve this question
1

2 Answers 2

Reset to default
2

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.

EDIT: I ended up with a working solution by using

  • GISInternals Core Build from here
  • SQL Server Native Client 11.0, so the connection string should be MSSQL:server=<host>;driver=SQL Server Native Client 11.0;database=<db>;uid=<user>;pwd=<pass>
  • setting --config MSSQLSPATIAL_USE_BCP YES
Improve this answer
4
  • I'm unclear on this solution. Which file needs to be downloaded (there are many)? I picked one and it seemed to install a new gdal even though have it installed already. I launched the new gdal install and ran my command and it still didn't work unless I used --config MSSQLSPATIAL_USE_BCP FALSE as outlined below. The file I'm loading needs the increase in performance with BCP but it seems I can't get it to run under BCP
    – jport
    Commented Jun 20, 2022 at 14:42
  • 1
    @jport I used the core msi (download.gisinternals.com/sdk/downloads/…), as well as the SQL Server Native Client 11.0, so the connection string should be something like MSSQL:server=<host>;driver=SQL Server Native Client 11.0;database=<db>;uid=<user>;pwd=<pass> and you have to add --config MSSQLSPATIAL_USE_BCP YES obviously
    – Jürgen Zornig
    Commented Jun 21, 2022 at 7:53
  • Does the download you specified have to do with your sql server instance? I loaded gdal305 and specified the use BCP and it returned immediately added the table but no records
    – jport
    Commented Jun 21, 2022 at 16:42
  • 1
    @jpod no, 303 was just the most recent one back then...should work with 305 also in my opinion. Are you sure, that your SQL Server Instance is configured to allow BCP? Have you tried to export something with the bcp utility? I assume you are trying to import...SQL Server is quite picky when it comes to schema differences, so perhaps you try to import to a newly created table first.
    – Jürgen Zornig
    Commented Jun 22, 2022 at 12:57
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:

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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