How to reduce exceptions due to timeouts

SQLAdminCJ

Hey all,

My firm has been using the SDK to compare databases on servers at different locations. Unfortunately, it keeps crashing due to connectivity issues.

It crashes with exceptions such as:

System.Data.SqlClient.SqlException (0x80131904): 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.)

and

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

These problems are due to intermittent connectivity issues between the servers. I imagine that if I can increase the logout time I could reduce the incidence of these crashes. However, I'm not sure whether or not to change it in the connection string in the code for the diff utility or in the parameters of the SQL server instances being compared.

Does anyone have any ideas on how I can troubleshoot this issue? I would appreciate any advice anyone can provide.

Thanks.

Brian Donahue

Hello,

Even though you can adjust the timeout at the connection string, this is only the connection timeout - you are more than likely wanting to set the query timeout, which is actually hard-coded.

You can also consider avoiding Named Pipes connections, as they are an additional level of complexity over TCP/IP. If you don't need to use Named Pipes, you can force the connection to use TCP by prefacing the server name with "TCP:", for instance, use TCP:localhost for the server name.

The obvious answer is, fix the problems with the network. But that could be a complicated endeavor.

SQLAdminCJ

This helped me understand the issue better. I will run the idea of forgoing named pipes with my manager. Thank you.

Brian Donahue wrote:

Hello,

Even though you can adjust the timeout at the connection string, this is only the connection timeout - you are more than likely wanting to set the query timeout, which is actually hard-coded.

You can also consider avoiding Named Pipes connections, as they are an additional level of complexity over TCP/IP. If you don't need to use Named Pipes, you can force the connection to use TCP by prefacing the server name with "TCP:", for instance, use TCP:localhost for the server name.

The obvious answer is, fix the problems with the network. But that could be a complicated endeavor.