C#/SQL Server 2008R2存储过程错误处理 [英] C#/SQL Server 2008R2 Stored Procedure error handling

问题描述

fI有一个调用存储过程的应用程序.该存储过程应该在外部ODBC数据库中查询所有可用的表和数据.有一些已知的表会导致错误，我通过将执行包装在TRY ... CATCH块中来解决了这些错误.当我从SQL Management Studio运行存储过程时，它将显示错误的表并完成执行.但是，当我从C#应用程序调用它时，遇到第一个错误表时将返回超时错误.有没有一种方法可以强制存储过程继续执行，并且不将错误返回给C#应用程序?

SQL代码:
-@tableName是通过搜索外部ODBC数据库中的所有可用表派生的.

开始尝试
SET @command =''''
SELECT @command =``SELECT * INTO [''+ @tableName +'']来自外部数据库.''+ @tableName
执行(@command)
结束尝试
开始捕捉
PRINT''处理表时出错:" + @tableName
结束观看

C#代码:

sqlCommand.ExecuteNonQuery();
catch(SqlException sqle)
{
Console.WriteLine(sqle);
}

fI have an application that calls a stored procedure. The stored procedure is supposed to query an external ODBC database for all available tables and data. There are some known tables that cause an error and I have accounted for them by wraping the execution in a TRY...CATCH block. When I run the stored procedure from SQL Management Studio it will display the errord tables and finish executing. However, when I call it from my C# application it will return a Timeout error when it hits that first error table. Is there a way to force a Stored procedure to continue to execute and not return the error to the C# applicaion?

SQL Code:
- @tableName is derived from searching all available tables in the external ODBC database.

BEGIN TRY
SET @command = ''''
SELECT @command = ''SELECT * INTO ['' + @tableName + ''] FROM EXTERNAL DATABASE.'' + @tableName
EXECUTE(@command)
END TRY
BEGIN CATCH
PRINT ''Error processing the table: '' + @tableName
END CATCH

C# Code:

sqlCommand.ExecuteNonQuery();
catch(SqlException sqle)
{
Console.WriteLine(sqle);
}

推荐答案

CATCH块负责执行正确的操作.如果当前在catch中引发frmo DB错误，则会在C#中收到错误，但是如果忽略(poroper)错误，则在catch之后将继续执行.

为了回答更具体的问题，您是否可以将实际代码连同收到的错误一起发布...

mika

Hi,

The CATCH block is responsible of doing the proper operations. If you currently raise an error frmo DB in catch, you''ll receive an error in C# but if you omit (poroper) errors the execution continues after the catch.

To answer more specific could you post the actual code along the error you receive...

mika

您可以通过在连接字符串中添加;timeout=60来更改SqlServer等待连接的超时字符串.

您还可以设置

You can change the timeout in the connection string for SqlServer to wait for the connection by adding ;timeout=60 to the connection string.

You can also set the

cmd.CommandTimeout=60;

默认值为30秒.最好不要将其设置为无限超时(值为0).

尝试增加其中的最后一个，直到SP运行.不得已时，请使用0(零=无限)，因为这将表明可能存在另一个问题.

Default is 30 seconds.. Better not to set it as infinite timeout (value as 0).

Try increasing the last of these until the SP runs. As a Last resort, use 0 (zero = infinite) since this would indicate there might be another problem.

不幸的是，我在SQL存储过程中使用了GOTO:((有更多的时间，我会尝试找到更好的方法！


SQL代码:
-@tableName是通过搜索外部ODBC数据库中的所有可用表派生的.

开始尝试
SET @command =''''
SELECT @command =``SELECT * INTO [''+ @tableName +'']来自外部数据库.''+ @tableName
执行(@command)
结束尝试
开始捕捉
PRINT''处理表时出错:''+ @tableName
转到此处:
结束观看

此处:
继续执行！

Unfortunately, I used a GOTO :(( in the SQL Stored Procedure. If I get more time I will try to figure something better!


SQL Code:
- @tableName is derived from searching all available tables in the external ODBC database.

BEGIN TRY
SET @command = ''''
SELECT @command = ''SELECT * INTO ['' + @tableName + ''] FROM EXTERNAL DATABASE.'' + @tableName
EXECUTE(@command)
END TRY
BEGIN CATCH
PRINT ''Error processing the table: '' + @tableName
GOTO Here:
END CATCH

Here:
Continue execution!!!

这篇关于C#/SQL Server 2008R2存储过程错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！