问题描述
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);
}
推荐答案
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.
这篇关于C#/SQL Server 2008R2存储过程错误处理的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!