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

Improve this question
3
  • Does ogrinfo ogr2ogr.exe MSSQL:database=somedbname;server=somesrv123;uid=userid;pwd=password;tables=sometable work as you suppose? – user30184 Feb 2 '20 at 20:51
  • @user30184 i added output to the question. Seems in part successful? – BERA Feb 3 '20 at 8: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 3 '20 at 13:56
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
Improve this answer
3
  • Less errors, now I only get: "ERROR 1: No column definitions found for table 'sometable', layer not usable." – BERA Feb 4 '20 at 7:53
  • try to set table parameter with geometry column name gdal.org/drivers/vector/… - 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 4 '20 at 8: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 7 '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

Improve this answer
2
  • 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 6: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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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