ErrorHandling在SQL Server次链式存储过程使用的ExecuteReader [英] ErrorHandling in SQL Server chained stored proc with ExecuteReader
问题描述
我串联起来的一些存储过程和所遇到的越来越错误处理正常工作的一些问题。由于部分这些存储过程是长期运行的，我做了使用 SqlInfoMessageEventHandler 和code，如 RAISERROR（'什么'，10 ，1）WITH NOWAIT 内所存储的程序，以报告操作到用户的进展。为了做到这一点，我读过，你不能使用cmd.ExecuteNonQuery（），而是必须使用cmd.ExecuteReader（）。我想这一点，它似乎是如此;我没有看到的ExecuteNonQuery任何消息。
我发现，虽然现在的问题是，当我使用的ExecuteReader，如果我的存储过程抛出任何错误，那么他们将被忽略。我的意思是我的.NET应用程序调用从try块这个存储过程，当存储的过程中遇到错误（如选择1/0），执行将不会进入catch块，而是提交我的事务。这样的一个例子是如下;
IF EXISTS（SELECT * FROM sys.objects中的WHERE OBJECT_ID = OBJECT_ID（N'[DBO]。[TempTstTbl]）和类型（N'U'））
DROP TABLE [DBO]。[TempTstTbl]
CREATE TABLE [DBO]。[TempTstTbl]（
步骤INT，
瓦尔VARCHAR（50）
）
IF EXISTS（SELECT * FROM sys.objects中的WHERE OBJECT_ID = OBJECT_ID（N'[DBO]。[儿童]）和类型（N'P，N'PC'））
DROP PROCEDURE [DBO]。[儿童]
走
CREATE PROCEDURE [DBO]。[儿童]
如
开始
BEGIN TRY
INSERT INTO [DBO]。[TempTstTbl]（步骤，缬氨酸）VALUES（1，'从子故障前）
选择1/0
--RAISERROR（这应该真的失败，16，2）
INSERT INTO [DBO]。[TempTstTbl]（步骤，缬氨酸）VALUES（2，'从子故障后）
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR（4000）;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
选择
@ErrorMessage = ERROR_MESSAGE（）
@ErrorSeverity = ERROR_SEVERITY（），
@ErrorState = ERROR_STATE（）;
- 使用RAISERROR CATCH块返回错误内
- 有关导致原来的错误信息
- 执行跳转到CATCH块。
RAISERROR（@ErrorMessage， - 短信息。
@ErrorSeverity， - 严重性。
@ErrorState - 国家。
）;
END CATCH;
结束
走
IF EXISTS（SELECT * FROM sys.objects中的WHERE OBJECT_ID = OBJECT_ID（N'[DBO]。[家长]）和类型（N'P，N'PC'））
DROP PROCEDURE [DBO]。[家长]
走
CREATE PROCEDURE [DBO]。[家长]
如
开始
BEGIN TRY
INSERT INTO [DBO]。[TempTstTbl]（步骤，缬氨酸）VALUES（1，'从父前孩子）
执行[DBO]。[儿童]
INSERT INTO [DBO]。[TempTstTbl]（步骤，缬氨酸）VALUES（2，'从父过了一会儿'）
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR（4000）;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
选择
@ErrorMessage = ERROR_MESSAGE（）
@ErrorSeverity = ERROR_SEVERITY（），
@ErrorState = ERROR_STATE（）;
- 使用RAISERROR CATCH块返回错误内
- 有关导致原来的错误信息
- 执行跳转到CATCH块。
RAISERROR（@ErrorMessage， - 短信息。
@ErrorSeverity， - 严重性。
@ErrorState - 国家。
）;
END CATCH;
结束
走
EXEC [DBO]。[家长]
SELECT * FROM [DBO]。[TempTstTbl]
使用一些.NET code等;
私人无效button4_Click（对象发件人，EventArgs的）
{
使用（SqlConnection的康恩=新的SqlConnection（@数据源= XPDEVVM \ XPDEV;初始目录= MyTest的;集成安全性= SSPI;））
{
conn.Open（）;
使用（的SqlTransaction反= conn.BeginTransaction（））
{
使用（CMD的SqlCommand = conn.CreateCommand（））
{
cmd.Transaction =反;
cmd.CommandText =[设置] [家长];
cmd.CommandType = CommandType.StoredProcedure;
尝试
{
- cmd.ExecuteReader（）; - 使用这种替代的ExecuteNonQuery是指除以在存储过程0错误被忽略，一切都致力于:(
cmd.ExecuteNonQuery（）;
trans.Commit（）;
}
赶上（例外前）
{
trans.Rollback（）;
}
}
}
}
}
没有人有任何想法如何，我可以从我的存储过程让我的进度消息，但还是赶上.NET异常，如果发生在存储过程中的错误？
看来，这个问题从DataReader的工作方式茎。我第一次碰到一个暗示尼尔森Rothermel评论中这个答案到一个单独的SO问题。
然后我再念对这个线程此问题的详细信息其中，理查德·麦金农给出了下面的例子，以此来解决这个问题（我的重点）;
尝试同样的严重性> 10（我用11）和SELECT行添加到SP（我检查对于Northwind数据库）。你会看到的 当你有一个选择检索数据，你永远不会得到的 例外。
[原创信息剪裁]
在这种情况下的ExecuteReader（）将启动过程中的第一组 结果，在SELECT语句。要获得下一组结果时，对 RAISERROR，您需要调用NextResult（）
所以只是看看错误，我会改变我的code从之前 看起来是这样;
SqlDataReader的博士= Command.ExecuteReader却（）; dr.NextResult（）; dr.Close（）;
我尝试添加dr.NextResult（）给我的code和它似乎对我来说已经解决了这一问题。
这则让我从我的存储过程获取信息的邮件，也可以让我赶在存储过程中出现的错误也。
I am chaining together some stored procedures and have run into some issues in getting error handling working correctly. As some of these stored procedures are long running, I've made use of SqlInfoMessageEventHandler and code such as RAISERROR('whatever',10,1) WITH NOWAIT within the stored procedures to report the progress of the operation to the user. In order to do this, I've read that you can't use cmd.ExecuteNonQuery() and instead have to use cmd.ExecuteReader(). I've tried this, and it does seem to be the case; I don't see any messages from ExecuteNonQuery.
The problem I found though is that when I use ExecuteReader, if my stored proc throws any errors, then they are ignored. By this I mean my .NET Application that calls this stored proc from a try block and when the stored proc encounters an error (like SELECT 1/0), execution never enters the catch block, but instead commits my transaction. An example of this is as follows;
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]
With some .NET code such as;
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();
}
}
}
}
}
Does anyone have any ideas as to how I can get my progress messages from my stored proc, but still catch .NET exceptions if errors occur in the stored procedure?
It seems that this issue stems from the way DataReader works. I first came across a hint in Nelson Rothermel comment in this answer to a separate SO question.
I then further read details of this issue described on this thread where Richard McKinnon gives the following example as a way to address the issue (my emphasis);
Try the same with severity > 10 (I am using 11) and add the SELECT line to the SP (I checked using the NorthWind DB). You'll see that when you have a SELECT that retrieves data, you never get the exception.
[Original message clipped]
In this case ExecuteReader() will start process the first set of results, the SELECT statement. To get the next set of results, the RAISERROR, you need to call NextResult().
So to just look at the errors I would change my code from before to look like this;
SqlDataReader dr = command.ExecuteReader(); dr.NextResult(); dr.Close();
I tried adding dr.NextResult() to my code and it does seem to have fixed the issue for me.
This then allows me to get info messages from my stored proc, but also allows me to catch errors raised in the stored proc also.
这篇关于ErrorHandling在SQL Server次链式存储过程使用的ExecuteReader的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
