Stored Procedure "Timeout expired" exception on Windows Forms (C#)

Question

Hello. I've read some similar topics (most of them on this forum), but none of them helped.

 

I have a stored procedure which runs fine on SQL Server (in 0 ms). The problem occurs when i try to run it under a WindowsForm application (C#).

 

The following code

Code Block

SqlCommand cmdUpdate;
SqlConnection sqlCon = GetConnection();
cmdUpdate = new SqlCommand("StoredProcedure_Name", sqlCon);
cmdUpdate.CommandType = CommandType.StoredProcedure;
sqlCon.Open();
cmdUpdate.CommandTimeout = 300;
cmdUpdate.ExecuteNonQuery();

 

 

produces the exception: "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding." on the ExecuteNonQuery() line.

 

It's not a problem with the CommandTimeout or the ConnectionTimeout (from within the connection string), they are both set to 300 (and even when set to 0, the problem is the same).

 

It's very strange, because only two stored procedures don't work, the others work without any problems and under SQL Server (using EXEC stored_procedure) all of them work fine. It seems like the stored procedure is executed, but no response is returned to the client (the WinForm app), thus the timeout exception.

 

Any ideas? Thanks.

Answer 1

I ran into this same problem.  You need to set the "CommandTimeout" parameter before you open the connection.

Answer 2

I am already set command Timout before open the connection but still i'm faceing the same problem.

Answer 3

Are the procedures really effecting the records? They can timeout because of locks. What is the output in sql server profiler? Also, you can use BeginExecuteNonQuery to execute command asynchronously. 

---

MCTS, CodeProject MVP 2008

Answer 4

Procedure is effecting the records. they work fine. I'm using ExecuteNonQuery for that. I'm coping data from mdb file to sqlserver table. at there approx. over 60,00,000 recored's.

and one interesting think is that when i set the commandtimeout=0 after that sqlcommand run approx. 1.5 minutes. but after that this working in background after 3 minute if i check all data is copyied in sql table.

 

this is my code

SqlCommand

 

 

cmd = new SqlCommand("sp_CallProcedure", conn);

cmd.CommandType =

 

CommandType.StoredProcedure;

cmd.CommandTimeout = 0;

 

 

if (conn.State == ConnectionState.Open)

conn.Close();

conn.Open();

cmd.ExecuteNonQuery();

 

thnks fo response

Answer 5

ExecuteNonQuery required Open connection. Close it only when the command is finished. As the command needs much time you need to either increase timeout or use BeginExecuteNonQuery which will not time out.

---

MCTS, CodeProject MVP 2008

Answer 6

I'm already set commandtimeout=0 which is infinite time and BeginExecuteNonQuery which is not working.

Answer 7

hi

Can  u please tell which type of values you are storing in the Db when exception occurs

 

Thanks

Answer 8

hi,

please check the connection state and also r u pointing with the correct db schema..

if the connection state is already opens please reinvoke the connection it will work,,,