Using Microsoft SQL through python on a webserver

Question

I'm trying to run some queries to a database from a pylons (paster-based) webserver and every time I try to import the pymssql Library I'm using (its this one by the way) I keep getting this error:

tds_init_winsock: WSAEnumProtocols failed with 10055(WSAENOBUFS: No buffer space
available.)

on the import. I also tried using sqlalchemy and get the exact same error when I try to make an sqlalchemy engine. Is there anything I can do to get this to work. I'm not attached to the webserver or the pymssql library so any others are fine.

Environment Information:

Machine I'm using is a32bit win7 desk top the server is running in a python virtualenv environment, but the pymssql library works flawlessly in the virtual environment just not on the server

UPDATE:

A little more background information (and explanation): I don't think that this is a MSSQL problem or even a python problem in a way. for two reasons: A) Me and others can still connect to the MSSQL database while this problem occurs and B) pymssql works perfectly fine if it isn't being imported from the server. I do however think It may have something to do with the way python and _mssql deal with sockets and/or the way the server deals with sockets... hope that helps a little more.

thanks in advance

Answer 1

Error code WSAENOBUFS, while translated into "No buffer space available", actually means (quoting the MSDN page I just pointed to):

An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.

and in practice (as explained e.g. here) usually means (quoting the article I just pointed to):

in most cases the problem occurs when total count of opened sockets reaches some magical number. MS writes that this limit is 3976 simultaneously opened sockets but it seems that on Win9x systems the real limit is much lower.

Since each socket stays "busy" for quite a while after it's closed (240 seconds, that article says) it wouldn't be surprising to see that number (whatever it may be in Windows 7) easily exceeded on a moderately-busy server, especially since a desktop system might well be configured with fewer socket resources than a real server system.

That article points to several possibilities for resolving the issue, though they're dated. But, the key point is that this seems to have nothing to do with Python, Pylons, Paster, or Pymssql -- it's strictly a problem of Windows system configuration and control. As such, you may have better luck asking on serverfault.com, where the server-savvy sysadms hang out!

Answer 2

Ok. I have finally gotten this completely working. pymssql seems to have some problems if it is imported after socket in python, importing it before sockets on a web server is a little ridiculous so I ended up just switching to pyodbc which is surprisingly similar to the pymssql syntax. Definitely a good compromise...