17

We have an application that access the database (Sql server 2014 enterprise edition). The application calls a stored procedures to access the database. Everything was working fine until recently start sending the following error and stop the applications. Restarting the app fix the issue temporarily but facing the same error latter.

Error: 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.)

I did a lot of research which most of them pointing out as a network issue but could not find anything to actually solve the problem. Do anyone know what change I should have to make on the database side to resolve this issue. I highly appreciate any suggestions.

0

5 Answers 5

11

Sounds like spotty DNS resolution issues or long running queries.

For DNS trouble-shooting

As a Band-Aid and trouble-shooting aid add and entry to the hosts file on your app servers (not the SQL Server) at c:\windows\system32\drivers\etc

SQLServerIPAddress SQLServerName

Example:

172.16.0.5 ProductionSQLBox

That way, the SQL Server name will be resolved by the hosts file until you can find the real problem for what's going on with name resolution.

Be sure and test the hosts file by pinging the SQL box from the app server via a command line. Or, alternately create a DSN with the ODBC Administrator within Control Panel and test the connection there.

For long running queries

Implement a longer command timeout value for the application connection strings connecting to the SQL Server.

Example

<connectionStrings> 
    <add name="webconfigconnectionString" connectionString="server=SQLServerName;database=dbName;uid=u‌​serName;password=ABC‌​123;Connect Timeout=120" />
</connectionStrings>
2
  • 1
    Note that the property is "Connect Timeout" (or "Connection Timeout"), not just "Timeout"
    – mchar
    Jul 9, 2020 at 6:43
  • 1
    @mchar thanks! changed it.
    – Sting
    Jul 9, 2020 at 13:45
7

This is not a database issue. There is no change on database side that can resolve this issue. This error is caused by high network bandwidth or error in your application. I would suggest you to forward this error to your Wintel/Network team of your company. They can change the network switch or manage the network bandwidth to stop this error.

4

This looks like an application issue.

My opinion is that the application is taking the assumption that the SQL connection is always available (no check or test before running commands) so when the line goes down or anything makes the connection drop/close the application produces that message.

To reproduce, simply kill the SQL session on the SQL server and you will get that very same a similar message.

0

Another cause for this message bit me in the rear just recently (though I was using Sql Server 2019).

For me, the error message was emitted because I mistyped my password!

I was completely thrown off for a while, because the message is very nonindicative for this case.

-2

Moved to hotspot and it's fixed for me - I'm no expert, but I'd say it has something to do with the internet connection / connectivity settings / router etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.