I am trying to execute a SQL script on SQL Server in Python using pyodbc and the driver ODBC 18 (Windows OS). The script produces a dataset with some rows in output and, during the execution, it generates also many debug informations (with PRINT or RAISERROR('...', 0, 1)) that can't be disabled. Unfortunately, executing the fetchall() method on the returning cursor, I receive the error pyodbc.ProgrammingError: No results. Previous SQL was not a query..

I found that the SQL script could run successfully. It's just the python part that interprets all kinds of message as an error. The following simple script produces the error above:

RAISERROR('msg', 0, 1)
SELECT 1 AS col

Changing the RAISERROR with PRINT the error will be also raised. By just removing all RAISERROR and PRINT, the previous script runs successfully. So, the root cause is the simple print of a message. Some messages could be disabled (e.g. through SET ANSI_WARNINGS OFF or SET NOCOUNT ON) but sometimes the info messages are not wanted to be hidden or disabled.

The question is: there is a way to let pyodbc ignore all messages? Are all kind of messages interpreted as error by the driver? I looked for a driver option in the MS ODBC Driver documentation and also in the pyodbc docs, but found nothing.

I am trying to execute a SQL script on SQL Server in Python using pyodbc and the driver ODBC 18 (Windows OS). The script produces a dataset with some rows in output and, during the execution, it generates also many debug informations (with PRINT or RAISERROR('...', 0, 1)) that can't be disabled. Unfortunately, executing the fetchall() method on the returning cursor, I receive the error pyodbc.ProgrammingError: No results. Previous SQL was not a query..

I found that the SQL script could run successfully. It's just the python part that interprets all kinds of message as an error. The following simple script produces the error above:

RAISERROR('msg', 0, 1)
SELECT 1 AS col

Changing the RAISERROR with PRINT the error will be also raised. By just removing all RAISERROR and PRINT, the previous script runs successfully. So, the root cause is the simple print of a message. Some messages could be disabled (e.g. through SET ANSI_WARNINGS OFF or SET NOCOUNT ON) but sometimes the info messages are not wanted to be hidden or disabled.

The question is: there is a way to let pyodbc ignore all messages? Are all kind of messages interpreted as error by the driver? I looked for a driver option in the MS ODBC Driver documentation and also in the pyodbc docs, but found nothing.

• python
• sql-server
• pyodbc
• ignore
• raiserror

• SET ANSI_WARNINGS OFF does more than disables messages, FYI. – Thom A Commented yesterday
• 1 As an example: db<>fiddle – Thom A Commented yesterday
• Good point @ThomA, but in this case I was focused only on the message suppression effect of that statement. – Armando Contestabile Commented yesterday

1 Answer 1

Use nextset() to skip the messages.

You will have to manually iterate through all result sets, including the messages and then move to the actual dataset using the cursor.nextset()

Define the connection and then:

cursor = conn.cursor()

# Execute SQL script
cursor.execute("""
    RAISERROR('msg', 0, 1); 
    SELECT 1 AS col;
""")

while cursor.nextset():
    pass

rows = cursor.fetchall()
print(rows)