10

OK first lets get the blond elements out of the way- I'm not asking for how I connect to my actual database.
What I'm asking for is an example or screen shot of the entries required when setting up a new connection.

Nothing I try to do to recreate either my 32 or 64 bit ODBC's is seeming to work and test connection just returns

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified QODBC3: Unable to connect

Something along the lines ofCreate a New MSSQL Connection

Given that the help files at this point are less than helpful!help me
And I've scoured the documentation for instructions in Lisboa, Dufour and Valmeira and the web all to no avail.
It should be so simple but I can't find anything- presumably because everyone thinks it should be so simple!

Pretty please- yes call me a muppet if you want- but this is driving me stark staring bonkers- Can any one help?

Alternatively how the &$^% do I find out more about the QODBC3 Drivers?

because it is rather irrelevant in this case as I've tried it from multiple configurations but nonetheless QGIS Affected 2.0, 2.2
SQL server: 2008 R2
User environment: Win7 64 and 32 bit tested

Improve this question
5
  • Which Version of QGIS are you using?
    – Mapperz ♦
    Commented Apr 9, 2014 at 17:26
  • 1
    See gis.stackexchange.com/questions/80610/…
    – Mapperz ♦
    Commented Apr 9, 2014 at 17:32
  • Provider/DSN parameters not needed if you have a host.
    – Mapperz ♦
    Commented Apr 9, 2014 at 17:34
  • Mapperz- thanks but no those don't answer the issue- the issue is with both 2.2 Valmeira and 2.0 Dufour
    – Richard Colwill
    Commented Apr 10, 2014 at 8:48
  • If the user leave the PROVIDER\DSN: Box blank... the ok button is not active, so the user is forced to fill it up, in order for the OK button to be active... which is confusing to his/her part.. user he/she no idea what word he/she might type the text box!!!
    – ian
    Commented Aug 6, 2020 at 6:24

3 Answers 3

Reset to default
12

In 2.16... (some really basic information I struggled to find and a note about an unhelpful error message)

The 'Connection Name' is a name for our own use.

The 'Provider/DSN' field can be left blank.

The 'Host' is a DNS name for the server or an IP address and specific port if not standard SQL port followed by instance name where required (eg. localhost\sqlexpress) (sorry - I don't personally undersand this bit - I needed help from the person who set up the database to describe this)

The 'Username' and 'Password' are as you'd expect. The 'save' checkboxes are to tell QGIS to remember these details.

Don't at this point click the 'Test Connection' button. You'll get an error "Error opening connection: Driver not loaded Driver not loaded"

Instead, click the button 'List Databases'

You should see the database listed.

Improve this answer
2
  • In QGIS 2.18.0 I am not able to open MS SQL Server 8 tables. I am able to connect and list all databases on the connection dialog. Afterwards, when I try to connect, I get: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sys.columns'. [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sys.types'. [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sys.objects'. (...) QODBC3: Unable to execute statement
    – jgrocha
    Commented Nov 11, 2016 at 11:46
  • Despite 'Test Connection' being located below 'List Databases', I too tried to test the connection before listing databases and got the Driver not loaded error. Perhaps a slight UI change will improve the experience for first-timers.
    – PeterS
    Commented Sep 11, 2017 at 9:44
6

The issue 'QODBC3: Unable to connect' may be due to the configuration on the SQL server. Have your IT Admin 'enable' the Protocols TCP/IP and NamedPipe services for the database instance you are trying to connect to. This is performed in the SQL Server Configuration manager > Network Configuration . Also ensure the IP addresses have TCP Port = 1433 and the TCP Dynamic Ports left blank.

Protocols for Database

Improve this answer
2

We solve this problem by installing the AccessDatabaseEngine driver from Microsoft:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Improve this answer
2
  • Could you please provide more detail with instructions on how to use this?
    – haresfur
    Commented Jul 17, 2019 at 0:47
  • You simply install it, restart, and connect in QGIS.
    – Inactivated Account
    Commented Jul 17, 2019 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.