G
Guest
I have this stored procedure on a SQL 2005 database that when invoked from
query analyzer or SQL Server Management studio runs in 2 seconds.
However, the following code times out on the executeScalar call:
DbCommand cmd = cnn.CreateCommand();
cmd.CommandText = "spc_search_items_count";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = UtilFuncs.DbScaledTimeout(45,
SysTimeoutScalars.Items);
//-- Create parameters --//
DBProvider.CreateParam(cmd, "vchSearchString",
DbType.AnsiString, srchString);
DBProvider.CreateParam(cmd, "intUserID", DbType.Int32,
nUserID);
DBProvider.CreateParam(cmd, "intBatchID", DbType.Int32,
nBatchID);
DBProvider.CreateParam(cmd, "intStatusID", DbType.Int32,
nStatusID);
DBProvider.CreateParam(cmd, "intCategoryID", DbType.Int32,
nCategID);
cnn.Open();
int nCount;
object obj = cmd.ExecuteScalar();
if(obj != null)
nCount = Convert.ToInt32(obj);
else
nCount = 0;
When monitoring SQL Server I see the process ID using just CPU and the
details say: TTT.dbo.spc_search_items_count;1
Does anybody have any advise of where the problem could lie? I have never
had this happen before; where invoking something through ADO.NET is any
different than invoking the stored procedure through other means.
query analyzer or SQL Server Management studio runs in 2 seconds.
However, the following code times out on the executeScalar call:
DbCommand cmd = cnn.CreateCommand();
cmd.CommandText = "spc_search_items_count";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = UtilFuncs.DbScaledTimeout(45,
SysTimeoutScalars.Items);
//-- Create parameters --//
DBProvider.CreateParam(cmd, "vchSearchString",
DbType.AnsiString, srchString);
DBProvider.CreateParam(cmd, "intUserID", DbType.Int32,
nUserID);
DBProvider.CreateParam(cmd, "intBatchID", DbType.Int32,
nBatchID);
DBProvider.CreateParam(cmd, "intStatusID", DbType.Int32,
nStatusID);
DBProvider.CreateParam(cmd, "intCategoryID", DbType.Int32,
nCategID);
cnn.Open();
int nCount;
object obj = cmd.ExecuteScalar();
if(obj != null)
nCount = Convert.ToInt32(obj);
else
nCount = 0;
When monitoring SQL Server I see the process ID using just CPU and the
details say: TTT.dbo.spc_search_items_count;1
Does anybody have any advise of where the problem could lie? I have never
had this happen before; where invoking something through ADO.NET is any
different than invoking the stored procedure through other means.