install pymssql and how to deal with DB-Lib error message 20009, severity 9

Hello,

In a recent zope project I’m obliged to use an mssql database. So I test first an installation with a windows server os. Everything is ok when I configure the good port (1433) and open tcpip connection in sql management and stop firewall.

But when I try to connect with pymssql in unix I have some problem.
When I launch a connection to mssql I have this error:

>>> _mssql.connect("host","user", "password")
Traceback (most recent call last):
File "<console>", line 1, in ?
MssqlDatabaseException: DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist
Net-Lib error during Operation now in progress Error 36 - Operation now in progress

Yahoo , I’m very happy.

In google , no good post about this problem, so I’m very desappointed. I know that pymssql on unix work with freedts which is an implementation of the protocol which use mssql.
When you install freedts you install also some utility. It’s located in bin. Some of it  named tsql. You can use it to test your connection. As pymssql is an wrapper to freedts (in unix) and if tsql don’t work I suppose pymssql also. So I try with this command and I have exactly the same results :

mac:bin yboussard$ ./tsql -H myip -p 1433 -U myuser
locale is "fr_FR.UTF-8"
locale charset is "UTF-8"
Password:
Msg 20017, Level 9, State -1, Server OpenClient, Line -1
Unexpected EOF from the server
Msg 20002, Level 9, State -1, Server OpenClient, Line -1
Adaptive Server connection failed
There was a problem connecting to the server

So I’m very again very happy and reassure that I’m in the good way.
In read documentation about freedts and we can debug connection in setting environment variable TDSDUMP=/tmp/freetds.log. So now when I launch an connection I log in freetds.log. I see in this file the tds version used by the connection and I see it was incorrect (tds version is 5) in accordance with that documentation.
So I use an another environnement variable to fix that :

export TDSVER=7.0

And miracle , everything work with tsql. So I force version of tds in my ~/.freetds.conf in global section as this

[global]
tds version = 7.0

And after that everything is ok in python. Yahoo!!. I hope that ticket will be useful for you.

Regards Youenn.

About these ads

17 Comments

  1. freefallfred said,

    March 4, 2009 at 3:42 pm

    Thanks for the hint, it solved my problem!

  2. alberto besana said,

    March 6, 2009 at 5:18 pm

    Very useful thank you!
    Not the same problem but you pointed out what I was missing:
    TDSDUMP=/tmp/freetds.log
    a debug log!

    I compiled freetds by my own. Probably I forgot some flag because I was using port 4000. I changed to standard value 1433 in freetds.conf and everything went fine.

    Ciao,
    A

  3. April 15, 2009 at 2:34 pm

    I noticed that this is not the first time you write about this topic. Why have you decided to write about it again?

    • yboussard said,

      April 15, 2009 at 3:09 pm

      why you did you say that. I don’t understand

  4. May 1, 2009 at 1:44 pm

    [...] overriden by a file in you userpath? /home/username.freetds.conf a google of that errormessage install pymssql and how to deal with DB-Lib error message 20009, severity 9 « PyYou Weblog [...]

  5. caio said,

    May 8, 2009 at 6:55 pm

    thank you yboussard..
    fixed my issue.

    cheers

  6. robert swift said,

    May 29, 2009 at 11:01 am

    worked a treat, thank you!

  7. mtkd said,

    June 25, 2009 at 2:54 pm

    Many thanks for posting this.

  8. Jessica said,

    August 7, 2009 at 6:30 pm

    Sweet! Just what I needed. Thanks.

  9. Stan said,

    September 11, 2009 at 8:24 pm

    Great tip! Having spent hours trying to figure out what an “EOF error” meant, this page at least gave me a fix: once I set “export TDSVER=8.0″ it worked. Thanks!

  10. Sean DiZazzo said,

    September 18, 2009 at 12:22 am

    Thanks!

  11. Erik W said,

    August 7, 2010 at 1:25 am

    Awesome, thank you so much! Not sure what I was doing wrong, but for me it was ignoring the conf file? I recompiled freetds for version 7.0 (to connect to an MS SQL Server) and it worked!

    wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz

    untar, then:

    ./configure –with-tdsver=7.0
    ./make
    ./make install

  12. linenise77 said,

    March 7, 2011 at 5:58 am

    Thanks, for your work!

  13. Notch said,

    July 26, 2011 at 8:03 pm

    You saved me. Thanks a million for this page!

  14. Svend Midtgaard said,

    November 23, 2011 at 12:59 pm

    I have just installed pymssql with python 2.5 on Windows 7. I set the environmentvariable export TDSVER=7.0 and wrote in global section of freetds.conf: tds version = 7.0

    I made a small testscript.py:

    import _mssql
    conn = _mssql.connect(server=’10.200.29.25′, user=’odbc’, password=’testabc123′, database=’AXDB40_RANDERS’)
    conn.execute_query(‘SELECT INSTNUM FROM EXU_INSTALLATION WHERE INSTNUM = 1001811′)
    for row in conn:
    print row

    When I run my test-script from within IDLE (Run -> Run module) i get this errormessage:
    “…
    MSSQLDatabaseException: (20017, ‘\xc4DB-Lib error message 20017, severity 9:\nUnexpected EOF from the server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed\n’)

    But when I run testscript.py from a commandline IT WORKS – data from MSSQL are printed on screen.

    Does anybody know how to make it work with IDLE?

    Best regard and thanks a lot
    Svend

    • yboussard said,

      November 23, 2011 at 1:23 pm

      environnement between idle and cmd must be different I think.

      • Svend Midtgaard said,

        November 23, 2011 at 1:44 pm

        Hey yboussard – thanks for your answer.

        Environment seems to be the same – these lines were also in my pythonscript:

        import os
        a = os.environ
        print a.get(‘EXPORT TDSVER’)

        and it printed “7.0” …
        Svend


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: