Possible bug in cursor when a result set has both data and messages?

[IDataReader]

I'm not a Python developer, but others on my team are. They reported problems when querying the SQL Server (2016) database I maintain, so I had to experiment.
I quickly found that they were assuming that the cursor would contain one result set, containing the result (integer) of the procedure they were executing. That's because they call procedures something like this:
cursor.execute ( "DECLARE @Result INTEGER ; EXECUTE @Result = spProcedureBlahBlah param1 , param2 ... paramn ; SELECT @Result ;" )
So, yes, there should be a result set containing the result of the procedure. But they weren't allowing for other result sets, result sets with no data, and messages.
Therefore, they received the "No results. Previous SQL was not a query." -- which is entirely preventable by understanding the tools in use.
Now, of course, people started shouting "you have to set NOCOUNT ON in the procedure!" -- but I have determined that the setting of NOCOUNT has no bearing on the issue (with PYODBC).
What has been a bit of an issue (in some cases) is the "Warning: Null value is eliminated by an aggregate or other SET operation." message.
And people shouted, "you have to set ANSI_WARNINGS OFF in the procedure!" -- but, while that is related (in some cases), we cannot always do that. We have a linked server (ptui!), so we have some distributed queries (ptui!) and they require ANSI_WARNINGS ON.
So, no, setting NOCOUNT ON or ANSI_WARNINGS OFF is not the answer -- they may be a work-around when appropriate, but work-arounds are not solutions.

My recommendation to the Python developers on my team and any others is to use cursor.description to determine whether or not there is data in the current result set (I'd appreciate other techniques) and to use cur.nextset() to ensure that all of the result sets are retrieved.
Retrieving the cur.messages may not be necessary in most cases.

But, I have experimented far too much this week, and I have tried many different scenarios and discovered an issue which no one has reported to me.

My description of the issue is :
Observed bahavior -- When a result set contains both data and messages, the messages are not being reported, and the cursor stops and produces no further result sets from the call.
Expected behavior -- pyodbc should produce a result set with both data and messages, as stated by Keith re:#765 ("It should be noted, each result set can include both row data and messages."), then keep going.
Tested with two versions of pyodbc (including 5.2.0) and two versions of SQL Server (2016 and 2022)
I haven't seen this listed as a known issue, or some way to configure pyodbc to behave this way.

To reproduce this, you can execute an SQL statement which returns data and also produces a message. The "Warning: Null value is eliminated..." message is easiest to produce.
And, of course, setting ANSI_WARNINGS OFF will resolve that -- but, as said, that's not always an option, and isn't really a good idea in general anyway.
I have another work-around, and that is to have a two-step process for queries which produce the "Warning: Null value is eliminated..." message --
SELECT the data into a temporary table (producing the "Warning: Null value is eliminated..." message), then query the temporary table.
The result is then a cursor containing a result set with the message but no data, and another result set with the data but no messages.
That's still just a work-around and I'm not about to go back through hundreds of procedures just so PYODBC doesn't choke on a few.

I have written a Python script which demonstrates the issue, but the relevant pyodbc call is:

cxn.cursor().execute (
"""
SET NOCOUNT ON
;
SET ANSI_WARNINGS ON
;
PRINT ' Before'
;
SELECT MAX ( [Now] ) [Now] -- The pyodbc cursor should include a result set with both the data and the message
FROM
(
SELECT SYSUTCDATETIME() [Now]
UNION ALL
SELECT NULL
) T
;
SELECT @@rowcount [RowCount] -- The pyodbc cursor should include a result set for this data
;
PRINT ' After' -- The pyodbc cursor should include a result set for this message
""" )

PYODBC_demo.py.txt

[gordthompson]

ODBC can sometimes behave strangely if a result set is not fully consumed. In your example code, if I change

        result = cur.fetchval()
        print ( "Result : " + str ( cur.description [ 0 ] [ 0 ] ) + " == " + str ( result ) )

… to …

        result = cur.fetchall()
        print ( "Result : " + str ( result ) )

then the output for your first case changes from

Messages : 
('[01000] (0)', '[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]  Before')
 No data
Data
Result : Now == 2025-07-16 18:41:01.042651
Result : 2025-07-16 18:41:01.042651

… to …

Messages : 
('[01000] (0)', '[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]  Before')
 No data
Data
Result : [(datetime.datetime(2025, 7, 16, 18, 47, 19, 379756),)]
Data
Result : [(1,)]
Messages : 
('[01000] (0)', '[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]  After')
 No data
Result : [(1,)]

[IDataReader]

Thanks. I guess I got hung up on trying to make a minimal test to demonstrate what I was seeing and only perpetuated my own mistake.
I now changed mine to:

    while True :
      row = cur.fetchone()
      if ( not row ) : 
        break
      for c in range ( 0 , len ( cur.description ) ) :
        result = row [ c ]
        print ( "Result : " + str ( cur.description [ c ] [ 0 ] ) + " == " + str ( result ) )

and it works as desired. I'll pass that along.

[IDataReader]

A bit more detail for anyone who needs it.

On my personal system, it works fine.
Python 3.13.5 (tags/v3.13.5:6cb20a2, Jun 11 2025, 16:15:46) [MSC v.1943 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.

import pyodbc
pyodbc.version
'5.2.0'

But on my work system, it works, but without displaying the "Null value is eliminated" message, so I assume it's a version issue.
Python 3.6.6 (v3.6.6:4cf1f54eb7, Jun 27 2018, 03:37:03) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.

import pyodbc
pyodbc.version
'4.0.39'

I also noticed that cur.Messages is never None in my tests.