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

Question

I am trying to call a stored procedure in Python, but it continues to give me the following error. The procedure is written in SQL Server 2008, and I use 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 throws the following exception:

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

What could be the problem?

Answer 1

This is what happens. A stored procedure contains several steps. When it is run from the SQL Server management studio, it is easy to see how each step leads to a separate message, for example "(3 row(s) affected)", and only the last step triggers a response.

-, pyodbc resultset, , , , fetchall().

, nextset(), , , :

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

, , SET NOCOUNT ON;, , , , (# rows affected) . ​​ proc, :

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

Answer 2

SET NOCOUNT ON SP SP, x SP

Answer 3

For stored procedures you do not need .fetchall (). I had a similar problem and removing this tag cleared it.