17

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?

KT.
  • 10,815
  • 4
  • 47
  • 71
  • Which error is thrown as you have two commented out? – Parfait Dec 23 '16 at 18:56
  • Possible duplicate of [MSSQL2008 - Pyodbc - Previous SQL was not a query](https://stackoverflow.com/questions/7753830/mssql2008-pyodbc-previous-sql-was-not-a-query) – Nusrath Mar 13 '19 at 11:00

6 Answers6

32

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()
KT.
  • 10,815
  • 4
  • 47
  • 71
  • Calling cursor.nextset() worked for me when a sproc was actively setting nocount to off, so setting it beforehand didn't work. – Jeffrey Harmon Apr 02 '19 at 22:01
  • I couldn't get `SET NOCOUNT ON` to work. while loop solved my problem, thank you so much. – BcK Feb 22 '21 at 11:16
5

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

Shijo
  • 9,313
  • 3
  • 19
  • 31
  • :- I tried it and it worked fine on the server but does not work from python. –  Dec 23 '16 at 14:49
  • @jessica - The error definitely looks like a missing `SET NOCOUNT ON;` *in the stored procedure itself*. Did you actually verify that `SET NOCOUNT ON;` is the first executable statement in the stored procedure? – Gord Thompson Dec 23 '16 at 20:51
2

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

sFishman
  • 137
  • 9
0

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

a_bodkin_betwixt_the_knaves
  • 21
  • 1
  • 2
0

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.

SohebBaig
  • 1
0

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

Mattman85208
  • 1,858
  • 2
  • 29
  • 51