3

I'm on Windows 10 trying to use ogr2ogr to transfer a single non-spatial table from SpatiaLite to SQL Server 2016. (I'm also using this for spatial tables back the other direction (MSSQL->SQLite) successfully with a much larger spatial table and not specifying any MSSQL driver)

I originally used the QGIS 3.6 installation GDAL to do this, however the bulk insert would not be used even if I set the --config MSSQLSPATIAL_USE_BCP TRUE. The insert worked but impractically slowly.

Now I've used the OSGEO4W network installer to express install GDAL 3.3.1 by itself. I use a batch file to run "bin\o4w_env.bat" and tried the following code which gives no error, but instantly finishes and does not insert any rows into the existing MSSQL table.

ogr2ogr -append -update -progress -f "MSSQLSpatial" "MSSQL:server=[server];database=[database];driver={ODBC Driver 17 for SQL Server};trusted_connection=yes;tables=[single existing table]" "[Spatialite Path].sqlite" -sql "[Spatialite SELECT statement]" --config CPL_DEBUG ON --config MSSQLSPATIAL_OGR_FID [MSSQL PK Identity column] --config MSSQLSPATIAL_USE_GEOMETRY_COLUMNS NO --config MSSQLSPATIAL_USE_BCP TRUE --config MSSQLSPATIAL_BCP_SIZE 5000 --config CPL_LOG [Log output path].txt --config CPL_LOG_ERRORS ON

This will also create the table if it doesn't exist, but once again not insert any rows. The log output shows no errors and outputs the following tags: "MSSQLSpatial: Use COPY/BCP: 1" & "OGR_MSSQLSpatial: Using column [...] as FID for table [...]".

This does work (but slowly) if I set the --config MSSQLSPATIAL_USE_BCP FALSE

Things I've Tried:

  1. Following SQL Drivers installed and set as the 'drivers' parameter: SQL Server, SQL Server Native Client 11.0, ODBC Driver 17 for SQL Server
  2. Installing SQL Server BCP Utility 32/64 bit
  3. Advanced Install of GDAL (OSGEO4W installer) with mssql bulk import library and then copying the "bin\gdalplugins\ogr_MSSQLSpatial.dll" into my main install gdalplugins folder. As well as entering the "gdal-mss gdal-mss-3.3.1-1.tar.bz2 0" value into the "\etc\setup\installed.db"
  4. Have full Admin Privileges to SQL server through my Windows login
  5. "ogrinfo --version" shows no errors/missing entry points

After many many hours I'm truly at a loss. How does the BCP/MSSQL driver operate?

Improve this question

1 Answer 1

Reset to default
5

The solution was to download GDAL from GIS Internals as suggested by Jürgen Zornig in this question.

Downloading GDAL from the OSGEO4W network installer, does not seem to activate the SQL Server bulk insert. Custom installing/dropping the ogr_MSSQLSpatial.dll into bin\gdalplugins\ does not rectify the issue. So despite showing up in debug as enabled it was not supported.

Solution steps using binaries(standalone/no installation required):

  1. Click the version you'd like on the GIS Internals and then choose the 'Compiled binaries in a single .zip package' option.
  2. Extract the .zip file and drop the file (root)\bin\gdal\plugins-optional\ogr_MSSQLSpatial.dll into (root)\bin\gdal\plugins
  3. You must have 'SQL Server Native Client 11.0' or later installed
  4. To run GDAL you'll need to run SDKShell.bat in the root folder, or manually set the windows environmental variables instead
  5. Although bulk insert should now default to on, manually set it with --config MSSQLSPATIAL_USE_BCP TRUE
  6. The code posted in my original question now works
Improve this answer
1
  • 1
    The later builds over at GIS Internals are now broken too. The only way I can get BCP to work with ogr2ogr is to use an older GDAL version.
    – Damien
    Commented Apr 28, 2023 at 1:38

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.