3

I have stored procedure which takes around 10 seconds to run when called from SSMS and executes successfully. The procedure takes an int as a parameter.

When calling the same stored procedure from code:

using (var connection = new SqlConnection(ConnectionStringName))
{
    using (var cmd = new SqlCommand("ProcedureName", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@itemId", itemId));
        cmd.CommandTimeout = 150;

        connection.Open();
        cmd.ExecuteNonQuery(); 
    }
}

The error I get is the following:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  
The timeout period elapsed prior to completion of the operation or the server is not responding. ---> 
System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

The passed parameter is valid and when calling the stored procedure from SSMS with the same parameter value it executes correctly.

shA.t
  • 16,580
  • 5
  • 54
  • 111
Tamas Ionut
  • 4,240
  • 5
  • 36
  • 59
  • How long does it take when you execute from SSMS? – rikitikitik Jun 10 '15 at 07:41
  • @shA.t Removed that line and I get the same exception, only faster. – Tamas Ionut Jun 10 '15 at 11:41
  • 1
    By using `cmd.CommandTimeout = 0;` you will never see that error, But your problem is inside of your stored procedure that needs some edits ;). – shA.t Jun 10 '15 at 11:44
  • Indeed, setting cmd.CommandTimeout = 0 solves the problem and I would mark it as an answer. However, the execution time of calling the SP takes about 30 seconds, much more than calling it from SSMS. Any tips on that one? – Tamas Ionut Jun 10 '15 at 11:59
  • 1
    I suggest you to ask another question and add code of your stored procedure to optimize it (or check [this](http://stackoverflow.com/q/3070653/4519059)) ;). – shA.t Jun 10 '15 at 12:12

2 Answers2

2

Maybe you forgot to specify the direction of the parameter, as you can provide input and output parameters. Try if this works:

SqlParameter param = new SqlParameter("@itemId", itemId);
param.Direction = ParameterDirection.Input;
cmd.Parameters.Add(param);
LInsoDeTeh
  • 150
  • 2
2

To avoid that error just use:

cmd.CommandTimeout = 0;

Note :
Your query execution will takes infinitive time.

shA.t
  • 16,580
  • 5
  • 54
  • 111