OperationalError: DB-Lib error message 10029,

[D. Watrous]

Hello,

I'm using pymssql to establish database connections from within threads of a threaded tcp server.  I've been getting the error:

OperationalError: DB-Lib error message 10029, severity 7:
Maximum number of DBPROCESSes already allocated.

It would appear that when "too many" threads open up, later threads cannot access the server.  I have the server configured to accept unlimited connections.  I've also confirmed that my code works when I limit the number of threads that are allowed to spawn.

Is there a way to configure the connection to allow a higher number of concurrent threads to access a remote database?

Thanks,
Daniel

[A]

pymssql doesn't impose any limit on the number of simultaneous connections. The message is from the driver - FreeTDS on Linux, or MS DB-Library for C on Windows. Please look at FreeTDS webpage/forum to obtain more info on this.

[D. Watrous]

I'm on windows, which means that I'll be using the MS DB-Library for C.  What I need to do then is call dbsetmaxprocs(maxprocs) to increase the number:
http://msdn.microsoft.com/en-us/library/aa937057\(SQL.80).aspx

What I see is that pymssql.py calls into _mssql.pyd.  What is the relationship between _mssql.pyd and the MS DB-Library?  Can I make this call directly?

Thanks.

[A]

I'll include an increased limit in the next release of pymssql. I'll try to provide a configurable value. In the meantime you have to hack mssqldbmodule.c youself.

[D. Watrous]

Here are some patches that I think would accomplish what I'm after, but I can't build.  There are several references to devel directories on the D: drive.  I'm sorry to sound so novice, but I'm not sure how to get around those.  This includes my patch for the asDict which is also included in another post, but since it hasn't been released I included it to keep positioning straight.

mssqldbmodule.c

1244c1244
<             &server, &user, &password, &trusted, &charset, &database))
---
>             &server, &user, &password, &trusted, &charset, &database, &maxProcs))
1248a1249
>     DBSETMAXPROCS(maxProcs);

[NEXT FILE]

pymssql.py

103c103
<     def __init__(self, src):
---
>     def __init__(self, src, asDict):
110a111
>         self.asDict = asDict
122c123
<    
---
>
150c151
<    
---
>
159c160
<    
---
>
230c231
<        
---
>
232,234c233,240
<             row = iter(self._source).next()
<             self._rownumber += 1
<             return tuple([row[r] for r in sorted(row.keys()) if type(r) == int])
---
>             if (self.asDict):
>                 row = iter(self._source).next()
>                 self._rownumber += 1
>                 return row
>             else:
>                 row = iter(self._source).next()
>                 self._rownumber += 1
>                 return tuple([row[r] for r in sorted(row.keys()) if type(r) == int])
264c270,274
<                     t = tuple([row[r] for r in sorted(row.keys()) if type(r) == int])
---
>                     if (self.asDict):
>                         # pass through if asDict=True
>                         t = row
>                     else:
>                         t = tuple([row[r] for r in sorted(row.keys()) if type(r) == int])
287,289c297,302
<             list = [tuple([row[r] for r in sorted(row.keys()) if type(r) == int]) for row in self._source]
<             self._rownumber += len(list)
<             return list
---
>             if (self.asDict):
>                 return [ row for row in self._source ]
>             else:
>                 list = [tuple([row[r] for r in sorted(row.keys()) if type(r) == int]) for row in self._source]
>                 self._rownumber += len(list)
>                 return list
334c347
<        
---
>
419c432
<     def __init__(self, cnx):
---
>     def __init__(self, cnx, asDict):
421a435
>         self.asDict = asDict
457c471
<         if self._autocommit == True:
---
>         if self._autocommit == True:
469c483
<         if self._autocommit == True:
---
>         if self._autocommit == True:
482c496
<         return pymssqlCursor(self._cnx)
---
>         return pymssqlCursor(self._cnx, self.asDict)
491c505
<                 self._autocommit = True
---
>                 self._autocommit = True
495c509
<                 self._autocommit = False
---
>                 self._autocommit = False
499,500c513,514
< def connect(dsn = None, user = "sa", password = "", host = ".", database = "",
<             timeout = 0, login_timeout = 60, trusted = False, charset = None):
---
> def connect(dsn = None, user = "sa", password = "", host = ".", database = "",
>             timeout = 0, login_timeout = 60, trusted = False, charset = None, asDict = False, maxProcs = 25):
509c523
<     trusted   whether to use Windows Integrated Authentication to connect
---
>     trusted   whether to use Windows Integrated Authentication to connect
528c542
<    
---
>
578c592
<         con = _mssql.connect(dbhost, dbuser, dbpasswd, trusted, charset)
---
>         con = _mssql.connect(dbhost, dbuser, dbpasswd, trusted, charset, dbbase, maxProcs)
584c598
<     try:   
---
>     try:
591c605
<    
---
>
601c615
<     return pymssqlCnx(con)
---
>     return pymssqlCnx(con, asDict)

[A]

Thanks for the patch. If you build on Windows, you need SQL Server Developer Tools, to be found on SQL Server 2000 installation CD. Otherwise you need freetds-devel or similar. See http://pymssql.sourceforge.net/download.html for details. Anyway you can delete those D: drive directories from setup.py.