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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

grayeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
grayeCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.