How to reduce exceptions due to timeouts

Automate and integrate using the SQL comparison API

Moderators: Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

How to reduce exceptions due to timeouts

Postby SQLAdminCJ » Wed Jun 20, 2012 8:43 am

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.
SQLAdminCJ
 
Posts: 10
Joined: Wed Dec 28, 2011 2:03 am

Postby Brian Donahue » Thu Jun 21, 2012 4:02 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Re:

Postby SQLAdminCJ » Fri Jun 22, 2012 9:34 pm

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.
SQLAdminCJ
 
Posts: 10
Joined: Wed Dec 28, 2011 2:03 am


Return to SQL Comparison SDK 10

Who is online

Users browsing this forum: No registered users and 0 guests