7

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'!

BERA
  • 72,339
  • 13
  • 72
  • 161
  • Does ogrinfo ogr2ogr.exe MSSQL:database=somedbname;server=somesrv123;uid=userid;pwd=password;tables=sometable work as you suppose? – user30184 Feb 02 '20 at 20:51
  • @user30184 i added output to the question. Seems in part successful? – BERA Feb 03 '20 at 08:05
  • 1
    Seems like ogr2ogr wants to read\create geometry_columns metadata table. Does you mssql database contains this table and records in it? Does your user have permissions to read this? – kuzkok Feb 03 '20 at 13:56

2 Answers2

3

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

ogr2ogr --config MSSQLSPATIAL_USE_GEOMETRY_COLUMNS NO -f GPKG test.gpkg MSSQL:database=somedb;server=somesrv;uid=user;pwd=password;tables=sometable
kuzkok
  • 426
  • 3
  • 5
  • Less errors, now I only get: "ERROR 1: No column definitions found for table 'sometable', layer not usable." – BERA Feb 04 '20 at 07:53
  • try to set table parameter with geometry column name https://gdal.org/drivers/vector/mssqlspatial.html#connecting-to-a-database - MSSQL:....tables=sometable(geometryColumn). Are you db in native geometry format? If it opening in arcmap - it can be in legacy arcgis geometry, where shape field contains key of geometry. Can you provide create table script? – kuzkok Feb 04 '20 at 08:56
  • also I have an issue with non-ascii characters in table name - driver not like this. For example any Cyrillic names in sql server profiler displayed like "[ВысотностьЦентр]". You can try to rename table, or build view with ascii name behind this table. – kuzkok Feb 07 '20 at 18:13
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

Diffusion_net
  • 470
  • 4
  • 10
  • I got the table but without geometries, nice! I dont know which geometry format to use, ill try some more. Do you know how to query the db to find out which geomtry type it is? – BERA Feb 10 '20 at 06:58
  • look at the schema of the table. Typically, you want to find out if your geometry is in text(wkt), binary(wkb), or geometry type (native). Try: SELECT * FROM INFORMATION_SCHEMA.COLUMNS where table_name='nameoftable' and respond with what it returns for your geometry column. – Diffusion_net Feb 10 '20 at 18:06