More

What if 'geometry_columns' already exists in MSSQL Server Database?


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:

And If I dig deeper with this query:

select * from matthew_baker.geometry_columns

I get a set of empty results:

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.


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.


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…


If you don't want to use environment variables (or dont have permissions) adding--config MSSQLSPATIAL_USE_GEOMETRY_COLUMNS NOto the command has solved this issue for me.


Watch the video: Solved There is already an object named AspNetRoles in the database. (October 2021).