Bug report #16718

QGIS MSSQL Layer creation error in OSX

Added by Jerome Anthony about 3 years ago. Updated over 1 year ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/MSSQL
Affected QGIS version:2.18.1 Regression?:No
Operating System:OSX macOS Sierra Easy fix?:No
Pull Request or Patch supplied:No Resolution:not reproducable
Crashes QGIS or corrupts data:No Copied to github as #:24617

Description

Hi I get the following error when I try to create a MSSQL layer in QGIS on OSX (mac).

Error opening connection: Driver not loaded Driver not loaded


Related issues

Related to QGIS Application - Bug report #16716: QGIS MSSQL connection creation issue Closed 2017-06-16

History

#1 Updated by Giovanni Manghi about 3 years ago

  • Status changed from Open to Feedback

is this to report the same kind of issue as #16716?

#2 Updated by Jürgen Fischer about 3 years ago

#3 Updated by Jerome Anthony about 3 years ago

Giovanni Manghi wrote:

is this to report the same kind of issue as #16716?

Is there anything you guys want from my side? This works fine on Windows.

#4 Updated by Giovanni Manghi about 3 years ago

Jerome Anthony wrote:

Giovanni Manghi wrote:

is this to report the same kind of issue as #16716?

Is there anything you guys want from my side? This works fine on Windows.

likely (not 100% sure) this means that some MS library is needed, and maybe this is only available for Windows and then only shipped with QGIS for Windows.

Interestngly enough GDAL/OGR has supports for MSSQL server, and this should also work out of the box on non Windows OSes, this is for example what my linux box says:

[email protected]:~$ ogrinfo --formats | grep SQL
SQLite vector- (rw+v): SQLite / Spatialite
MSSQLSpatial vector- (rw+): Microsoft SQL Server Spatial Database
PostgreSQL vector- (rw+): PostgreSQL/PostGIS
MySQL vector- (rw+): MySQL
PGDUMP vector- (w+v): PostgreSQL SQL dump

#5 Updated by Chid Gilovitz almost 3 years ago

I have the same issue, as does a colleague, although I'm not sure if the cause is identical because it's a very general error message. I'm on MacOS 10.12.6.

In addition, on clicking 'List Databases' while attempting to add a MSSQL connection, I get the following error message:

[iODBC][Driver Manager]dlopen({FreeTDS}, 6): image not found [iODBC][Driver Manager]Specified driver could not be loaded QODBC3: Unable to connect

It's interesting that 'List Databases' gives a different error message to 'Test Connection', which just says
Driver not loaded Driver not loaded
.
Following this up on the iODBC site (http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/FAQ#%5BiODBC%5D%20%5BDriver%20Manager%5DSpecified%20driver%20could%20not%20be%20loaded) suggests that iODBC cannot find the .so file needed for the driver. I have attempted to fix this problem by reinstalling FreeTDS and ensuring the driver file is where it should be and there is an odbcinst.ini file that points to it. This didn't work.

There is some more information about this here https://github.com/Homebrew/legacy-homebrew/issues/24550. It appears that recent versions of OS X/MacOS do not come the necessary iODBC headers.

Any help on this would be greatly appreciated - I suspect the problem is common amongst Mac users however we probably do not often have a need to connect to MSSQL databases so it goes unreported.

#6 Updated by Giovanni Manghi almost 3 years ago

Chid Gilovitz wrote:

I have the same issue, as does a colleague, although I'm not sure if the cause is identical because it's a very general error message. I'm on MacOS 10.12.6.

are you using the native MSSQL provider (layer > add layer > add MSSQL Spatial Layer)?

#7 Updated by Chid Gilovitz almost 3 years ago

Giovanni Manghi wrote:

are you using the native MSSQL provider (layer > add layer > add MSSQL Spatial Layer)?

Apologies for not specifying. Yes.

#8 Updated by Giovanni Manghi almost 3 years ago

  • Status changed from Feedback to Open

#9 Updated by Mick Collopy over 2 years ago

I have no trouble at all from windows desktop - not it is on localhost, but evidently the native provider loads the driver.
However on Linux (Ubuntu) no matter what I try with ODBC, FfreeTDS, iODBC, although the server can connect, QGIS cannot load the driver.
Not sure whether the issue is the provider or the driver.

This post suggest may the linux distros are not compiled with the driver
http://osgeo-org.1560.x6.nabble.com/QGIS-MSSQL-2008-Data-import-problem-tp4986449p4987086.html
Is this the missing piece of the puzzle? Does this trigger something for someone else?

#10 Updated by Jürgen Fischer over 2 years ago

Mick Collopy wrote:

This post suggest may the linux distros are not compiled with the driver
http://osgeo-org.1560.x6.nabble.com/QGIS-MSSQL-2008-Data-import-problem-tp4986449p4987086.html
Is this the missing piece of the puzzle? Does this trigger something for someone else?

Did you install libqt4-sql-tds / libqt5sql5-tds (debian/ubuntu - might be called differently on your distribution)

#11 Updated by Giovanni Manghi over 2 years ago

  • Status changed from Open to Feedback

#12 Updated by Giovanni Manghi almost 2 years ago

  • Status changed from Feedback to Closed
  • Resolution set to not reproducable

Closing for lack of feedback.

#13 Updated by Mick Collopy over 1 year ago

Still getting the same issue after upgrading to 3.4
Yes, both libqt4-sql-tds / libqt5sql5-tds are installed.
All conncetion tests are successful from the command line to test
a) FreeTDS using tsql
b) ODBC using isql
Same error message "Error opening connection: Driver not loaded Driver not loaded"

What should be the settings for these?
Connection Name
Provider/DSN
Host

#14 Updated by Mick Collopy over 1 year ago

I found a workaround - I can import the data using add Vector layer, database
- finally got a query to work using FreeTDS on Linux, where the 'database' name had to be entered as the 'servername' in /etc/odbc.ini
- from there I can extract point data using Processing Toolbox, Qgis Geoalgorithms -> Vector Creation Tools ->points layer from table -> select x and y

However trying to use 'MSSQL Spatial Layer' still doesn't work, same error message 'driver not loaded'

#15 Updated by Nikolay Lebedev over 1 year ago

Same issue here. Any news?

Also available in: Atom PDF