0

I'm feeling like this should work, but I'm banging my head against the keyboard, the documentaion and the google as I can't find any examples to follow for MSSQL.

I have 3rd party data only in an ESRI Personal DB (.MDB). I converted that to MSSQL because I believe there's no way to open that in QGIS.

The ACCESS to SQL conversion ran fine and I can see the tables and data in the SQL Server DB. I setup a User DSN to the DB and when I add a new MSSQL Layer connection, the Test Connection works.

However when I attempt to Connect to the DB in the Add MSSQL Table Dialog I get the following error:

[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'geometry_columns'. QODBC3: Unable to execute statement

Maybe there a schema migration I need to run?

Vince
  • 20,017
  • 15
  • 45
  • 64
w7pea
  • 113
  • 1
  • 10
  • ESRI Personal GDB is directly supported by QGIS through the OGR, no need to convert to MSSQL. – Get Spatial Apr 23 '15 at 00:24
  • 1
    I doubt the geometry type embedded in .MDB compatible with SQL-Server geometry. If you used a pure Access conversion utility, then you're pretty much assured to lose the geometry. – Vince Apr 23 '15 at 01:00
  • @GetSpatial I've heard that and read that, but been able to open the .MDBn with QGIS 2.8.1 Wien on Windows 8.1. I've tried adding it as various layers and pointing to the .mdb directly. I've tried adding the .MDB as a DSN and connecting that way. If you have a pointer to more information or step by step I and others would appreciate it. I should add, with 64bit windows you can only make 32bit MDB file DSNs and they don't work with 64bit QGIS. – w7pea Apr 23 '15 at 05:56
  • 1
    Someone kindly led me to this question that is likely the source of your difficulty, and the solution: Opening Esri Personal GDB using QGIS 2.6 – Get Spatial Apr 24 '15 at 15:18
  • This was it, thank you very much!!!

    Also, sorry for my slow feedback, this is a hobby project for me, but I really appreciate the assistance!

     – w7pea Apr 28 '15 at 19:24

2 Answers2

0

When you set up the connection, you can change whether QGIS reads the geometry_columns table (an OGC standard, I think) or just investigates the database tables when you click connect.

Setting up geometry columns is really important as it speeds up the process a lot. But there's no automatic way to do it. I'll copy the schema for geometry columns below. For now, you can change the check-box that says 'only look in the geometry)_columns metadata table' and it should work.

example SQL Server config

SQL Server geometry_columns table creation.

Definition of columns is:

  • f_table_catalog = database (e.g., GISDB)
  • f_table_schema = schemaa (e.g., dbo)
  • f_table)name = table name (e.g., Buildings)
  • f_geometry_column = geometry column name (e.g., SP_GEOMETRY)
  • coord_dimension = not sure (values are 0, 1 or 2 anyone else help here?)
  • srid = spatial reference id (e.g., 28355_

  • geometry_type = geometry type (LineString, Polygon, MultiPolygon, Point etc.)

    USE [GISDB] GO

    /****** Object: Table [dbo].[geometry_columns] Script Date: 04/23/2015 10:00:24 ******/ SET ANSI_NULLS ON GO

    SET QUOTED_IDENTIFIER ON GO

    SET ANSI_PADDING ON GO

    CREATE TABLE [dbo].[geometry_columns]( [f_table_catalog] varchar NULL, [f_table_schema] varchar NULL, [f_table_name] varchar NULL, [f_geometry_column] varchar NULL, [coord_dimension] [int] NULL, [srid] [int] NULL, [geometry_type] varchar NULL ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF GO

Alex Leith
  • 13,453
  • 30
  • 69
0

The answer to this for 2.6+ (I was using 2.8.1 at the time of this post) is to get the 64bit Access drivers and make some modifications to the batch file that launches QGIS.

This is all covered in this post: Opening Esri Personal Geodatabase (*.mdb) using QGIS?

Thanks to @get-spatial for the find!

Community
  • 1
w7pea
  • 113
  • 1
  • 10