Q:

MSSQL2014 & python 3.7.3: Trying to get stored procedure return value results in "No results. Previous SQL was not a query."

I'm running a python script which connects to a MSSQL database, runs a stored procedure and should take a some actions depending on the stored procedure return value. Here's the code:
'''
sql = """\
        SET NOCOUNT ON
        DECLARE @rv INT
        EXEC @rv = [MY_DB].[dbo].[sp_Load_Actuals]
        SELECT @rv 
        """
...
conn = pyodbc.connect('Driver={ODBC Driver 13 for SQL Server};'
                                      'Server=MY_SERVER;'
                                      'Database=MY_DB;'
                                      'Trusted_Connection=yes;')

conn.autocommit = True
cursor = conn.cursor()
cursor.execute(sql)
retv = cursor.fetchone() 
...

On the execution of the last statement I'm catching the pyodbc.DatabaseError exception with the message  "No results. Previous SQL was not a query." I have read all related articles here, but apparently I'm still doing something wrong. 
What do I miss?

A:

You have to call cursor.nextset() to skip past any info messages that were output by the called stored procedures, as NOCOUNT ON is not enough here.   
But unfortunately cursor doesn't have a .hasrows() method, so you have to catch the exception in a loop.
EG:
import pyodbc

sql = """\
        SET NOCOUNT ON
        DECLARE @rv INT
        EXEC @rv = sp_executesql N'print ''informational message'''
        SELECT @rv 
        """

conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                                      'Server=localhost;'
                                      'Database=tempdb;'
                                      'Trusted_Connection=yes;')

conn.autocommit = True
cursor = conn.cursor()
cursor.execute(sql)

while True:
    try:
        retv = cursor.fetchone() 
        break
    except pyodbc.ProgrammingError as e:
       if "Previous SQL was not a query." in str(e):
        if not cursor.nextset():
          throw

print(retv)

