.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Insert Column Limit from XML? The query processor ran out of stack space during query optimization e

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server

I get the following error:

Msg 8621, Level 17, State 2, Procedure ProcessFliteTracWorkingXML_FTPax, Line 348
The query processor ran out of stack space during query optimization. Please simplify the query.

I have looked at this KB fix, but we are a newer version then in that article:

Also found this post with a similiar issue, but no solution:

Before I call Microsoft Support I thought I would try here first.

I am trying to run a stored proc that basically needs to parse xml messages coming from an outside datasource.  We are using SQL Server 2005 SP3 (9.00.4053.00 SP3 Standard Edition).  This datasource is an old pervasived database with not the best data model.  So what we wanted to do was pull the raw data as is into a staging table and then convert it to a better data model in our ODS.  There are around 300 columns in this table and I have a generic process that pulls the rows out and converts to XML and saves to a working table (this

View Complete Post

More Related Resource Links

MS SQL 2000 The query processor ran out of stack space during query optimization.

On an insert statement with an image field I get the following error when the data to go into the image field is over a certain size. Lots of special characters. The query processor ran out of stack space during query optimization. Please help on how to fix.

Merge Replication - Error - Query processor ran out of stack space


Hello everyone,

I have a merge publication configured that has been running fine with no issues for many weeks.  Here is my setup... Publisher - SQL 2008 SP1, Distributor - SQL 2008 R2, Subscriber - SQL 2008 SP1.

On Wednesday and Thursday of last week, I created a few hundred foreign keys referencing a table (user information for auditing).  Then, on Friday evening, I started to receive this message:



Msg 8621, Level 17, State 1, Procedure MSmerge_sel_sp_18DB84E1A521483601D2A6D477F6483D, Line 85
The query processor ran out of stack space during query optimization. Please simplify the query.



I have traced it down, this command gets executed:


exec MSmerge_sel_sp_18DB84E1A521483601D2A6D477F6483D @maxschemaguidforarticle = 'F990B1EE-D52F-4DCA-8EEF-C1783DA47A0F', @type = 6


Which executes this:



SharePoint Dataview Insert New Mode - Defaulting a Required Column to a Query String Parameter Valu

I've got a SharePoint DataView with an insert button. there is a required lookup column I want to default to the value of Querystring Value during new mode.  Can I do this in the generated code below? I tried replacing @ApplicantId with @QSApplicantID.. and  tried string(@QSApplicantID) too.. no luck gave me an error: The data source control failed to execute the insert command. Could it be that QSApplicantID (which I've used in my DataView Datasource filter with no problem) is not available during the insert? I think I can do what I want in Javascript but was hoping I could instead right in the XSL markup. *my query string parameter**     ParameterBinding Name="QSApplicantID" Location="QueryString(ID)" DefaultValue="2222222"/ **the new form** SharePoint:FormField runat="server" id="ff4{$Pos}" ControlMode="New" FieldName="ApplicantId" __designer:bind="{ddwrt:DataBind('i',concat('ff4',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@ApplicantId')}" / **the link** a href="javascript: {ddwrt:GenFireServerEvent('__cancel;dvt_1_form_insertmode={1}')}">Insert</a Thanks for any help or information!

SQL 2008 SP2: adding a "persisted computed column" creates an "Internal Query Processor Error: The q

Hi, a simple (and long-existing) stored procedure started throwing this error after adding a persisted computed column on another table that is not used in the stored procedure (but is referenced by a foreign key in the table used in the stored proc.)
Msg 8624, Level 16, State 1, Procedure pSetPersonExtraAnswer, Line 26
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

I think I have traced the "culprit" to the following update.
ALTER TABLE dbo.Persons ADD fullName AS (firstName + ' ' + lastName) PERSISTED

Here is the very weird thing:

If I restore the DB, immediately add the persisted computed column and run the problematic SP call, I get the error in Management Studio.

