pyodbc: PRINT messages from triggers not being returned when SET NOCOUNT is ON

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

• Python: 3.11
• pyodbc: 4.0.39
• OS: Windows
• DB: SQL Server 2019
• driver: ODBC Driver 18 for SQL Server 2018.181.02.01

Issue

The cursor.messages is always empty in the below code because of the SET NOCOUNT ON statemen but I see the PRINT statement when I run it in Microsoft SQL Studio Manager. I believe it’s because you’re not calling SQLGetDiagRec when you get SQL_NO_DATA_FOUND.

According to Return Codes ODBC. The application should call SQLGetDiagRec or SQLGetDiagField to retrieve additional information when it gets the following return codes.

• SQL_SUCCESS
• SQL_SUCCESS_WITH_INFO
• SQL_ERROR
• SQL_NO_DATA
• SQL_NEED_DATA
• SQL_STILL_EXECUTING

So please make sure you’re calling those functions in those scenarios.

Code to replicate

import logging
import unittest

import pyodbc

log = logging.getLogger(__name__)


class MyTestCase(unittest.TestCase):

    def test_something(self):
        conn_str = "Driver=ODBC Driver 18 for SQL Server;Server=localhost;TrustServerCertificate=yes;Trusted_Connection=yes;APP=tests.utils.TestCase:1022::46840;MARS_Connection=yes;ConnectRetryCount=30"
        conn = pyodbc.connect(conn_str, autocommit=True)
        cursor = conn.execute("DROP TABLE IF EXISTS FOO; CREATE TABLE FOO(ID INT IDENTITY PRIMARY KEY, NAME NVARCHAR(255) NULL);")
        while cursor.nextset():
            cursor.nextset()
        cursor = conn.execute("""
CREATE  TRIGGER [dbo].[trg_FOO] ON FOO
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON
    --UNCOMMENTING THE BELOW LINE CAUSES PYODBC TO GET THE PRINT STATEMENT
    --SET NOCOUNT OFF
    PRINT 'WOW!!!!'
END
        """)
        cursor.nextset()
        cursor = cursor.execute("INSERT INTO FOO(Name) SELECT NEWID() WHERE 1=0")
        log.info(f"{cursor.messages=}")
        while cursor.nextset():
            log.info(f"{cursor.messages=}")
        log.info(f"{cursor.messages=}")


if __name__ == '__main__':
    unittest.main()

Trace Files

SQL.NO_COUNT_OFF.LOG SQL.NO_COUNT_ON.LOG