DatabaseError: ('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt (0) (SQLExecDirectW)')

Question

I am trying to read data from SQL server into pandas data frame. Below is the code.

def get_data(size):
    con = pyodbc.connect(r'driver={SQL Server}; server=SPROD_RPT01; database=Reporting')
    cur = con.cursor()
    db_cmd = "select distinct top %s * from dbo.KrishAnalyticsAllCalls" %size
    res = cur.execute(db_cmd)
    sql_out = pd.read_sql_query(db_cmd, con, chunksize=10**6)
    frames = [chunk for chunk in sql_out]
    df_sql = pd.concat(frames)
    return df_sql

df = get_data(5000000)

I am getting following error:

pandas.io.sql.DatabaseError: Execution failed on sql 'select distinct top 500000 * from dbo.KrishAnalyticsAllCalls': ('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt (0) (SQLExecDirectW)')

I had executed the function before and interrupted the execution with ctrl+k as I wanted to make a change in the function. Now, after making the change when I'm trying to execute the function I am getting the above error.

How can I kill that connection/IPython Kernel since I don't know of any IPython Kernel running executing the query in the function?

Ebba

If you are working in a Python shell/IDE then you may need to shut it down and restart it to kill off the "zombie" connection. – Solitary

Yes, I have already done that. I have also tried shutting down the entire computer but still getting the same error. – Ebba

Answer 1

I was facing the same issue. This was fixed when I used fetchall() function. The following the code that I used.

import pypyodbc as pyodbc

def connect(self, query):
    con = pyodbc.connect(self.CONNECTION_STRING)
    cursor = con.cursor()
    print('Connection to db successful')
    cmd = (query)
    results = cursor.execute(cmd).fetchall()
    df = pd.read_sql(query, con)
    return df, results

Using cursor.execute(cmd).fetchall() instead of cursor.execute(cmd) resolved it. Hope this helps.

Seaborne

Answer 2

The issue is due to cursor being executed just before the pd.read_sql_query() command . Pandas is using the connection and SQL String to get the data . DB Cursor is not required .

#res = cur.execute(db_cmd)
sql_out = pd.read_sql_query(db_cmd, con, chunksize=10**6)
print(sql_out)

Crabstick

This was the answer for me thanks. – Feld

Answer 3

Most likely you haven't connected to the SQL server yet. Or, you connected in a previous instance for a different SQL query that was run. Either way, you need to re-establish the connection.

import pyodbc as pyodbc
conn = pyodbc.connect('Driver={YOUR_DRIVER};''Server=YOUR_SERVER;''Database=YOUR_DATABASE;''Trusted_Connection=yes')

Then execute your SQL:

sql = conn.cursor()
sql.execute("""ENTER YOUR SQL""")

Then transform into Pandas:

df = pd.DataFrame.from_records(sql.fetchall(),columns=[desc[0] for desc in sql.description])

Bax

Answer 4

I had this same error message when attempting to reindex my elastic search indexes. It would return:

Connection is busy with other results

The solution in my case was to add the following to my DB connection, specifically the extra_params in the OPTIONS as follows:

"OPTIONS": {
                "driver": "ODBC Driver 17 for SQL Server",
                "extra_params": "MARS_Connection=Yes",
            },

Dictation