Link to home
Start Free TrialLog in
Avatar of lmred
lmredFlag for United States of America

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.SqlException: Internal Query Processor Error: The query processor ran out of stack space during query optimization.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at TADS.ODEObjectManager.ODEObjectDBClass.GetMultipleParameterSupportRecords(SqlConnection DBConn, ParamInfoDB paramInfo, String strSupportRecWhere)
   at TADS.ODEObjectManager.ODEObjectDBClass.GetParamInfoByName(Guid ObjectID, String strSearch, ArrayList arrParameters, String[] roles)
   at TADS.ODEObjectManager.ODEDataObjectReader.GetSearchParameterIDsTest(String Expression)
   at TADS.ODEObjectManager.ODEDataObjectReader.CoarseSearch(String expression)
   at TADS.ODEObjectManager.SearchPlugin.SearchObjec

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(sqlCmd, arrParameters, strSearch, ObjectID);
            strSQLStatement += BuildParameterRolesSelect(roles);
           
            using (SqlConnection DBConn = DatabaseConnection(strDBName, 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(paramInfo);
               
                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.Substring(0, strSupportRecWhere.LastIndexOf(" or "));
                   
                    GetMultipleParameterSupportRecords(DBConn, paramInfo, strSupportRecWhere);
                   
                }
            }
            return paramInfo;
        }

 private string BuildParamInfoSelect(SqlCommand sqlCmd,ArrayList arrParameters,string strSearch,Guid ObjectID)
        {
            string strSQLStatement = "select * from ParamInfo where ODEPropertiesID = @ODEPropertiesID and " + strSearch + " = @Value ";
            sqlCmd.Parameters.Add("@Value", SqlDbType.VarChar).Value = arrParameters[0].ToString();

            for (int intCount = 1; intCount < arrParameters.Count; intCount++)
            {
                strSQLStatement += " or " + strSearch + " = @Value" + intCount;
                sqlCmd.Parameters.Add("@Value" + intCount, SqlDbType.VarChar).Value = arrParameters[intCount].ToString();

            }
            sqlCmd.Parameters.Add("@ODEPropertiesID", SqlDbType.UniqueIdentifier).Value = ObjectID;
            return strSQLStatement;
        }

private void GetMultipleParameterSupportRecords(SqlConnection DBConn, ParamInfoDB paramInfo,string strSupportRecWhere)
        {
            SqlDataAdapter attributeRecords =
                new SqlDataAdapter(
                    "select * from ParameterAttributes " + strSupportRecWhere + " order by AttributeID", DBConn);
            attributeRecords.TableMappings.Add(new DataTableMapping("Table", "ParameterAttributes"));
            attributeRecords.SelectCommand.CommandTimeout = 120;

            SqlDataAdapter DerivedRecords =
                new SqlDataAdapter(
                    "select * from DerivedParameterInfo " + strSupportRecWhere,
                    DBConn);
            DerivedRecords.TableMappings.Add(new DataTableMapping("Table", "DerivedParameterInfo"));
            DerivedRecords.SelectCommand.CommandTimeout = 120;

            SqlDataAdapter DerivedReferenceRecords =
                new SqlDataAdapter(
                    "select * from DerivedReferences where DerivedParameterInfoID in(select ID from DerivedParameterInfo " + strSupportRecWhere + ")",
                    DBConn);
            DerivedReferenceRecords.TableMappings.Add(new DataTableMapping("Table", "DerivedReferences"));
            DerivedReferenceRecords.SelectCommand.CommandTimeout = 120;
            attributeRecords.Fill(paramInfo);
            DerivedRecords.Fill(paramInfo);
            DerivedReferenceRecords.Fill(paramInfo);
        }


ASKER CERTIFIED SOLUTION
Avatar of graye
graye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lmred

ASKER

Is there a way to change my code so they won't run out of stack space on their server? Is the query statement that uses the 'in' clause causing the problem? The whole purpose for writing this code is to speed up their search. So whatever I do, it needs to be fast.

Lmred
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lmred

ASKER

Thanks you so much for your help.