CODEWITHSUNDEEP
pythonsqlsql-serverpyodbc
user7135817
user7135817

Reputation:

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

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?

Upvotes: 19

Views: 20923

Answers (6)

sFishman
sFishman

Reputation: 133

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

Upvotes: 3

Mattman85208
Mattman85208

Reputation: 2090

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

Upvotes: 0

SohebBaig
SohebBaig

Reputation: 1

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.

Upvotes: 0

a_bodkin_betwixt_the_knaves
a_bodkin_betwixt_the_knaves

Reputation: 21

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

Upvotes: 0

KT.
KT.

Reputation: 11430

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()

Upvotes: 37

Shijo
Shijo

Reputation: 9711

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

Upvotes: 6

Related Questions