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:
- Following SQL Drivers installed and set as the 'drivers' parameter: SQL Server, SQL Server Native Client 11.0, ODBC Driver 17 for SQL Server
- Installing SQL Server BCP Utility 32/64 bit
- 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"
- Have full Admin Privileges to SQL server through my Windows login
- "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?
Best Answer
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):
(root)\bin\gdal\plugins-optional\ogr_MSSQLSpatial.dll
into(root)\bin\gdal\plugins
--config MSSQLSPATIAL_USE_BCP TRUE