Losing exceptions generated within Stored Procedures in MS SQL Server 2012

[lyallp]

Hi, I have a strange one, whereby I seem to be losing exceptions from MS SQL Server.
When I invoke a stored procedure, using the 'ProofOfPyodbcError.py' (attached), I receive exceptions up until I do some sort of Update DDL in the Stored Procedure.
Prior to the Insert/Update/Delete, I can receive exceptions.

The ProofOfPyodbcError.py script has hard coded Database connection string, so it will need to be tweaked if you wish to run it.

After the Insert/Update/Delete, it doesn't matter what I do, no exceptions are received by the python code.

The attached Proof_Of_Error_DDL.sql will establish a test table and stored procedure, although, the script does have a hard coded Database name in it.

The ODBC settings are done using C:\Windows\SysWOW64\odbcad32.exe to create a "System DSN", which is used in the Database connection string.

Am I simply doing something wrong with my Python code or is there something deeper?

I originally hit this whilst using SQLAlchemy but have narrowed it down to pyodbc.

Environment:

Windows 7 Microsoft Windows NT 6.1 (7601) 64 bit
Python 2.7.9 (default, Dec 10 2014, 12:24:55) [MSC v.1500 32 bit (Intel)] on win32
pyodbc-3.0.10.-cp27-none-win32.whl
Microsoft SQL Server Enterprise (64-bit) 2012 (11.0.3128.0) Unclustered, stand alone on a development workstation

Suggestions more than welcomed!

DDL to create Stored Procedure and Table used by Stored Procedure.

use mssql_db_dev

drop table [dbo].[proof_of_error];

