Connection timeout

  • Thread starter Thread starter Ant
  • Start date Start date
A

Ant

Hi,
I am trying to run a SP that takes about 90 seconds to complete. If i run it
from SQL Server, it completes ok but if I run it from my .NET application, I
get a timeout error after about 40 seconds.
I set the Connection timeout in the connection string builder to 180 but it
still time out well before 3 minutes.
Is there anything else I should check?
Many thanks for any ideas on this
Ant

below is the script:

SqlConnectionStringBuilder connectionsString = new
SqlConnectionStringBuilder();


connectionsString.DataSource = "myserver";
connectionsString.InitialCatalog = backup.EpicorDB;
connectionsString.IntegratedSecurity = true;
connectionsString.ConnectTimeout = 1800;

using (SqlConnection connection = new
SqlConnection(connectionsString.ToString()))
{
// create the command to call the SP
SqlCommand commandBackup = new SqlCommand("MyBackupLiveData",
connection);
commandBackup.CommandType = CommandType.StoredProcedure;

// set an event to indicate SP has completed
commandBackup.StatementCompleted += new
StatementCompletedEventHandler(commandBackup_StatementCompleted);

// create & load the params for the SP
SqlParameter paramOffice = new SqlParameter("@Office",
backup.Office);
SqlParameter paramSecurityDB = new SqlParameter("@secmanDB",
backup.securityDB);
SqlParameter paramEpicorDB = new SqlParameter("@E4SEDB",
backup.EpicorDB);
SqlParameter paramControlDB = new SqlParameter("@ControlDB",
backup.ControlDB);
SqlParameter paramCompanyDB = new SqlParameter("@CompanyDB",
backup.CompanyDB);


// add the params to the command
commandBackup.Parameters.Add(paramOffice);
commandBackup.Parameters.Add(paramSecurityDB);
commandBackup.Parameters.Add(paramEpicorDB);
commandBackup.Parameters.Add(paramControlDB);
commandBackup.Parameters.Add(paramCompanyDB);


try
{
connection.Open();

// execute the SP
commandBackup.ExecuteNonQuery();
}
catch (Exception ex)
{
textboxCompleted.ForeColor = System.Drawing.Color.Red;
textboxCompleted.Text = ex.Message + "\r\n\r\nPlease check
the name of your database & try again";
textboxOfficeName.Text = string.Empty;
}



finally
{

connection.Close();
}
 
Mr. Beamer is (of course) right. But eventually, with an ASP.NET application
that has a SP that takes 90 seconds to run you will need to think about
Connection timeouts as well as the Connection pool overflows. If this is a
Windows application, then this is not as big of an issue. However, you need
to consider what the user is being asked to do during that 90 seconds. In
many cultures (like New York), the "user timeout" is typically 15 seconds.
At this time users tend to give up waiting. Of course in Arkansas this
timeout value is closer to 60 seconds and in Cleveland it's almost 4
minutes. ;) You need to either tighten up the SP so it runs faster or plan
an activity for the user--like launching solitaire or linking to a casino
gambling site.


--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
Back
Top