lmred
asked on
What does 'Internal Query Processor Error: The query processor ran out of stack space during query optimization.' mean?
I am using C# and connecting to a SQL 2000 database. I am building a dynamic query. I am adding to my where clause. I am using this in a loop to create this query and am making it a parameterized query. It worked fine here in my office. What is wrong? When I sent it to the customer they received this error:
Exception Searching Object :Internal Query Processor Error: The query processor ran out of stack space during query optimization.
Details:
System.Data.SqlClient.SqlE xception: Internal Query Processor Error: The query processor ran out of stack space during query optimization.
at System.Data.SqlClient.SqlC onnection. OnError(Sq lException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlI nternalCon nection.On Error(SqlE xception exception, Boolean breakConnection)
at System.Data.SqlClient.TdsP arser.Thro wException AndWarning (TdsParser StateObjec t stateObj)
at System.Data.SqlClient.TdsP arser.Run( RunBehavio r runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlD ataReader. ConsumeMet aData()
at System.Data.SqlClient.SqlD ataReader. get_MetaDa ta()
at System.Data.SqlClient.SqlC ommand.Fin ishExecute Reader(Sql DataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlC ommand.Run ExecuteRea derTds(Com mandBehavi or cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlC ommand.Run ExecuteRea der(Comman dBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlC ommand.Run ExecuteRea der(Comman dBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlC ommand.Exe cuteReader (CommandBe havior behavior, String method)
at System.Data.SqlClient.SqlC ommand.Exe cuteDbData Reader(Com mandBehavi or behavior)
at System.Data.Common.DbComma nd.System. Data.IDbCo mmand.Exec uteReader( CommandBeh avior behavior)
at System.Data.Common.DbDataA dapter.Fil lInternal( DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataA dapter.Fil l(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataA dapter.Fil l(DataSet dataSet)
at TADS.ODEObjectManager.ODEO bjectDBCla ss.GetMult ipleParame terSupport Records(Sq lConnectio n DBConn, ParamInfoDB paramInfo, String strSupportRecWhere)
at TADS.ODEObjectManager.ODEO bjectDBCla ss.GetPara mInfoByNam e(Guid ObjectID, String strSearch, ArrayList arrParameters, String[] roles)
at TADS.ODEObjectManager.ODED ataObjectR eader.GetS earchParam eterIDsTes t(String Expression)
at TADS.ODEObjectManager.ODED ataObjectR eader.Coar seSearch(S tring expression)
at TADS.ODEObjectManager.Sear chPlugin.S earchObjec
Here is the code:
public ParamInfoDB GetParamInfoByName(Guid ObjectID, string strSearch, ArrayList arrParameters,
string[] roles)
{
SqlCommand sqlCmd = new SqlCommand();
ParamInfoDB paramInfo = new ParamInfoDB();
string strSQLStatement = BuildParamInfoSelect(sqlCm d, arrParameters, strSearch, ObjectID);
strSQLStatement += BuildParameterRolesSelect( roles);
using (SqlConnection DBConn = DatabaseConnection(strDBNa me, strDBServer))
{
sqlCmd.Connection = DBConn;
sqlCmd.CommandText = strSQLStatement;
sqlCmd.CommandType = CommandType.Text;
SqlDataAdapter ParamAdapter = new SqlDataAdapter(sqlCmd);
ParamAdapter.TableMappings .Add(new DataTableMapping("Table", "ParamInfo"));
ParamAdapter.Fill(paramInf o);
if (paramInfo.ParamInfo.Count > 0)
{
string strSupportRecWhere = "where";
foreach (ParamInfoDB.ParamInfoRow rowParam in paramInfo.ParamInfo)
{
strSupportRecWhere += " paraminfoid = '" + rowParam.ID.ToString() + "' or ";
}
//***remove last or
strSupportRecWhere = strSupportRecWhere.Substri ng(0, strSupportRecWhere.LastInd exOf(" or "));
GetMultipleParameterSuppor tRecords(D BConn, paramInfo, strSupportRecWhere);
}
}
return paramInfo;
}
private string BuildParamInfoSelect(SqlCo mmand sqlCmd,ArrayList arrParameters,string strSearch,Guid ObjectID)
{
string strSQLStatement = "select * from ParamInfo where ODEPropertiesID = @ODEPropertiesID and " + strSearch + " = @Value ";
sqlCmd.Parameters.Add("@Va lue", SqlDbType.VarChar).Value = arrParameters[0].ToString( );
for (int intCount = 1; intCount < arrParameters.Count; intCount++)
{
strSQLStatement += " or " + strSearch + " = @Value" + intCount;
sqlCmd.Parameters.Add("@Va lue" + intCount, SqlDbType.VarChar).Value = arrParameters[intCount].To String();
}
sqlCmd.Parameters.Add("@OD EPropertie sID", SqlDbType.UniqueIdentifier ).Value = ObjectID;
return strSQLStatement;
}
private void GetMultipleParameterSuppor tRecords(S qlConnecti on DBConn, ParamInfoDB paramInfo,string strSupportRecWhere)
{
SqlDataAdapter attributeRecords =
new SqlDataAdapter(
"select * from ParameterAttributes " + strSupportRecWhere + " order by AttributeID", DBConn);
attributeRecords.TableMapp ings.Add(n ew DataTableMapping("Table", "ParameterAttributes"));
attributeRecords.SelectCom mand.Comma ndTimeout = 120;
SqlDataAdapter DerivedRecords =
new SqlDataAdapter(
"select * from DerivedParameterInfo " + strSupportRecWhere,
DBConn);
DerivedRecords.TableMappin gs.Add(new DataTableMapping("Table", "DerivedParameterInfo"));
DerivedRecords.SelectComma nd.Command Timeout = 120;
SqlDataAdapter DerivedReferenceRecords =
new SqlDataAdapter(
"select * from DerivedReferences where DerivedParameterInfoID in(select ID from DerivedParameterInfo " + strSupportRecWhere + ")",
DBConn);
DerivedReferenceRecords.Ta bleMapping s.Add(new DataTableMapping("Table", "DerivedReferences"));
DerivedReferenceRecords.Se lectComman d.CommandT imeout = 120;
attributeRecords.Fill(para mInfo);
DerivedRecords.Fill(paramI nfo);
DerivedReferenceRecords.Fi ll(paramIn fo);
}
Exception Searching Object :Internal Query Processor Error: The query processor ran out of stack space during query optimization.
Details:
System.Data.SqlClient.SqlE
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.SqlD
at System.Data.SqlClient.SqlD
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.Common.DbComma
at System.Data.Common.DbDataA
at System.Data.Common.DbDataA
at System.Data.Common.DbDataA
at TADS.ODEObjectManager.ODEO
at TADS.ODEObjectManager.ODEO
at TADS.ODEObjectManager.ODED
at TADS.ODEObjectManager.ODED
at TADS.ODEObjectManager.Sear
Here is the code:
public ParamInfoDB GetParamInfoByName(Guid ObjectID, string strSearch, ArrayList arrParameters,
string[] roles)
{
SqlCommand sqlCmd = new SqlCommand();
ParamInfoDB paramInfo = new ParamInfoDB();
string strSQLStatement = BuildParamInfoSelect(sqlCm
strSQLStatement += BuildParameterRolesSelect(
using (SqlConnection DBConn = DatabaseConnection(strDBNa
{
sqlCmd.Connection = DBConn;
sqlCmd.CommandText = strSQLStatement;
sqlCmd.CommandType = CommandType.Text;
SqlDataAdapter ParamAdapter = new SqlDataAdapter(sqlCmd);
ParamAdapter.TableMappings
ParamAdapter.Fill(paramInf
if (paramInfo.ParamInfo.Count
{
string strSupportRecWhere = "where";
foreach (ParamInfoDB.ParamInfoRow rowParam in paramInfo.ParamInfo)
{
strSupportRecWhere += " paraminfoid = '" + rowParam.ID.ToString() + "' or ";
}
//***remove last or
strSupportRecWhere = strSupportRecWhere.Substri
GetMultipleParameterSuppor
}
}
return paramInfo;
}
private string BuildParamInfoSelect(SqlCo
{
string strSQLStatement = "select * from ParamInfo where ODEPropertiesID = @ODEPropertiesID and " + strSearch + " = @Value ";
sqlCmd.Parameters.Add("@Va
for (int intCount = 1; intCount < arrParameters.Count; intCount++)
{
strSQLStatement += " or " + strSearch + " = @Value" + intCount;
sqlCmd.Parameters.Add("@Va
}
sqlCmd.Parameters.Add("@OD
return strSQLStatement;
}
private void GetMultipleParameterSuppor
{
SqlDataAdapter attributeRecords =
new SqlDataAdapter(
"select * from ParameterAttributes " + strSupportRecWhere + " order by AttributeID", DBConn);
attributeRecords.TableMapp
attributeRecords.SelectCom
SqlDataAdapter DerivedRecords =
new SqlDataAdapter(
"select * from DerivedParameterInfo " + strSupportRecWhere,
DBConn);
DerivedRecords.TableMappin
DerivedRecords.SelectComma
SqlDataAdapter DerivedReferenceRecords =
new SqlDataAdapter(
"select * from DerivedReferences where DerivedParameterInfoID in(select ID from DerivedParameterInfo " + strSupportRecWhere + ")",
DBConn);
DerivedReferenceRecords.Ta
DerivedReferenceRecords.Se
attributeRecords.Fill(para
DerivedRecords.Fill(paramI
DerivedReferenceRecords.Fi
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks you so much for your help.
ASKER
Lmred