2

I'm trying to get the warning message from SQL Server's Raiserror in python with PyODBC.

The severity is low on purpose, because it acts as a printing functionality, but it doesn't have to wait until the query is done before printing.

The output I expect would be something like:

*Test Message*
|col1|
|:-|
|1|

Here's my code:

import pyodbc

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=ECH-DWPROD02;DATABASE=Test;Trusted_Connection=yes;')
cursor = conn.cursor()

cursor.execute("SET NOCOUNT ON; RAISERROR('Test Message',0,1) WITH NOWAIT; select 1 as col1")
cursor.fetchall()
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
user14518362
  • 320
  • 4
  • 11

1 Answers1

3

Cursor.messages is a fairly recent addition to pyodbc (v4.0.31 – July 2021). It allows us to retrieve messages emitted from SQL Server stored procedures et al. via PRINT and RAISERROR.

Note that such messages constitute a "result" from the server so if they are returned then one must call .nextset() to retrieve additional results, otherwise a "Previous SQL was not a query." error will occur:

crsr.execute("""\
SET NOCOUNT ON; 
RAISERROR('Test Message',0,1) WITH NOWAIT; 
select 1 as col1
""")
print(crsr.messages)
# [('[01000] (50000)', '[Microsoft][ODBC SQL Server Driver][SQL Server]Test Message')]
crsr.nextset()  # required before .fetchall()
print(crsr.fetchall())
# [(1, )]
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1
    Thanks!! This comment was very timely and helped with debugging problem I was having. My version was one lower than the required one and I would not have know about this but for your comment. – Trevor Andrade Oct 02 '22 at 07:50
  • This was extremely helpful to me when debugging some SQL statements I was executing via pyodbc – Max Nov 03 '22 at 22:52