Stored temporary procedures when run from code, but not from the query tool

Question

I am trying to find out why a stored procedure call takes several seconds in the SQL Server express query window, but when I start the stored procedure call in code, the TIMES OUT request. We are using SQL Server 2008. I know that it’s hard to say exactly what happens without seeing the stored procedure. I just hope this is a known issue. Any recommendations are greatly appreciated.

An SQL query that calls "STORED_PROCEDURE_X" and runs after 2 seconds in the SQL Server Express Query window:

EXEC STORED_PROCEDURE_X '07/01/2010', '07/31/2010', 0, '', 'true','', 'Top 20'

Code that calls "STORED_PROCEDURE_X" and TIMES OUT:

SqlConnection connSQL = null;
SqlCommand sqlCmd = null;
SqlDataAdapter sqlDataAdpater = null;
DataTable returnData = null;

try
{
    returnData = new DataTable();
    connSQL = new SqlConnection(sqlConnection);
    sqlCmd = new SqlCommand("STORED_PROC_X", connSQL);
    if (connSQL.State == ConnectionState.Closed)
    {
        connSQL.Open();
    }
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd.CommandTimeout = 600;
    sqlCmd.Parameters.Add("@StartDate", SqlDbType.NVarChar).Value = "07/01/2010";
    sqlCmd.Parameters.Add("@EndDate", SqlDbType.NVarChar).Value = "07/31/2010";

    sqlCmd.Parameters.Add("@AuditType", SqlDbType.Int).Value = "0";

    sqlCmd.Parameters.Add("@SortBy", SqlDbType.NVarChar).Value = "";

    sqlCmd.Parameters.Add("@IsClaimDepartment", SqlDbType.NVarChar).Value = "true";
    sqlCmd.Parameters.Add("@IdsList", SqlDbType.NVarChar).Value = "";
    sqlCmd.Parameters.Add("@ReportType", SqlDbType.NVarChar).Value = "Top 20";
    sqlDataAdpater = new SqlDataAdapter(sqlCmd);
    sqlDataAdpater.Fill(returnData);
    if (connSQL.State == ConnectionState.Open)
    {
        connSQL.Close();
    }
    return returnData;
}
catch (Exception ex)
{
    LogErrorMessages("ExecuteStoredProcedure", ex.Message);
    throw ex;
}

Ruled out:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Answer 1

, , 30 , 00:00 , .net 40 , , 30 TimeOut .

ALTER PROCEDURE..., , . , , ,

Answer 2

, , SSMS, SSMS , , , , , .

SQL Profiler ( Express Edition), ? , , .

Answer 3

" " .

http://technet.microsoft.com/en-us/library/ms190439.aspx

Answer 4

, , . : sniffing ( ), .. varchar 22 , nvarchar.

, nvarchar varchar nvarchar. , 50 . 22 .

, nvarchar varchar, wow; 1 .

, SQL Server, . sql vb.net # 1 ; , , horrendus?

: nvarchar.

, , -. , ; .

, . 80 . , LinkToMeet.com, 150 , .

Answer 5

a) b) :

DataTable returnData = null;

try
{
    using(SqlConnection connSQL = new SqlConnection(sqlConnection))
    using(SqlCommand sqlCmd = new SqlCommand("STORED_PROC_X", connSQL))
    {
       sqlCmd.CommandType = CommandType.StoredProcedure;
       sqlCmd.CommandTimeout = 1200;

       // those two parameters should really be SqlDbType.DateTime!!
       sqlCmd.Parameters.Add("@StartDate", SqlDbType.NVarChar, 25).Value = "07/01/2010";
       sqlCmd.Parameters.Add("@EndDate", SqlDbType.NVarChar, 25).Value = "07/31/2010";

       sqlCmd.Parameters.Add("@AuditType", SqlDbType.Int).Value = "0";

       sqlCmd.Parameters.Add("@SortBy", SqlDbType.NVarChar, 50).Value = "";

       // this parameter should really be SqlDbType.Bit !!
       sqlCmd.Parameters.Add("@IsClaimDepartment", SqlDbType.NVarChar, 50).Value = "true";

       sqlCmd.Parameters.Add("@IdsList", SqlDbType.NVarChar, 25).Value = "";
       sqlCmd.Parameters.Add("@ReportType", SqlDbType.NVarChar, 25).Value = "Top 20";

       SqlDataAdapter sqlDataAdpater = new SqlDataAdapter(sqlCmd);

       returnData = new DataTable();
       sqlDataAdpater.Fill(returnData);
   }

   return returnData;
}
catch (Exception ex)
{
   LogErrorMessages("ExecuteStoredProcedure", ex.Message);
   throw;
}

SqlDataAdapter - .

,

• NVARCHAR ( )
• DATETIME! ( NVARCHAR)
• boolean BIT! ( NVARCHAR)
• , throw, throw ex ( throw ex, , )

Answer 6

, , SP, [thi ]

Answer 7

- ? .

Answer 8

I had the same problem. My saved proc is executed from MSSMS or dbForgeStudio, but not from C # code (SqlCommand). I fixed this problem by changing the stored procedure on the SQL server (without any changes).

Answer 9

change CommandTimeout = 0

sqlCmd.CommandTimeout = 0;