Sql异常与.net 4& EF [英] Sql Exception With .net 4 & EF

问题描述

我们正在使用.net 4 MVC2与EF& Sql Server 2005。

对于一些请求，它很少发生，只有当我们进行搜索时，通过映射到执行全文搜索的存储过程的类实现，获取
的例外：

  [SqlException（0x80131904））超时过期。在完成操作或服务器之前经过的超时时间没有响应。] 
 System.Data.SqlClient.SqlConnection.OnError（SqlException异常，Boolean breakConnection）+2030802 
 System.Data.SqlClient。 SqlInternalConnection.OnError（SqlException异常，Boolean breakConnection）+5009584 
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning（）+234 
 System.Data.SqlClient.TdsParser.Run（RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj）+2275 
 System.Data.SqlClient.SqlDataReader.ConsumeMetaData（）+33 
 System.Data.SqlClient.SqlDataReader.get_MetaData（）+86 
系统。 Data.SqlClient.SqlCommand.FinishExecuteReader（SqlDataReader ds，RunBehavior runBehavior，String resetOptionsString）+311 
 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds（CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，Bool ean async）+987 
 System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，String method，DbAsyncResult result）+162 
 System.Data.SqlClient.SqlCommand.RunExecuteReader（ CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，String方法）+32 
 System.Data.SqlClient.SqlCommand.ExecuteReader（CommandBehavior行为，String方法）+141 
 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader（ CommandBehavior行为）+12 
 System.Data.Common.DbCommand.ExecuteReader（CommandBehavior行为）+10 
 System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands（EntityCommand entityCommand，CommandBehavior行为）+443 
 
 [EntityCommandExecutionException：执行命令定义时发生错误。查看内部异常的详细信息。] 
 System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands（EntityCommand entityCommand，CommandBehavior行为）+479 
 System.Data.Objects.ObjectContext.CreateFunctionObjectResult（EntityCommand entityCommand，EntitySet entitySet， EdmType edmType，MergeOption mergeOption）+182 
 System.Data.Objects.ObjectContext.ExecuteFunction（String functionName，MergeOption mergeOption，ObjectParameter [] parameters）+218 
 System.Data.Objects.ObjectContext.ExecuteFunction（String functionName，ObjectParameter []参数）+53 
  

如果在发生错误后重试搜索或不同的术语），它定期工作。

任何建议不胜感激

谢谢

--MB

THOUGHT 1：异常似乎发生在连接到sql server之后，而存储过程正在执行时，我是否正确？如果是这种情况，那么我需要扩展查询执行超时，而不是连接超时？这是可能的吗？

THOUGHT 2：也许我错了，这可能是一个陈旧的连接池？在Java世界中，您可以在应用程序获取之前传递在连接上执行的sql，以确保连接已打开并正常工作？可能是导致这个。我无法在中找到这样的选项http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

THOUGHT 3：I 'm lost：）

解决方案

您可以尝试设置 ObjectContext.CommandTimeout 属性：

获取或设置所有对象上下文操作的超时值（以秒为单位）。空值表示将使用底层提供程序的默认值。

we are using .net 4 MVC2 with EF & Sql Server 2005.

For some requests, and it happens rarely, and only when we are doing search which is implemented with classes mapped to stored procedures performing full text search, we get the exception:

[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2030802
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009584
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +86
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +443

[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
   System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +479
   System.Data.Objects.ObjectContext.CreateFunctionObjectResult(EntityCommand entityCommand, EntitySet entitySet, EdmType edmType, MergeOption mergeOption) +182
   System.Data.Objects.ObjectContext.ExecuteFunction(String functionName, MergeOption mergeOption, ObjectParameter[] parameters) +218
   System.Data.Objects.ObjectContext.ExecuteFunction(String functionName, ObjectParameter[] parameters) +53

If the search is retried after the error occurs (with same or different term), it works regularly.

Any suggestions well appreciated

Thanks

--MB

THOUGHT 1: The exception seems to happen after the connection to sql server is made, and while the stored procedure is being executed, am I correct? If this is the case, then I would need to extend the query execution timeout and not the connection timeout? Is this possible, where?

THOUGHT 2: Perhaps I am wrong and this could be a stale connection in the pool? In Java world you can pass the sql that is executed on the connection before the app obtains it to make sure the connection is opened and working? Could that be causing this. I have not been able to locate such an option within http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

THOUGHT 3: I'm lost :)

解决方案

You can try setting the ObjectContext.CommandTimeout property:

Gets or sets the timeout value, in seconds, for all object context operations. A null value indicates that the default value of the underlying provider will be used.

这篇关于Sql异常与.net 4& EF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！