SQL Server异常：TCP提供程序，错误：0 - 指定的网络名称不再可用

Good Morning!

早上好！

Apologies in advance, I do realize that similar questions have been asked but working through them has not resolved my issues hence this post.

提前道歉，我确实已经意识到已经提出了类似的问题但是通过这些问题并没有解决我的问题。

Before posting exception details here is the general overview:

在发布异常详细信息之前，这里是一般概述：

I have .NET 4.0 smart desktop application using SQL server (2005 and 2008r2) for data storage. In the database I have a table which logs when an entity has been changed. This allows other instances of the application to check for changes and reload certain data. The table has three fields:

我有使用SQL Server（2005和2008r2）进行数据存储的.NET 4.0智能桌面应用程序。在数据库中，我有一个表，用于记录实体何时被更改。这允许应用程序的其他实例检查更改并重新加载某些数据。该表有三个字段：

Id [int], EntityName [nvarchar(4000)], ChangeDateTime [datetime]

Id is the primary key and there is an index on the ChangeDateTime field.

Id是主键，ChangeDateTime字段上有一个索引。

To make this work the application checks this table at certain intervals or when actions are performed to see if an update has occurred by retrieving the latest change datetime for a certain entity and comparing that with an internally stored value.

为了使这项工作，应用程序以特定间隔检查此表，或者在执行操作时通过检索某个实体的最新更改日期时间并将其与内部存储的值进行比较来查看是否发生了更新。

The query looks as follows:

查询如下所示：

Query: SELECT TOP(@p2) MAX([BondFM].[dbo].[EntityTypeChangedLog].[ChangeDateTime]) AS [ChangeDateTime] FROM [BondFM].[dbo].[EntityTypeChangedLog]   WHERE ( [BondFM].[dbo].[EntityTypeChangedLog].[ChangedEntityTypeName] = @p3)
Parameter: @p2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Parameter: @p3 : String. Length: 4000. Precision: 0. Scale: 0. Direction: Input. Value: "SampleEntityName".

So the table only receives two interactions from running applications:

因此，该表仅接收来自运行应用程序的两个交互：

A Select request as shown above to retrieve the latest update datetime for an entity
如上所示选择请求以检索实体的最新更新日期时间
An Insert request when an entity has changed to add a new row
实体更改为添加新行时的插入请求

The problem is that I get many exception of the following type when carrying out the select statement:

问题是在执行select语句时我得到以下类型的许多异常：

Message : An exception was caught during the execution of a retrieval query: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.).

Sometimes it also comes up as:

有时它也会出现：

TCP Provider, error: 0 - The semaphore timeout period has expired or
TCP提供程序，错误：0 - 信号量超时期限已过期或
TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.
TCP提供程序，错误：0 - 远程主机强制关闭现有连接。

Additionally I get the following inner exception which hasn't helped me to narrow down the problem:

此外，我得到以下内部异常，这没有帮助我缩小问题范围：

    Inner Exception
    ---------------
    Type : System.Data.SqlClient.SqlException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
    Message : A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
    Source : .Net SqlClient Data Provider
    Help link : 
    Errors : System.Data.SqlClient.SqlErrorCollection
    Class : 20
    LineNumber : 0
    Number : 64
    Procedure : 
    Server : SQLServer
    State : 0
    ErrorCode : -2146232060
    Data : System.Collections.ListDictionaryInternal
    TargetSite : Void OnError(System.Data.SqlClient.SqlException, Boolean)
    Stack Trace :    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
       at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
       at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
       at System.Data.SqlClient.TdsParserStateObject.ReadByte()
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteScalar()
       at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.ExecuteScalar()

I have read up on and tested various issues with connectivity which have not helped and I was wondering if there is something happening inside the table (e.g. locks?) which causes this?

我已经阅读并测试了连接中的各种问题，这些问题没有帮助，我想知道表格内是否发生了某些事情（例如锁？）导致这种情况？

Thanks for reading and any help or pointers would be appreciated!

感谢阅读和任何帮助或指针将不胜感激！

Mike

麦克风

2 个解决方案

#1

You can try by increasing connection timeout in your connection string.

您可以尝试增加连接字符串中的连接超时。

    private static void OpenSqlConnection()
     {
        string connectionString = GetConnectionString();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
          connection.Open();
          Console.WriteLine("State: {0}", connection.State);
          Console.WriteLine("ConnectionTimeout: {0}",
          connection.ConnectionTimeout);
        }
     }

     static private string GetConnectionString()
     {
      // To avoid storing the connection string in your code,  
      // you can retrieve it from a configuration file, using the  
     // System.Configuration.ConfigurationSettings.AppSettings property  
        return "Data Source=(local);Initial Catalog=AdventureWorks;"
      + "Integrated Security=SSPI;Connection Timeout=30";
    }

#2

CAUSE

原因

The error is being caused by a timeout exception for long running queries. In previous versions of Visual Studio .NET, the exception was properly represented as a exception with a timeout description.

该错误是由长时间运行的查询的超时异常引起的。在以前版本的Visual Studio .NET中，异常被正确表示为具有超时描述的异常。

RESOLUTION

解析度

Set the commandtimeout property of the command object to an appropriate value. Use a value of zero to wait without an exception being thrown.

将命令对象的commandtimeout属性设置为适当的值。使用零值等待而不抛出异常。

https://support.microsoft.com/nl-nl/kb/555938

#1