Today, I worked on a service request that our customer got several issues that I would like to share with you my findings here.
1) pyodbc.Error: ('HY000', '[HY000] [Microsoft][ODBC Driver 17 for SQL Server]Connection is busy with results for another command (0) (SQLExecDirectW)')
- This error ocurrs when the Python code is trying to open a new cursor when we have a previous one with results.
import os
import pymssql
import pyodbc
conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=servername.database.windows.net,1433;UID=username;PWD=Password;database=dbName;Mars_Connection=no");
cursor = conn.cursor()
cursor.execute('select * from sys.databases')
row = cursor.fetchone()
print(f"row={row}")
cursor3 = conn.cursor()
cursor3.execute('select * from sys.databases')
cursor3.close()
row = cursor3.fetchone()
print(f"row={row}")
conn.close()
- As we mentioned in our previous article enabling Mars we could fix this issue.
2) pyodbc.ProgrammingError: Attempt to use a closed cursor.
import os
import pymssql
import pyodbc
conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=servername.database.windows.net,1433;UID=username;PWD=Password;database=dbName;Mars_Connection=no");
cursor = conn.cursor()
cursor.execute('select * from sys.databases')
cursor.close()
row = cursor.fetchone()
print(f"row={row}")
cursor3 = conn.cursor()
cursor3.execute('select * from sys.databases')
cursor3.close()
row = cursor3.fetchone()
print(f"row={row}")
conn.close()
- In this situation, the issue is regarding in the line 11 that the cursor is closed before executing it.
3) pyodbc.ProgrammingError: The cursor's connection has been closed.
import os
import pymssql
import pyodbc
conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=servername.database.windows.net,1433;UID=username;PWD=Password;database=dbName;Mars_Connection=no");
cursor = conn.cursor()
cursor.execute('select * from sys.databases')
conn.close()
row = cursor.fetchone()
print(f"row={row}")
cursor3 = conn.cursor()
cursor3.execute('select * from sys.databases')
cursor3.close()
row = cursor3.fetchone()
print(f"row={row}")
conn.close()
- This situation is happening when the connection is closed before obtaining the data or run the cursor.
4) ERROR:asyncio:Unclosed connection - connection: <aioodbc.connection.Connection object at 0xXXX
This is a not ODBC driver code, this error message is coming from an exception captured by aioodbc/connection.py at master · aio-libs/aioodbc · GitHub because our customer is using this library aioodbc/aioodbc at master · aio-libs/aioodbc · GitHub for async ODBC calls.
def __del__(self):
if not self.closed:
# This will block the loop, please use close
# coroutine to close connection
self._conn.close()
self._conn = None
warnings.warn("Unclosed connection {!r}".format(self),
ResourceWarning)
context = {'connection': self,
'message': 'Unclosed connection'}
if self._source_traceback is not None:
context['source_traceback'] = self._source_traceback
self._loop.call_exception_handler(context)
So, all these errors above points to either the connection was closed before executing next query or the connection is busy processing the previous query which could cause bottleneck.
5) [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")
- This error ocurred when ODBC driver was not able to prepare a statement, for example, syntax error in the parametrized query that the application is trying to run.
Enjoy!
