CODEWITHSUNDEEP
c#sqlasp.netsql-servertimeout
Andy
Andy

Reputation: 21

C# SQL connection timeout

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.

Upvotes: 1

Views: 10579

Answers (2)

JanR
JanR

Reputation: 6132

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

Upvotes: 1

Manbir Singh
Manbir Singh

Reputation: 1

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...

Upvotes: 0

Related Questions