CREATE TABLE [dbo].[proof_of_error](
    [col1] [int] NOT NULL,
    [col2] [int] NOT NULL,
    [col3] [smallint] NULL,
    CONSTRAINT [PK_proof_of_error] PRIMARY KEY NONCLUSTERED 
(
    [col1] ASC,
    [col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];


USE [mssql_db_dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DROP PROCEDURE [dbo].[spProofOfError]
GO

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spProofOfError] @doDelete int = 0
AS
BEGIN
    declare @anInteger int;
    set @anInteger = 123;
    --print '@doDelete = '+ cast(@doDelete as nvarchar)

    if @doDelete = 1
    BEGIN
        BEGIN TRY
        --delete nothing.
            --print 'executing delete'
            DELETE [proof_of_error]
             WHERE col1 = 1 AND col2 = 1;
            --print 'Execute delete completed.'
        END TRY
        BEGIN CATCH
            --print 'Exception whilst executing delete'
            set @anInteger = 'UNEXPECTED DELETE ERROR';
        END CATCH;
    END
    ELSE
    BEGIN
        --print 'Did not Execute Delete'
        set @anInteger = 'DID NOT EXECUTE DELETE';
    END;
    -- print 'exception regardless of which path taken'
    set @anInteger = 'ERROR REGARDLESS OF PATH';

    --print 'Returning @anInteger = '+cast(@anInteger as nvarchar)
    return @anInteger
END
GO

Proof to show that SQL Server does raise exceptions when expected, using the execution mechanism as captured by SQL Trace.

use mssql_db_dev
declare @p1 int

BEGIN TRY
print 'Running Delete'
set @p1=NULL
exec sp_prepexec @p1 output,
N'@P1 int',
N'
declare @result int;
EXEC @result = spProofOfError @P1;
select @result as result;
',1;
END TRY
BEGIN CATCH
    SELECT 'With Delete'
        ,ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

BEGIN TRY
print 'Without running Delete'
 set @p1=NULL
 exec sp_prepexec @p1 output,
 N'@P1 int',
 N'
 declare @result int;
 EXEC @result = spProofOfError @P1;
 select @result as result;
 ',
 0;
END TRY
BEGIN CATCH
    SELECT 'Without Delete'
        ,ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Python code using pyodbc to invoke the above stored procedure, expecting the same results as manually executing the stored proc in SQL Server Management Studio

from __future__ import print_function
import logging
logging.basicConfig(level=logging.DEBUG)

#Default logging, overridden by command line option right down the bottom of this file
logger = logging.getLogger("ProofOfError")
logger.setLevel(logging.DEBUG)


import pyodbc
print("pyodbc version = ", pyodbc.version)
logger.debug("Attempting connection to ***DEV WORKSTATION*** SQL Server for info (hard coded credentials for ODBC PFdb)")

db = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=mssql_db_dev;UID=db_user_dev;PWD=dev')
logger.debug("Connection succeeded")

executeStoredProcedureSQL = """
declare @result int;
EXEC @result = spProofOfError ?;
select @result as result;
"""
#executeStoredProcedureSQL = """select count(1)+1 from dbo.proof_of_error;"""
logger.debug("SQL = '%s'", executeStoredProcedureSQL)

try:
    logger.info('\n\nRunning WITHOUT DELETE')
    cursor = db.cursor()
    cursor.execute(executeStoredProcedureSQL, (0,))  # zero does not run the DELETE
    logger.debug("cursor.description = ", cursor.description)
    if cursor.description:
        row = cursor.fetchone()
        if row:
            logger.debug("column[0] = %s", repr(row[0]))
        else:
            logger.debug("no rows returned")

except Exception as ex:
    logger.error("exception ignored - "+repr(ex))
finally:
    if cursor:
        cursor.close
    else:
        logger.debug("Cursor None")

try:
    logger.info('\n\nRunning WITH DELETE')
    cursor = db.cursor()
    cursor.execute(executeStoredProcedureSQL, (1,))   # 1 runs the DELETE
    logger.debug("cursor.description = %s", cursor.description)
    if cursor.description:
        row = cursor.fetchone()
        if row:
            logger.debug("column[0] = %s", repr(row[0]))
        else:
            logger.debug("no rows returned")

except Exception as ex:
    logger.error("exception ignored - "+repr(ex))
finally:
    if cursor:
        cursor.close
    else:
        logger.debug("Cursor None")

[gordthompson]

I notice that your stored procedure does not include

SET NOCOUNT ON;

as the first executable statement. Doing so could very likely avoid the issue.

[githubmlai]

I'm experiencing a similar issue. When pyodbc executes the following stored procedure, the error (divide by 0) is not caught

Here is the stored proc

IF OBJECT_ID('dbo.spSwallowError') IS NULL
EXEC ('CREATE PROCEDURE dbo.spSwallowError AS SET NOCOUNT ON;');

GO

ALTER PROCEDURE dbo.spSwallowError
AS
BEGIN
SELECT 1;
SELECT 1 / 0;

 END;

[gordthompson]

@githubmlai - The SELECT 1; statement has already put a result set into the "pipeline" back to the client, so you won't see the exception until you call nextset() to try and retrieve the second result set:

>>> import pyodbc
>>> conn = pyodbc.connect("DSN=myDb;")
>>> crsr = conn.cursor()
>>> crsr.execute("{CALL dbo.spSwallowError}")
<pyodbc.Cursor object at 0x03041218>
>>> rows = crsr.fetchall()
>>> rows
[(1, )]
>>> crsr.nextset()
Traceback (most recent call last):
  File "<pyshell#6>", line 1, in <module>
    crsr.nextset()
pyodbc.DataError: ('22012', '[22012] [Microsoft][SQL Server Native Client 10.0][SQL Server]Divide by zero error encountered. (8134) (SQLMoreResults)')

[githubmlai]

Thanks! Any suggestions on how to guarantee pyodbc rethrows the error? Should I always fetch all the rows and then call next set repeatedly?

[gordthompson]

@githubmlai - Essentially, yes. You'd just keep calling nextset() until it returned False. The exception, if any, would either get thrown immediately or on one of the nextset() calls.

[kumar1838]

ERROR_NUMBER() AS ErrorNumber
ERROR_SEVERITY() AS ErrorSeverity
ERROR_STATE() AS ErrorState
ERROR_PROCEDURE() AS ErrorProcedure
ERROR_LINE() AS ErrorLine
ERROR_MESSAGE() AS ErrorMessage;

how to get these parameters in pyodbc using python? can anyone help me out please!!!!