5

When trying to load a .SHP into MSSQLSpatial using OGR2OGR, I get the following error:

ERROR 1: Error initializing the metadata tables : [Microsoft][ODBC SQL Server Dr
iver][SQL Server]There is already an object named 'geometry_columns' in the data
base.

I can see the entries using this query:

SELECT *
FROM giswork.INFORMATION_SCHEMA.COLUMNS
where table_name like 'geometry%'

And the result looks like this:

enter image description here

And If I dig deeper with this query:

select * from matthew_baker.geometry_columns

I get a set of empty results:

enter image description here

My questions:

  • What should be in the geometry_columns table (if it's not there already...)
  • Why does og2ogr throw the error about the geometry_columns table already being created?

EDIT: It seems that GDAL created these tables when I tried to use the OGR2OGR command - they have not been in my database until now. My current spatial data was created using FME and is updated with FME.

I can view AND edit my MSSQLSpatial tables in QGIS 2.10 without any issues, across multiple schemas in my database.

4

I have stumbled on this issue lately, and if you set the MSSQLSPATIAL_USE_GEOMETRY_COLUMNS environment variable to NO, then the error message disappears. Related documentation here.

Hope it helps somebody later.

  • Thanks - do you put that in the connection string or outside? Still getting the error in both cases unfortunately... – DPSSpatial Dec 6 '16 at 15:38
  • Add it as an environment variable, not as part of the connection string. On Windows e.g. kb.wisc.edu/cae/page.php?id=24500 – Benedek Simo Dec 7 '16 at 10:14
  • Does that have to occur on the machine the server resides on? or the Client machine? – DPSSpatial Dec 7 '16 at 16:09
  • It has to be on the machine where you run the command. – Benedek Simo Dec 8 '16 at 8:40
  • Aargh still not working - set the environment variable as both a user and system variable... even restarted... still getting the error... Maybe I'll test to see if it needs to be set on the server... – DPSSpatial Dec 8 '16 at 16:46
2

I have a partial answer to part 2 of your question 'Why does ogr2ogr throw the error about the geometry_columns table already being created?'

I'm performing a .SHP into MSSQLSpatial using OGR2OGR and also getting the

ERROR 1: Error initializing the metadata tables : [Microsoft][ODBC SQL Server Dr
iver][SQL Server]There is already an object named 'geometry_columns' in the data
base.

The MSSQL database I'm loading to also has SDE on it. There is no table called geometry_columns but there is a table called sde_geometry_colums and a stored procedure name geometry_columns. My guess is that ogr2ogr is checking whether a table called geometry_columns exists and if it doesn't then create it. So it's not finding the geometry_columns table and then trying to create the geometry_columns table. Because there is an object (the stored proc) called geometry_columns an error will be thrown when ogr2ogr tries to create a table of the same name. I tested this by renaming the stored proc and re-running ogr2ogr and I no longer get the error. I'm performing all this on a test database and I don't know what the implications are for SDE by renaming this stored proc...

  • Thanks for the info... by the way, is there a reason you're loading spatial data into the back-end of an ArcSDE database rather than as an SDE feature class through the 'front end'? – DPSSpatial Dec 5 '17 at 19:56
  • I've got a separate machine with QGIS and MSSQL spatial, and I'm trying to create a workflow that doesn't involve any ESRI software. I was having some data loading issues on this machine and thought I'd try the ogr2ogr on my machine with SDE on it to see if I could resolve the issue there (by exporting the data out of sde and reloading through ogr2ogr as the data had already passed through the load process using ArcGIS front-end), and that's how stumbled on this error. – user3436711 Dec 6 '17 at 9:13
  • Ahhh! Good stuff. We're in the same boat, but use PostGIS as the 'source' of the spatial data, and use python to move the data to mssql sever - otherwise, going straight from QGIS To MSSQL is still not working for us... – DPSSpatial Dec 6 '17 at 13:25

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.