OGR2OGR from mssql to geopackage

Question

Im trying to export a table from MSSQL to geopackage using ogr2ogr:

This is the exact command I try in windows terminal (but with real serv,db,user,pass ofc):

C:\OSGeo4W64\bin\ogr2ogr.exe -f "GPKG" C:/GIS/data/testdata/test123.gpkg MSSQL:database=somedbname;server=somesrv123;uid=userid;pwd=password;tables=sometable

I get errors:

ERROR 1: Error initializing the metadata tables : [37000][Microsoft][SQL Server Native Client 11.0][SQL Server]CREATE TABLE permission denied in database 'somedbname'.(262)

and

ERROR 1: No column definitions found for table 'sometable', layer not usable.

What am I doing wrong? I can connect to the db in QGIS and ArcMap and add table to the map.

ogrinfo MSSQL:database=somedb;server=somesrv;uid=user;pwd=password;tables=sometable

ERROR 1: Error initializing the metadata tables : [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE TABLE permission denied in database 'somedb'. and ERROR 1: No column definitions found for table 'sometable', layer not usable. INFO: Open of MSSQL:database=somedb;server=somesrv;uid=user;pwd=password;tables=sometable' using driverMSSQLSpatial' successful. 1: sometable

Adding the layer to QGIS and using OGR2OGR from QGIS generate errors:

CMD.EXE was started with the above path as the current directory.

UNC paths are not supported. Defaulting to Windows directory.

ERROR 1: Error initializing the metadata tables : [37000][Microsoft][SQL Server Native Client 11.0][SQL Server]CREATE TABLE permission denied in database 'somedb'.(262)

ERROR 1: No column definitions found for table 'sometable', layer not usable.

ERROR 1: Couldn't fetch requested layer 'someschema.sometable'!

Answer 1

ogr2ogr MSSQL Driver wants to read\create geometry_columns metadata table in you mssql database.

• You can create table geometry_columns and add record for your table to it.

OR

• You can set config parameter MSSQLSPATIAL_USE_GEOMETRY_COLUMNS to NO

ogr2ogr --config MSSQLSPATIAL_USE_GEOMETRY_COLUMNS NO -f GPKG test.gpkg MSSQL:database=somedb;server=somesrv;uid=user;pwd=password;tables=sometable

Answer 2

Try the following.

ogr2ogr --debug ON -sql "select top 100 * from table" -f "GPKG" -a_srs "EPSG:4269" test123.gpkg MSSQL:database=dbname;server=servername;trusted_connection=yes;GeometryFormat=native -nln nameinpackage

NOTE:

table not dbo.table in the sql query -> i have had issues when using prefixes randomly

trusted_connection=yes can be replaced with trusted_connection=no;UID=user;PWD=pwd

GeometryFormat=native if geometry field is a geometry type in sql otherwise use one of: native|wkb|wkt|wkbzm

--debug ON to get debug messages

-a_srs "EPSG:4269" set the EPSG based on SRID for your data