0

I have trouble adding new layers to the existing MS SQL Server database. Whenever I try to add any type of spatial layer I encounter an error, as follows:

[Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'geometry_columns' in the database. QODBC3: Could not complete command

I have tried manually deleting the geometry_columns table, but it also generates other error, so I am not able to do it. I have tried to establishing connection both with and without option "check only geometry_columns table". Also I've managed to add layers using OSGeo4W Shell command with ogr2ogr so it seems that only problem is within GUI of QGIS. Is there a good way to baypass this error or some kind of workaround?

As it is shown on the screenshot below I have geometry_columns table inside DB user schema:

Database schema

Side note: The database itself was created with ArcCatalog, but now I need it to work also with QGIS

Maciej_Szypulski
  • 21
  • 5
  • You shouldn't be creating or dropping this table, just insering/deleting rows. – Vince Aug 22 '22 at 13:30
  • Yes, I am aware of that, but whenever I try to import shapefile from project via drag&drop to the database I encounter this error, I am unable to display or edit geometry_columns table. When I try to display this table I have following error:

    [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'XXXXX\15421214'. [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AND'. QODBC3: Could not complete command

     – Maciej_Szypulski Aug 22 '22 at 14:05

1 Answers1

1

I found out that for some reason MS SQL DB automatically tries to create the geometry_columns and spatial_ref_sys tables (with the wrong create table statement). Actually deleting those tables in the users schema and creating them manually in "dbo" schema was a way to go.

Now QGIS works fine with the DB I can drag&drop layers without any errors and automatically adds layer info into those tables.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
Maciej_Szypulski
  • 21
  • 5