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

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

    • Moved by Bob Beauchemin Monday, December 6, 2010 5:08 AM Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    Friday, October 26, 2007 3:28 PM

All replies

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

    Friday, November 30, 2007 3:29 PM
  • I am already set command Timout before open the connection but still i'm faceing the same problem.

    Monday, December 6, 2010 4:40 AM
  • 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
    Monday, December 6, 2010 9:24 AM
  • 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

    Monday, December 6, 2010 1:49 PM
  • 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
    • Proposed as answer by Papy Normand Saturday, April 28, 2012 9:23 PM
    Monday, December 6, 2010 2:45 PM
  • I'm already set commandtimeout=0 which is infinite time and BeginExecuteNonQuery which is not working.
    Tuesday, December 7, 2010 4:16 AM
  • hi

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

     

    Thanks

    Thursday, June 23, 2011 7:53 AM
  • 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,,,

    Tuesday, April 24, 2012 11:35 AM
  • You need to use both BeginExecuteNonQuery and EndExecuteNonQuery like:

    IAsyncResult result = command.BeginExecuteNonQuery();
    while(!result.IsCompleted)
    {
    System.Threading.Thread.Sleep(100);
    }
    command.EndExecuteNonQuery(result);

    Source: http://msdn.microsoft.com/en-us/library/ca56w9se(v=vs.110).aspx

    Thanks,

    Suraj.

    • Proposed as answer by surajguru Wednesday, January 14, 2015 4:15 PM
    Wednesday, January 14, 2015 4:11 PM
  • thank' dud it's work :D
    Monday, August 31, 2015 2:04 AM