26 Oktober 2007 15:28
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 codeCode Block
SqlConnection sqlCon = GetConnection();
cmdUpdate = new SqlCommand("StoredProcedure_Name", sqlCon);
cmdUpdate.CommandType = CommandType.StoredProcedure;
cmdUpdate.CommandTimeout = 300;
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.
- Dipindahkan oleh Bob BeaucheminMVP 06 Desember 2010 5:08 Moved to a more relevent forum (From:.NET Framework inside SQL Server)
30 Nopember 2007 15:29
I ran into this same problem. You need to set the "CommandTimeout" parameter before you open the connection.
06 Desember 2010 4:40
I am already set command Timout before open the connection but still i'm faceing the same problem.
06 Desember 2010 9:24Are 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
06 Desember 2010 13:49
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
cmd = new SqlCommand("sp_CallProcedure", conn);
cmd.CommandTimeout = 0;
if (conn.State == ConnectionState.Open)
thnks fo response
06 Desember 2010 14:45ExecuteNonQuery 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
- Disarankan sebagai Jawaban oleh Papy Normand 28 April 2012 21:23
07 Desember 2010 4:16I'm already set commandtimeout=0 which is infinite time and BeginExecuteNonQuery which is not working.
23 Juni 2011 7:53
Can u please tell which type of values you are storing in the Db when exception occurs
24 April 2012 11:35
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,,,