SQL Server Exception: TCP Provider, error: 0 - The specified network name is no longer available

Question

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:

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

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

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:

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 Provider, error: 0 - An existing connection was forcibly closed by the remote host.

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

Answer 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";
    }

Answer 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.

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.

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