MSSQL2008 - Pyodbc - Previous SQL was not a query

Question

I can't figure out what's wrong with the following code, The syntax IS ok (checked with SQL Management Studio), i have access as i should so that works too.. but for some reason as soon as i try to create a table via PyODBC then it stops working.

import pyodbc

def SQL(QUERY, target = '...', DB = '...'):
    cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + target + DB+';UID=user;PWD=pass')
    cursor = cnxn.cursor()
    cursor.execute(QUERY)
    cpn = []

    for row in cursor:
        cpn.append(row)
    return cpn

print SQL("CREATE TABLE dbo.Approvals (ID SMALLINT NOT NULL IDENTITY PRIMARY KEY, HostName char(120));")

It fails with:

Traceback (most recent call last):
  File "test_sql.py", line 25, in <module>
    print SQL("CREATE TABLE dbo.Approvals (ID SMALLINT NOT NULL IDENTITY PRIMARY KEY, HostName char(120));")
  File "test_sql.py", line 20, in SQL
    for row in cursor:
pyodbc.ProgrammingError: No results.  Previous SQL was not a query.

Anyone have any idea to why this is? I got a "SQL Server" driver installed (it's default), running Windows 7 against a Windows 2008 SQL Server environment (Not a express database).

Bejarano

Also tested with "SQL Server Native Client 10.0", same problem.. it just says that my "Previous SQL was not a query".. – Bejarano

Also note that i CAN connect to the server, all that works (both with Windows login and SQL account login, but the error is the same no matter what option i use) – Bejarano

Does this have anything to do with the fact that i'm using a Enterprise Cluster and connecting to the cluster and not the actual machines themselves? – Bejarano

Quite obviously, the SQL statement you're executing is not a query! There is no SELECT statement. If you'd like to get something back, do CREATE TABLE ... SELECT 1 – Bort

I got this error because I was using a stored procedure that did multiple things, only one of which was selecting the results I wanted. This answer explains how to solve that issue. – Remsen

Answer 1

Just in case some lonely net nomad comes across this issue, the solution by Torxed didn't work for me. But the following worked for me.

I was calling an SP which inserts some values into a table and then returns some data back. Just add the following to the SP :

SET NOCOUNT ON

It'll work just fine :)

The Python code :

    query = "exec dbo.get_process_id " + str(provider_id) + ", 0"
    cursor.execute(query)

    row = cursor.fetchone()
    process_id = row[0]

The SP :

USE [DBNAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GET_PROCESS_ID](
    @PROVIDER_ID INT,
    @PROCESS_ID INT OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO processes(provider_id) values(@PROVIDER_ID)
    SET @PROCESS_ID= SCOPE_IDENTITY()
    SELECT @PROCESS_ID AS PROCESS_ID
END

Lawmaker

Always nice with some additonal solutions! – Bejarano

This is the correct answer. If you do not want to, or cannot alter your procedure, then use: noCount = """ SET NOCOUNT ON; """ and then cursor.execute(nocount + query) – Guyon

@Lawmaker Fabulous! Its worked for me. In my case, i was inserting the data into a temp table. It was still looking for NOCOUNT ON. Any idea as to why this is looking for SET NOCOUNT ON? – Clearway

thanks for the solution. just adding more to this answer. If you have a SQL function (rather than a stored proc), SQL server does not allow you to write SET NOCOUNT ON in the function, but you can add this statement before executing the function. This will help eliminate the " Previous SQL was not a query" on SQL FUNCTIONS. – Specialty

Just a minor comment to the effect that if you're running a .sql file and you're happy to edit that, you can just prepend SET NOCOUNT ON; at the top of the file. No need to do string processing on the query in Python. – Thundering

Python 3.7 + MS SQL 2016: trick with NOCOUNT doesn't work for me (((( – Holp

@Guyon your comment was super helpful and solved my issue. I was trying to execute a stored procedure and getting weird errors. I wonder why "SET NOCOUNT ON" isn't the default behavior in pyodbc. – Belga

For me it works with SET NOCOUNT ON in my stored procedure – Asturias

Still works in 2020. Followed @felbus's suggestion of adding it as prefix to the query. – Polish

For those that this did NOT work for (such as me and @ivanoff) , please see @johnson 's answer below about "SET ANSI_WARNINGS OFF;" - I needed this too. Works great now. – Polychromy

Answer 2

Using the "SET NOCOUNT ON" value at the top of the script will not always be sufficient to solve the problem.

In my case, it was also necessary to remove this line:

Use DatabaseName;

Database was SQL Server 2012, Python 3.7, SQL Alchemy 1.3.8

Hope this helps somebody.

Experiment

Thank you, exactly what I needed. – Tiddlywinks

Thank you again. I've already needed this twice. :) – Glycoside

Thank you so much, Removing the "USE database; " has resolved my problem. – Croat

Answer 3

I got this because I was reusing a cursor that I was looping over:

rows = cursor.execute(...)
for row in rows:
    # run query that returns nothing
    cursor.execute(...)
    # next iteration of this loop will throw 'Previous SQL' error when it tries to fetch next row because we re-used the cursor with a query that returned nothing

Use 2 different cursors instead

rows = cursor1.execute(...)
for row in rows:
    cursor2.execute(...)

or get all results of the first cursor before using it again:

Use 2 different cursors instead

rows = cursor.execute(...)
for row in list(rows):
    cursor.execute(...)

Blackandblue

Answer 4

As others covered, SET NOCOUNT ON will take care of extra resultsets inside a stored procedure, however other things can also cause extra output that NOCOUNT will not prevent (and pyodbc will see as a resultset) such as forgetting to remove a print statement after debugging your stored procedure.

Esposito

Answer 5

As Travis and others have mentioned, other things can also cause extra output that SET NOCOUNT ON will not prevent.

I had SET NOCOUNT ON at the start of my procedure but was receiving warning messages in my results set.

I set ansi warnings off at the beginning of my script in order to remove the error messages.

SET ANSI_WARNINGS OFF

Hopefully this helps someone.

Zuzana

It helped me! Thanks! – Gooseflesh

Answer 6

Every statement in a script could produce some output either as a dataset or/and as a message.

select 1 as id

produce

id
-----------
1

(1 row affected)

Last line above is "messages". If I run cursor.fetchall() on this I will get a result back without any errors.

But if my query is an insert .. into .. or a call to stored procedure that does not return dataset, then fetchall() on it would give me an error.

There is a way to know if fetching would give me pyodbc.ProgrammingError:

if cursor.description:
    cursor.fetchall()

cursor.description has all the field definitions for your dataset It would be None if there is nothing there.

If I have multiple statements in a script I can iterate over datasets with nextset(). Here is a way to read full response:

def print_current_results(cursor):
    if cursor.messages:
        print(cursor.messages)
    if cursor.description:
        print(cursor.fetchall())

cursor.execute(some_script)
print_current_results(cursor)
while cursor.nextset():
   print_current_results(cursor)

To reduce number of loops and minimize the overall output, use SET NOCOUNT ON that is mentioned in almost all other answers. If sql statement has not produced dataset or message then it skipped in output and this is how you can do just cursor.fetchall() instead of above much longer script

Roanne

Answer 7

If your stored procedure calls RAISERROR, pyodbc may create a set for that message.

CREATE PROCEDURE some_sp
AS
BEGIN
    RAISERROR ('Some error!', 1, 1) WITH NOWAIT
    RETURN 777
END

In python, you need to skip the first sets until you find one containing some results (see https://github.com/mkleehammer/pyodbc/issues/673#issuecomment-631206107 for details).

sql = """
    SET NOCOUNT ON;
    SET ANSI_WARNINGS OFF;
    DECLARE @ret int;
    EXEC @ret = some_sp;
    SELECT @ret as ret;
    """
cursor = con.cursor()
cursor.execute(sql)

rows = None
#this section will only return the last result from the query
while cursor.nextset():
    try:
        rows = cursor.fetchall()
    except Exception as e:
        print("Skipping non rs message: {}".format(e))
    continue

row = rows[0]
print(row[0])  # 777.

Voiceful

Answer 8

I think the root cause of the issue described above might be related with the fact that you receive the same error message when you execute for example a DELETE query which will not return a result. So if you run

 result = cursor.fetchall()

you get this error, because a DELETE operation by definition does not return anything. Try to catch the exception as recommended here: How to check if a result set is empty?

Gehrke

Answer 9

In case your SQL is not Stored Proc.

usage of 'xyz != NULL' in query, will give the same error i.e. "pyodbc.ProgrammingError: No results. Previous SQL was not a query."

Use 'is not null' instead.

Gabrielgabriela

Answer 10

First off:

if you're running a Windows SQL Server 2008, use the "Native Client" that is included with the installation of the SQL software (it gets installed with the database and Toolkits so you need to install the SQL Management applicaton from Microsoft)

Secondly: Use "Trusted_Connection=yes" in your SQL connection statement:

cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=ServerAddress;DATABASE=my_db;Trusted_Connection=yes')

This should do the trick!

Bejarano

Answer 11

I have solved this problem by splitting the use database and sql query into two execute statements.

Curassow