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);
        }


LVL 2
lmredAsked:
Who is Participating?
 
grayeConnect With a Mentor Commented:
This error isn't directly related to your program...  instead it's a message from the SQL Server itself, tell you that SQL Server is running out of stack space.

SQL Server has some very good database "Tuning Wizards" that you can use to analyise a days worth of logs and give you  some recommendations on how to "tune" SQL Server.   That might help.... or it might just be that you need more memory/disk/whatever on the server.

I'd be looking at the event logs on the server where SQL Server is hosted...  It might shed some light.
0
 
lmredAuthor Commented:
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
0
 
grayeConnect With a Mentor Commented:
Yes, any "nested query" would require a lot of stack (and probably wouldn't be the most efficient way to accomplish the task).   An quick/easy solution is to rewrite the query to use an SQL join operation (instead of a nested query).  It'd look something like this:

Select * from DerivedReferences where <something goes here> join DerivedParameterInfo on DerivedReferences.DerivedParameterInfoID = DerivedParameterInfo.ID

BTW:  SQL Server has some wonderful tools to analyze a query and show the "cost" via the proposed execution plan.   I encourage you to try both techniques and compare the "cost" via the Query Analyizer.

0
 
lmredAuthor Commented:
Thanks you so much for your help.
0
All Courses

From novice to tech pro — start learning today.