If I restore the DB, run the problematic SP call and then add the computed column, I no longer get an error from SSMS when I rerun the SP call. But, I still get it from my ASP.NET (via enterprise library) code!

When the error happens, altering the pro

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

MDX Query optimization

Hello Experts, I have a MDX query which first selects those promotions and products valid for a customer and a particular period. Then those sets are used for data extraction from cube. WITH MEMBER   [MEASURES].[Measure_Value] as Sum   ( { [MEASURES].[MeasureA] ,[MEASURES].[MeasureB] ,[MEASURES].[MeasureC] } ) SET   [Products_Subset] as FILTER ( {[Product].[TU Code]. Children} , ([Country].[Code].&[XXX],[Time].[Calendar].[Month].&[7]&[2010] , [Customer].[Hierarchy].[CustomerLevel2].&[00005]&[00004] ,[MEASURES].[Measure_Value])<>0) SET   [Promotion_Subset] as FILTER ( {[Promotion].[Promotion ID]. Children} , ([Country].[Code].&[XXX],[Time].[Calendar].[Month].&[7]&[2010] , [Customer].[Hierarchy].[CustomerLevel2].&[00005]&[00004] ,[MEASURES].[Measure_Value])<>0) MEMBER   [Measures].[Level2 Customer Code] AS   [Customer].[Hierarchy].CurrentMember.Properties("Level2 Customer Code") SELECT   { [Measures].[Planning Customer Code] ,[MEASURES].[MeasureA] ,[MEASURES].[MeasureB] ,[MEASURES].[MeasureC] ,[Measures].[Comments] } ON COLUMNS , FILTER   (( { [Promotion_Subset]} * { [Products_Subset]} * {[Customer].[Hierarchy].[CustomerLevel2].&[00005]&[00004]} * {[Time].[Calendar].[Month].&[7]&[2010]}* {[Country].[Code].&[

Efficiency: new column in source query or derived column task?

Hi All, I've just started working on an SSIS package that pulls data from an OLE DB Source by a query. A new column needs to be added based on the value of a queried column. I was wondering if it's better to do that in the query or with a derived column? A simple example: I have a table that contains CustomerName and CustomerCode (this one can be V /valid/ or I /invalid/). I need to store the CustomerCodeDesc in a separate column in the destination table. Is it better to alter the query like this: SELECT CustomerName, CustomerCode, CASE WHEN CustomerCode = 'V' THEN 'Valid' ELSE 'Invalid' END AS CustomerCodeDesc FROM CustomerTable Or is it better to use a DerivedColumn task in the DataFlow? Or maybe it doesn't really matter...

insert query from ms SQL server into a mysql linked server

INSERT into openquery(dbserver1MySQL,'select * from graham.lookup_in_table') select * from NavteqAPAC.dbo.Admin_Names   is giving me the following error! Please help me   OLE DB provider "MSDASQL" for linked server "dbserver1MySQL" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.1.49-community]Commands out of sync; you can't run this command now". Msg 7343, Level 16, State 2, Line 1 The OLE DB provider "MSDASQL" for linked server "dbserver1MySQL" could not INSERT INTO table "[MSDASQL]".

SQL query optimization

I have a stored procedure that is taking a long time to run. I have to optimize it. While testing I found that there is a join that is taking a long time : select t1.col1, t1.col2, count(distinct a.personid) e1, count(distinct b.personid) e2 from table1 t1 ...... join messages m with(nolock) on m.messageid = t1.messageid join messagerecipients mr with(nolock) on m.messageid = mr.messageid  left join people a with(nolock) on mr.personid = emp.personid and emp.persontypeid = 2  left join people b with(nolock) on mr.personid = con.personid  and con.persontypeid = 1   The personid in messagerecipients does not have an index. So the join are taking a long time when I find the count in the select statement. I do not intent to modify the production table for now to apply indexes. Is there a way I can optimize my query   Thanks, Reshmi

Write select query with a paremetrized where clause, and populate database with the retrieved column

I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add

Pipe SQL Query function into Variable with no column headings

Hello, I don't know if I'm in the right forum but could not find one for Powershell. I need to return 1 piece of data from a sql database to a variable. I don't want the column headings. Here's what I have so far:         Function GetMenuGroup ([int]$StoreID) { $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=Servername;Database=DatabaseName;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "exec storedProcedure $StoreID" $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet)|out-null $SqlConnection.Close() $DataSet.Tables[0] } $MG=Getdata 2 |out-string Here's the results I get now - How do I return just "DatathatIreallyWant" in a string variable? I don't want the column or underline. ColumnName ------------- DataThatIreallyWant   Thank you, MG2

How to filter managed meta data column using page query string?

I have two managed term sets as follows: Articles:Downloads Projects:Downloads I am trying to use a content query web part to filter a list based on the above manged meta data column. I want to filter out only "Articles:Downloads" Scenario 1: When I edit the web part and directly set the filter option with the managed meta data column to "Articles:Downloads" it works fine. When I export the web part, in the .webpart file, the "FilterValue1" property has a value "Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910". Scenario 2: I want the web part to get the filter information from query string [PageQueryString:pageType]. So I tried using these options: a) pageType=Downloads      This works but returns downloads under 'projects' as well. (i want downloads only under 'articles')  b) pageType=Articles:Downloads     Does not work and shows no records c) pageType = Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work. (This value is the same as one found in 'FilterValue1' of the static web part). c) pageType = Articles:Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work. d) pageType = 9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work.   What am I missing here? Can some one please help?

