C# SQL connection timeout

Question

I have a very strange case of SQL connection timeout from an application written in C# .NET.

The SqlCommand.ExecuteNonQuery() is being used to sequentially execute several scripts in SQL Server, one after another. Each script contains a command to create just one table (no data update/insert/delete operations at all). For some reason, at one of the scripts, the SqlCommand.ExecuteNonQuery throws a timeout exception.

When I execute creation of all these tables in SQL Server Management Studio, they get executed just fine and almost instantaneously.

Does anyone has an idea what could be causing timeout when the tables are created from the application?

All sql scripts look similar like following:

SQL:

create table dbo.Test  
(  
  Code varchar(10) not null  
, Name varchar(50) not null  
, other columns...  

  primary key  
, unique key  
, foreign key
)

The scripts are shipped from C# using this code:

try
{
  using (SqlConnection conSQL = new SqlConnection ("[connection string]"))
  {
     using (SqlCommand cmdSQL = new SqlCommand(sSQL, conSQL))
     {
       cmdSQL.CommandTimeout = iTimeOut;
       conSQL.Open();

       cmdSQL.ExecuteNonQuery(); // this is where it jumps to catch part and                                                                            
                                 // throws out timeout exception
       conSQL.Close();
     }
  }
}
catch(Exception ex)
{
  throw (ex);
}

This is happening on the Test server, meaning nothing else is happening on the server while the application is executing these scripts.

Would you agree that the sql script is very simple right? I just don't understand why this one script is timing out and the rest don't. To note, nothing else is running on the Test sql server, the iTimeOut value is 30 seconds. — Andy
– Andy, Commented Mar 7, 2016 at 15:46

JanR · Accepted Answer · 2016-03-07 04:20:32Z

1

You can override the default time out setting for sql transactions by updating the machine.config, which can be found here:

%windir%\Microsoft.NET\Framework\[version]\config\machine.config

64-bit

%windir%\Microsoft.NET\Framework64\[version]\config\machine.config

At the end of the machine.config add or update the following line:

<system.transactions>
   <machineSettings maxTimeout="01:00:00" />  --> set this to desired value.
 </system.transactions>
</configuration>

If the above doesn't work, you can specify the Timeout setting for SqlCommand through code as well:

  using (SqlConnection connection = new SqlConnection(connectionString)) {
     connection.Open();
     SqlCommand command = new SqlCommand(queryString, connection);
     // Setting command timeout in seconds:
     command.CommandTimeout = 3600;
     try {
        command.ExecuteNonQuery();
     }
     catch (SqlException e) {
        Console.WriteLine(e);
     }
  }

more information here

edited Mar 7, 2016 at 4:20

answered Mar 7, 2016 at 3:09

JanR

6,1303 gold badges25 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Andy Over a year ago

I have raised the timeout till 1 minute, no help. Setting to a higher value is not good in my opinion. I am trying to find out the reason why this is happening

JanR Over a year ago

I believe the default is 10 minutes? so not sure why you think more than 1 minute is bad. However show us the code of the script that is causing it, maybe we can see what's happening that way?

JanR Over a year ago

Can you update your answer with the relevant c# code (Where you are calling these sql queries) and an example query?

Andy Over a year ago

I just wanted to say that it is a very simple script that executes under 1 second in management studio. Giving it to execute in 1 minute or more is kinda too much. There should be another option other than that...

JanR Over a year ago

what's the value of your iTimeout? if it's too low, it will cause a timeout error.

|

Manbir Singh · Accepted Answer · 2016-03-07 04:16:52Z

0

Just stop it from running and run again your problem will be solved.... It is generally occur first time only when their are lot of files to load maybe it is bug in visual studio. New: After stop try to refresh open Web page (in browser where error displays ) instead of relaunch it again...

edited Mar 7, 2016 at 4:16

answered Mar 7, 2016 at 3:28

Manbir Singh

11 bronze badge

1 Comment

Andy Over a year ago

I have done this several times, the problems occurs at each relaunch of the application.

Collectives™ on Stack Overflow

C# SQL connection timeout

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related