'Multithreaded pyodbc connection

I am trying to use a pyodbc connection in multiple threads. I am receieving the following error: Connection is busy with results for another command (0) (SQLExecDirectW)'). I also tried setting MultipleActiveResultSets=yes; and got the same results.

I really don't want to have to create a connection for every thread or query. I also don't want to use a lock to serialize queries from all threads.

I was first using the driver "SQL Server" and then moved to "SQL Server Native Client 11.0" with no luck.

Any suggestions?

pythonsqlmultithreadingpyodbc


Solution 1:[1]

According to ODBC-documentation https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client?view=sql-server-ver15, the keyword you should be using is MARS_Connection=yes. Using this with pyodbc does work and solves my dual-open-cursor -issue.

Using MultipleActiveResultSets=true does not work for two reasons: using incorrect keyword and using incorrect values, as in ODBC flags are not true/false, they are yes/no.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jari Turkia

Related Questions