在SQL Server ErrorHandling中次链式存储过程与的ExecuteReader(E

2019-07-31 13:59发布

我串联起来的一些存储过程和所遇到的越来越错误处理正常工作的一些问题。 由于一些这些存储过程是长期运行的,我做了使用SqlInfoMessageEventHandler和代码,如RAISERROR('whatever',10,1) WITH NOWAIT在存储过程中的报告操作的用户进度。 为了做到这一点,我读过,你不能使用cmd.ExecuteNonQuery(),而是必须使用cmd.ExecuteReader()。 我已经试过这一点,它似乎是如此; 我没有看到的ExecuteNonQuery任何消息。

我发现,虽然问题是,当我使用的ExecuteReader,如果我的存储过程引发任何错误,那么他们将被忽略。 我的意思是要求从try块这个存储过程,当存储的过程中遇到错误(如选择1/0),执行将不会进入catch块,而是提交我的交易我的.NET应用程序。 这样的一个例子是如下;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempTstTbl]') AND type in (N'U'))
DROP TABLE [dbo].[TempTstTbl]

CREATE TABLE [dbo].[TempTstTbl] (
    Step INT,
    Val  VARCHAR(50)
)

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Child]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[Child]
GO 
CREATE PROCEDURE [dbo].[Child]
AS
BEGIN
BEGIN TRY
    INSERT INTO [dbo].[TempTstTbl] (Step, Val) VALUES (1, 'FROM CHILD BEFORE FAULT')
    SELECT 1/0
    --RAISERROR ('This should really fail', 16, 2)
    INSERT INTO [dbo].[TempTstTbl] (Step, Val) VALUES (2, 'FROM CHILD AFTER FAULT')
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Parent]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[Parent]
GO
CREATE PROCEDURE [dbo].[Parent]
AS
BEGIN
BEGIN TRY
    INSERT INTO [dbo].[TempTstTbl] (Step, Val) VALUES (1, 'FROM PARENT BEFORE CHILD')
    Exec [dbo].[Child]
    INSERT INTO [dbo].[TempTstTbl] (Step, Val) VALUES (2, 'FROM PARENT AFTER CHILD')
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;
END
GO

EXEC [dbo].[Parent]
SELECT * FROM [dbo].[TempTstTbl]

对于某些.NET代码等;

private void button4_Click(object sender, EventArgs e)
{
    using (SqlConnection conn = new SqlConnection(@"Data Source=XPDEVVM\XPDEV;Initial Catalog=MyTest;Integrated Security=SSPI;"))
    {
        conn.Open();
        using (SqlTransaction trans = conn.BeginTransaction())
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.Transaction = trans;
                cmd.CommandText = "[cfg].[Parent]";
                cmd.CommandType = CommandType.StoredProcedure;

                try
                {
                    -- cmd.ExecuteReader(); -- Using this instead of ExecuteNonQuery means the divide by 0 error in the stored proc is ignored, and everything is committed :(
                    cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                }
            }
        }
    }
}

没有人有任何想法,我怎么可以从我的存储过程让我的进度消息,但仍然在发生在存储过程中的错误赶上.NET异常?

Answer 1:

看来,这个问题从DataReader的工作方式造成的。 我第一次碰到尼尔森Rothermel评论暗示在来到这个答案到一个单独的SO问题。

然后,我再阅读说明这个问题的详细信息, 该线程在那里理查德·麦金农给出了下面的例子,以此来解决这个问题(我的重点);

试着用严重性> 10(我用11)相同,SELECT行添加到SP(我检查使用罗斯文DB)。 你会看到,当你有一个选择检索数据,你永远不会例外。

[原文消息截取]

在这种情况下的ExecuteReader()将启动过程的第一个结果集,SELECT语句。 要获得下一组结果时,RAISERROR, 你需要调用NextResult()。

所以光看我会改变我的代码之前,像这样的错误;

SqlDataReader的DR = Command.ExecuteReader却(); dr.NextResult(); dr.Close();

我尝试添加dr.NextResult()到我的代码和它似乎已经解决了这一问题对我来说。

这就让我从我的存储过程获取信息的邮件,也可以让我赶在存储过程中也提出了错误。



文章来源: ErrorHandling in SQL Server chained stored proc with ExecuteReader