insert query with subquery

Hello, I have 2 tables, one fact table (FeitDienstDagen) which contains employments, and a reference/dimension table, vDimMedewerker,  which contains a history of employees and their departments. IN the fact table I want to insert the correct WerkID from vDimMedewerker. So the employeeID (ms120_obj) must be same,  and the DienstDag_KEY (int date Key) must be greater then or equal to  the first werk_start_KEY (int date key) for that employee,  Ordered descendant. How to do that? Her my query, but it doesn't do the job :-) USE TEAMSOFT_TBSDB GO DELETE FROM dbo.FeitDienstdagen GO INSERT INTO [TEAMSOFT_TBSDB].[dbo].[FeitDienstdagen]            ([DienstverbandID]            ,[Deeltijdfactor]            ,[DienstDag_KEY]            ,[MutatieID]            ,[DienstverbandStart]            ,[DienstverbandEind]            ,[UrenWeek]            ,[Contracturen]            ,[UrenDag]     

How to copy column headers along with the query result in SS00

HiIs it possible to copy the column headers along with the query result from SQL Server 2000 Query Analyser.Thanks in advance.fjz

Error in SQL server 2000 after the migration (internal query processor error)

I just moved a couple of databases from one instance of SQL 2000 from different hardware to SQL 2000 instance of other hardware and after the move from the application side the app team gets an error -2147217900 - Internal Query Processor Error: The query processor could not produce a query plan.  Contact your primary support provider for more information.', 'E', '2UA929041X. Does any one have any idea about this. I took a trace while they were testing and I found out this in the trace. exec usp_DlyPrc_LogProcessEvent '-2147217900 - Internal Query Processor Error: The query processor could not produce a query plan.  Contact your primary support provider for more information.', 'E', '2UA929041X An idea or direction on this will be greatly appreciated. Thanks hash

How to enable delete column at Gridview when having specify query.

Hello:  I have trouble to add delete column to gridview when using specific query. I have PK set up at the table, I need add specific query to display data at gridview:   SELECT [PID], [GID], [TID], [SID], [NEWSID] FROM [TableOne] WHERE ([NAME] = ?)   After builder this query, I click advanced button. However, generate insert, update, and delete Statements is not enabled.   How to add delete column to this gridview?   Your help is highly appreciated.

know the hard disk's SQL Server space available from SQL Query



I would be able to determine how much hard disk space is available on the Server with a SQL Query, and from an other computer on the LAN.


How can I do something?


Thanks for your help.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend