1

Using Ubuntu 10.04 LTS, I'm trying to connect to an MS SQL Server we have on another box from within a Python script. What are the steps I need to follow to do so? Right now, I'm trying to use the pyodbc module, but whenever I try to connect I get the following error:

Data source name not found, and no default driver specified (0)(SQLDriverConnectW)

So I looked at the odbvcinst.ini file which was empty, added an entry, and now when I use one of the items I've defined there I get the following error:

Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0)(SQLDriverConnectW)

I've installed: unixodb unixodbc-dev freetds-dev freetds-bin. I've put this on SF and not SO because I'm thinking this is a server configuration issue. Which driver should I have and how do I reference it in which file, etc? (I'm pretty new to Ubuntu)

Nick
  • 205
  • 5
  • 8

1 Answers1

2

Have you set up the data source in your /etc/freetds.conf file?

The entry should look something like:

# A typical Microsoft server
[egServer70]
        host = ntmachine.domain.com
        port = 1433
        tds version = 7.0

(Though I tend to use tds version = 8.0 when I'm working with MS SQL Server, and it works fine)

Edit: (Going to put my responses here so I can code-format them)

Your /etc/odbcinst.ini should look like:

[MSSQL]
Description = FreeTDS
Driver = /usr/lib/libtdsodbc.so 
Setup = /usr/lib/libtdsS.so
CPTimeout =
CPReuse =

(Make sure the driver paths are right. Sometimes it's /usr/lib/odbc/)

Then the python string should look like:

import pyodbc
connection = pyodbc.connect("DRIVER={MSSQL};SERVER=yourserver.yourdomain.com;UID=username;PWD=password;DATABASE=databasename")
Satanicpuppy
  • 5,917
  • 1
  • 16
  • 18
  • Ok I've done this, and I'm getting the same error. Do I need to point to this somewhere in the odbcinst.ini? Right now in that file I have driver=/usr/lib/libodbc.so, I take it this is wrong? – Nick Jul 01 '10 at 15:51
  • Update: As described in [this post on pyodbc and mssql](http://www.saltycrane.com/blog/2011/09/notes-sqlalchemy-w-pyodbc-freetds-ubuntu/), on Ubuntu 12.04 the drivers have been moved to `/usr/lib/x86_64-linux-gnu/odbc/`. Gonna leave this here for any other late-comers like myself :) – george Oct 22 '12 at 19:59