Error "Previous SQL was not a query" in Python?

Question

I am trying to call a stored procedure in Python but it keeps giving me the following error. The procedure is written in SQL Server 2008 and I am using PyODBC to call the method and pass parameters to it.

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+serveripaddr+';DATABASE='+database+';UID='+userid+';PWD='+password+'')
cursor = cnxn.cursor()
cursor.execute("{call p_GetTransactionsStats('KENYA', '41')}")
rows = cursor.fetchall()

The last line results in the following exception:

ProgrammingError: No results.  Previous SQL was not a query.

What could be the problem here?

Answer 1

Can you add SET NOCOUNT ON to you SP and try if you can not modify SP, first execute this statement xand then call SP

Answer 2

I got the error: "ProgrammingError: No results. Previous SQL was not a query." on some SQL with an openquery (not a Stored Procedure). I added the "SET NOCOUNT ON" just before my openquery and cleared the error.

SQL Server OPENQUERY to Oracle Python pyodbc

Answer 3

Here's what happens. The stored procedure contains several steps. When it is executed from the SQL Server Management studio, it is easy to see how each step results in a separate message such as "(3 row(s) affected)", and only the very last step produces the response.

Apparently, when invoked via pyodbc cursor, each of those separate steps produces a separate resultset, where all the resultsets, but the very last one, contain no data that could be read via fetchall().

Hence, one option to solve the problem is to iterate over these resultsets using nextset() until you find one which does produce the result, e.g.:

while cursor.nextset():   # NB: This always skips the first resultset
    try:
        results = cursor.fetchall()
        break
    except pyodbc.ProgrammingError:
        continue

A nicer option is, as mentioned in a different answer, to use the SET NOCOUNT ON; directive, which seems to prevent all of the intermediate, empty (# rows affected) resultsets. The directive can be simply prepended to the proc invocation, for example:

cursor.execute("set nocount on; exec MyStoredProc ?", some_parameter)
results = cursor.fetchall()

Answer 4

I encountered same issue. Remember to use 'SET NOCOUNT ON' in your stored proc. Also, check there is no 'print' statement in your stored proc.

Answer 5

For future Googlers: I was getting this error and my stored procedure wasn't actually even inserting the data in the database table I wanted when run from pyodbc. My problem was that I didn't close the cursor and commit after executing. Make sure you do

cursor.close()
connection.commit()

after running cursor.execute() if you're doing more than just SELECTs

Answer 6

For stored procedures, you don't need .fetchall(). I had a similar issue and taking away that tag cleared it up.