Executing Stored Procedure in SQL Server without a full accounting of Errors and Queries

[RunSeven]

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.9.2 (64 bit)
• pyodbc: 4.0.31
• OS: Microsoft
• DB: MS SQL Server 2019
• driver: ODBC Driver 17 for SQL Server

Issue

I have the following stored procedure which I am trying to execute. The commented 'SELECT 0;' is somewhat important as adding or removing it has what I think is a relevant change to the output but I wanted to leave it out initially.

Stored Procedure

CREATE PROCEDURE [dbo].[MultipleReturnMultipleError]	
AS
BEGIN
	
    SET XACT_ABORT OFF;
	
    --SELECT 0;

    SELECT 1/0;

    SELECT 1;
    SELECT 2;
    SELECT 3;	
    SELECT 1/0;

    SELECT 4;	
    SELECT 1/0;

    SELECT 5;
    SELECT 1/0;

    SELECT 6;
    
END;

Procedure Call in Python

import os
import pyodbc

from dotenv import load_dotenv

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

def test_handle_multiple_errors():

    DATABASE = os.getenv('METADATA_DATABASE_NAME')
    ENGINE = create_engine(CONNECTION.format(
        SERVER, DATABASE, DRIVER
    ), encoding='utf8')
    raw_connection = ENGINE.raw_connection()
    
    cursor = raw_connection.cursor()
    sql = "EXECUTE [DW_Staging].[dbo].[MultipleReturnMultipleError];"

    try:
        cursor.execute(sql)
        print("RESULT")
        result = cursor.fetchone()
        print(result)
    except Exception as ex:
        print("\n\nINITIAL EXECUTION ERROR")
        print(ex.args)        

    next_set = True
    while next_set:

        try:
            print("\nMOVE TO NEXT RESULT")
            next_set = cursor.nextset()
            print("RESULT")
            result = cursor.fetchone()
            print(result)
        except Exception as ex:            
            print("\n\nSUBSEQUENT ERROR")        
            print(ex.args)

This has the output

INITIAL EXECUTION ERROR
('22012', '[22012] [Microsoft][SQL Server Native Client 11.0][SQL Server]Divide by zero error encountered. (8134) (SQLExecDirectW)')

MOVE TO NEXT RESULT
RESULT
(1, )

MOVE TO NEXT RESULT
RESULT
(2, )

MOVE TO NEXT RESULT
RESULT
(3, )

MOVE TO NEXT RESULT


SUBSEQUENT ERROR
('22012', '[22012] [Microsoft][SQL Server Native Client 11.0][SQL Server]Divide by zero error encountered. (8134) (SQLMoreResults)')

MOVE TO NEXT RESULT
RESULT


SUBSEQUENT ERROR
('No results.  Previous SQL was not a query.',)

I expected the 'nextstep' function to continue moving through subsequent items with output for the SELECT statements and errors down until 'SELECT 6' where it would exit. Unfortunately this is not the case. I believe it may be associated with the 'SQLMoreResults' error that comes with the second Divide by Zero error.

If the original 'SELECT 0' is uncommented and the procedure updated then even less information is returned as shown in the output below.

RESULT
(0, )

MOVE TO NEXT RESULT


SUBSEQUENT ERROR
('22012', '[22012] [Microsoft][SQL Server Native Client 11.0][SQL Server]Divide by zero error encountered. (8134) (SQLMoreResults)')

MOVE TO NEXT RESULT
RESULT


SUBSEQUENT ERROR
('No results.  Previous SQL was not a query.',)

It appears that there is a difference in an immediate execution error and an error thrown after a successful SELECT statement. I'm not sure if this is relevant to the underlying issue.

I am not sure why the SQLMoreResults error is thrown on the first encounter here however it exits immediately without moving on to the 'SELECT 1;', 'SELECT 2;' and then exiting on the second encounter of Divide by Zero as it did previously.

Essentially I am looking for the full output of all steps both successful statements and errors so if multiple errors are encountered then they can all be logged at the Python level rather than needing to move between Python and SQL for debugging.

I think 'SET NOCOUNT ON;' has been suggested for similar errors which I have tried with no success.

[v-chojas]

Could you post an ODBC trace of the two cases?

[RunSeven]

ODBC Trace Attached.

First File
initial_error.log

Contains trace for the first instance where we hit the Divide by Zero immediately.

Second File
initial_success.log

Contains trace where we initially receive the 'SELECT 0;'

[v-makouz]

This issue happens because inside cursor.nextset() there is a call to SQLMoreResults and if it returns an error, pyODBC will free the statement handle and getting more results becomes impossible. The problem is that there is no obvious way to tell if SQLMoreResults returned an error because it itself failed (in which case the statement should be freed), or because the result that it got contains an error (in which case it shouldn't).

I'm going to try to think of a solution to his, but if anyone else has suggestions feel free to share.

[RunSeven]

I am able to obtain the output when running SQLCMD on the same procedure (same driver etc.) so have attached trace of this executing the 'initial error' stored procedure.

There appears to me to be some overlap of the API calls at critical points but then a fair amount of difference in what happens in between. This could mean that it is too different in structure to be of use even as a starting point but it is as close as I have to a suggestion

sqlcmd_initial_error.log

[v-chojas]

sqlcmd is designed specifically for msodbcsql so it can make assumptions on how SQLMoreResults behaves; not so with pyODBC which is generic and has to work with other ODBC drivers too.

[hopechols]

try this, it's built on top of ideas from @gordthompson

def sql_execute_pyodbc(self, sql_script, as_pdf=False):
    pyodbc.pooling = False
    connection_url = sqlalchemy.engine.URL.create(
        "mssql+pyodbc",
        username = self.login_user,
        password = self.login_pass,
        host = self.login_server,
        database = self.login_db,
        query={
            "driver": "ODBC Driver 17 for SQL Server",
            "autocommit": "True",
        },
    )
    engine = sqlalchemy.create_engine(connection_url).raw_connection()
    if sqlalchemy.__version__>='2.0.0': sql_script = sqlalchemy.text(sql_script)
    sql_cols, sql_result =[], []
    with engine.cursor() as cursor:
      cursor.execute(sql_script)
      if cursor.description and cursor.rowcount: 
          sql_cols, sql_result = [col[0] for col in cursor.description], cursor.fetchall()
      while cursor.nextset(): pass
      if len(sql_result)>0:
        result = pandas.DataFrame(np.array(sql_result), columns=sql_cols)
      else:
        result = pandas.DataFrame({"sql_script":[sql_script],"result":["True"]})
    return result if as_pdf else spark.createDataFrame(result)

[mkleehammer]

How bad would it be to assume the SQLMoreResults call did not fail? What if we assume it indicates that there is an error to retrieve? In the super rare case that is wrong, the next API call should fail also.

Is this a driver bug? Should it really be returning an error status? Not that it helps any.

[mkleehammer]

I think my suggestion above might be the way to go:

https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/multiple-results?view=sql-server-ver16

If one of the statements in a batch fails, SQLMoreResults will return either SQL_ERROR or SQL_SUCCESS_WITH_INFO. If the batch was aborted when the statement failed or the failed statement was the last statement in the batch, SQLMoreResults will return SQL_ERROR. If the batch was not aborted when the statement failed and the failed statement was not the last statement in the batch, SQLMoreResults will return SQL_SUCCESS_WITH_INFO. SQL_SUCCESS_WITH_INFO indicates that at least one result set or count was generated and that the batch was not aborted.

Since it is documented to return SQL_ERROR, do not assume the call itself failed.