Query Processor Error

I get the following message when I'm inserting a row into a table. The
statement is very simple, but there is a large amount of data going into one
column.
Internal Query Processor Error: The query processor ran out of stack space
during query optimization
All the KB articles I've found so far refer to queries that have a large
number of elements in an IN clause, or a CASE statement with a large number
of WHEN clauses - niether of which fit this scenario.
Has anyone else had this problem and found a way around it. I can't
replicate the problem on any of our development or test servers - it only
happens on the production server. All servers are the same spec - W2K
Server and SQL Server 2000 - all service packed up.
Any help appreciated.
Cheers,
CameronCameron,
Can you provide more information? What is the structure of the table,
and what is the insert statement, or at the least, what form does it have -
insert .. values, insert into .. select? Are there any triggers on the
table?
What indexes are on the table? Are any of the tables involved actually
views?
It's hard to suggest a way around a problem with this little information
about what you are trying to do.
-- Steve Kass
-- Drew University
-- Ref: 20CAE9D5-49D4-43CF-A330-EE3EB766EF1A
cj wrote:
>I get the following message when I'm inserting a row into a table. The
>statement is very simple, but there is a large amount of data going into one
>column.
>Internal Query Processor Error: The query processor ran out of stack space
>during query optimization
>All the KB articles I've found so far refer to queries that have a large
>number of elements in an IN clause, or a CASE statement with a large number
>of WHEN clauses - niether of which fit this scenario.
>Has anyone else had this problem and found a way around it. I can't
>replicate the problem on any of our development or test servers - it only
>happens on the production server. All servers are the same spec - W2K
>Server and SQL Server 2000 - all service packed up.
>Any help appreciated.
>Cheers,
>Cameron
>
>|||even though the statement may be simple, inserts could
have complex parsing for NULL conditions etc
the standard windows program defaults to 1MB stack size.
however, i believe this is reduced to 256K on sql server
for performance reasons.
in either Visual Studio C/C++ or the Windows Server EE
Customer Support Diagnostic, there are the utilities
editbin.exe and imagecfg.exe that lets you reconfigure the
stack size (sizes are in hex)
but i would try modifying your insert statement before
modifying the sql server binary, since this needs to be
redone every hotfix, sp etc
>--Original Message--
>I get the following message when I'm inserting a row into
a table. The
>statement is very simple, but there is a large amount of
data going into one
>column.
>Internal Query Processor Error: The query processor ran
out of stack space
>during query optimization
>All the KB articles I've found so far refer to queries
that have a large
>number of elements in an IN clause, or a CASE statement
with a large number
>of WHEN clauses - niether of which fit this scenario.
>Has anyone else had this problem and found a way around
it. I can't
>replicate the problem on any of our development or test
servers - it only
>happens on the production server. All servers are the
same spec - W2K
>Server and SQL Server 2000 - all service packed up.
>Any help appreciated.
>Cheers,
>Cameron
>
>.
>|||I would try DBCCs on the database in question, and if that didn't help, I
would call product support services and get them to help you out if you can
afford it. That sounds bad.
And don't cross post!
--
----
--
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
"cj" <smuffnstuff@.hotmail.com> wrote in message
news:O7Ptg8aoDHA.3612@.TK2MSFTNGP11.phx.gbl...
> I get the following message when I'm inserting a row into a table. The
> statement is very simple, but there is a large amount of data going into
one
> column.
> Internal Query Processor Error: The query processor ran out of stack space
> during query optimization
> All the KB articles I've found so far refer to queries that have a large
> number of elements in an IN clause, or a CASE statement with a large
number
> of WHEN clauses - niether of which fit this scenario.
> Has anyone else had this problem and found a way around it. I can't
> replicate the problem on any of our development or test servers - it only
> happens on the production server. All servers are the same spec - W2K
> Server and SQL Server 2000 - all service packed up.
> Any help appreciated.
> Cheers,
> Cameron
>|||What is this 1MB stack space ?
"joe chang" <anonymous@.discussions.microsoft.com> wrote in message
news:059e01c3a1bf$bba5da20$a401280a@.phx.gbl...
> even though the statement may be simple, inserts could
> have complex parsing for NULL conditions etc
> the standard windows program defaults to 1MB stack size.
> however, i believe this is reduced to 256K on sql server
> for performance reasons.
> in either Visual Studio C/C++ or the Windows Server EE
> Customer Support Diagnostic, there are the utilities
> editbin.exe and imagecfg.exe that lets you reconfigure the
> stack size (sizes are in hex)
> but i would try modifying your insert statement before
> modifying the sql server binary, since this needs to be
> redone every hotfix, sp etc
> >--Original Message--
> >I get the following message when I'm inserting a row into
> a table. The
> >statement is very simple, but there is a large amount of
> data going into one
> >column.
> >
> >Internal Query Processor Error: The query processor ran
> out of stack space
> >during query optimization
> >
> >All the KB articles I've found so far refer to queries
> that have a large
> >number of elements in an IN clause, or a CASE statement
> with a large number
> >of WHEN clauses - niether of which fit this scenario.
> >
> >Has anyone else had this problem and found a way around
> it. I can't
> >replicate the problem on any of our development or test
> servers - it only
> >happens on the production server. All servers are the
> same spec - W2K
> >Server and SQL Server 2000 - all service packed up.
> >
> >Any help appreciated.
> >
> >Cheers,
> >
> >Cameron
> >
> >
> >.
> >|||Have you got an insert trigger on the table? What are your settings for
nested and recursive triggers?
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"cj" <smuffnstuff@.hotmail.com> wrote in message
news:O7Ptg8aoDHA.3612@.TK2MSFTNGP11.phx.gbl...
> I get the following message when I'm inserting a row into a table. The
> statement is very simple, but there is a large amount of data going into
one
> column.
> Internal Query Processor Error: The query processor ran out of stack space
> during query optimization
> All the KB articles I've found so far refer to queries that have a large
> number of elements in an IN clause, or a CASE statement with a large
number
> of WHEN clauses - niether of which fit this scenario.
> Has anyone else had this problem and found a way around it. I can't
> replicate the problem on any of our development or test servers - it only
> happens on the production server. All servers are the same spec - W2K
> Server and SQL Server 2000 - all service packed up.
> Any help appreciated.
> Cheers,
> Cameron
>

Query performance

This is a multi-part message in MIME format.
--=_NextPart_000_0019_01C6AC39.4D136D70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello,
I wrote the query below, but I was wondering whether there was a better = way of doing so. I am basically trying to build a recursive process.
Any suggestion would be greatly appreciated.
Thanks.
Mike
*************************************************************************= ********************
*************************************************************************= ********************
CREATE PROCEDURE dbo.CustomersFiltered
(
@.filter varchar(50)
)
AS
create table #t
(
dummyId bigint IDENTITY(1,1) PRIMARY KEY,
customerId bigint,
parentId bigint,
levelId int
)
INSERT #t (customerId, parentId, levelId)
SELECT customerAutoId, parentCustomerAutoId, 0
FROM customers
WHERE [name] like @.filter
declare @.dummyId bigint
select @.dummyId =3D 1
declare @.customerId bigint
declare @.parentId bigint
declare @.levelId bigint
WHILE (SELECT COUNT(*) FROM #t WHERE dummyId =3D @.dummyId) > 0
BEGIN
select @.customerId =3D customerId,
@.parentId =3D parentId,
@.levelId =3D levelId
FROM #t WHERE dummyId =3D @.dummyId
insert into #t (customerId, parentId, levelId)
select CustomerAutoId,
parentCustomerAutoId,
@.levelId - 1
from customers
where parentCustomerAutoId =3D @.customerId
group by CustomerAutoId,
parentCustomerAutoId
if (@.parentId is not null)
insert into #t (customerId, parentId, levelId)
select c.CustomerAutoId,
c.parentCustomerAutoId,
@.levelId + 1
from customers c
where c.customerAutoId =3D @.parentId
and not exists (select * from #t where c.customerAutoId =3D = customerId)
group by CustomerAutoId,
parentCustomerAutoId
select @.dummyId =3D @.dummyId + 1
END
select t.levelId as LevelId,
t.customerId as CustomerAutoId,
c.[name] as [Name],
c.sic as SIC,
c.ParentCustomerAutoId as ParentCustomerAutoId
from #t t
inner join customers c on c.customerAutoId =3D t.customerId
group by t.levelId,
t.customerId,
c.[name],
c.sic,
c.ParentCustomerAutoId
order by levelId desc,
c.ParentCustomerAutoId,
c.[name]
drop table #t
GO
--=_NextPart_000_0019_01C6AC39.4D136D70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Hello,

I wrote the query below, but I was = wondering whether there was a better way of doing so. I am basically trying to = build a recursive process.

Any suggestion would be greatly appreciated.

Thanks.
Mike


****************************************************************= *****************************
****************************************************************= *****************************
CREATE PROCEDURE dbo.CustomersFiltered( @.filter varchar(50))AS

create table #t( dummyId bigint IDENTITY(1,1) = PRIMARY KEY, customerId = bigint, parentId bigint, levelId int)

INSERT #t (customerId, parentId, levelId)SELECT = customerAutoId, parentCustomerAutoId, 0FROM customersWHERE [name] like = @.filter


declare @.dummyId bigintselect @.dummyId =3D 1

declare @.customerId bigintdeclare @.parentId bigintdeclare @.levelId bigint

WHILE (SELECT COUNT(*) FROM #t WHERE dummyId =3D @.dummyId) > 0BEGIN

select @.customerId =3D customerId, @.parentId =3D parentId, @.levelId =3D levelId FROM #t WHERE dummyId =3D @.dummyId

insert into #t (customerId, parentId, = levelId) select = CustomerAutoId, = parentCustomerAutoId, = @.levelId - 1 from customers where parentCustomerAutoId =3D @.customerId group by CustomerAutoId, = parentCustomerAutoId

if (@.parentId is not null) = insert into #t (customerId, parentId, levelId) = select c.CustomerAutoId, &nbs= p; c.parentCustomerAutoId, &nbs= p; @.levelId + 1 from customers c where c.customerAutoId =3D @.parentId and not exists (select = * from #t where c.customerAutoId =3D customerId) = group by CustomerAutoId, = parentCustomerAutoId

select @.dummyId =3D @.dummyId + 1

END

select t.levelId &nbs= p; as LevelId, t.customerId &= nbsp; as CustomerAutoId, c.[name]  = ; as [Name], c.sic &n= bsp; as SIC, c.ParentCustomerAutoId as ParentCustomerAutoIdfrom #t t inner join customers c on c.customerAutoId =3D = t.customerIdgroup by t.levelId, t.customerId, c.[name], c.sic, c.ParentCustomerAutoIdorder by levelId desc, c.ParentCustomerAutoId, &nbs= p; c.[name]

drop table #tGO


--=_NextPart_000_0019_01C6AC39.4D136D70--If you're using SQL Server 2005, you should consider using Common Table
Expressions (CTE) instead. Temporary tables suffer from writing data to
the tempdb database, whereas CTEs are in memory only.
A good walktrough is available at the MSDN site:
http://msdn2.microsoft.com/en-us/library/ms186243.aspx
Hope this helps
"Mike" <none@.none.com> wrote in
news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:
> I wrote the query below, but I was wondering whether there was a
> better way of doing so. I am basically trying to build a recursive
> process.
> Any suggestion would be greatly appreciated.
> Thanks.
> Mike
>
> **********************************************************************
*
> **********************
> **********************************************************************
*
> **********************
> CREATE PROCEDURE dbo.CustomersFiltered
> (
> @.filter varchar(50)
> )
> AS
>
> create table #t
> (
> dummyId bigint IDENTITY(1,1) PRIMARY KEY,
> customerId bigint,
> parentId bigint,
> levelId int
> )
>
> INSERT #t (customerId, parentId, levelId)
> SELECT customerAutoId, parentCustomerAutoId, 0
> FROM customers
> WHERE [name] like @.filter
>
> declare @.dummyId bigint
> select @.dummyId = 1
>
> declare @.customerId bigint
> declare @.parentId bigint
> declare @.levelId bigint
> WHILE (SELECT COUNT(*) FROM #t WHERE dummyId = @.dummyId) > 0
> BEGIN
> select @.customerId = customerId,
> @.parentId = parentId,
> @.levelId = levelId
> FROM #t
> WHERE dummyId = @.dummyId
> insert into #t (customerId, parentId, levelId)
> select CustomerAutoId,
> parentCustomerAutoId,
> @.levelId - 1
> from customers
> where parentCustomerAutoId = @.customerId
> group by CustomerAutoId,
> parentCustomerAutoId
> if (@.parentId is not null)
> insert into #t (customerId, parentId, levelId)
> select c.CustomerAutoId,
> c.parentCustomerAutoId,
> @.levelId + 1
> from customers c
> where c.customerAutoId = @.parentId
> and not exists (select * from #t where c.customerAutoId => customerId)
> group by CustomerAutoId,
> parentCustomerAutoId
>
> select @.dummyId = @.dummyId + 1
> END
>
> select t.levelId as LevelId,
> t.customerId as CustomerAutoId,
> c.[name] as [Name],
> c.sic as SIC,
> c.ParentCustomerAutoId as ParentCustomerAutoId
> from #t t
> inner join customers c on c.customerAutoId = t.customerId
> group by t.levelId,
> t.customerId,
> c.[name],
> c.sic,
> c.ParentCustomerAutoId
> order by levelId desc,
> c.ParentCustomerAutoId,
> c.[name]
>
> drop table #t
> GO
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||I am using SQL Server 2000 SP4.
Is there anything that I could improve in my query? I checked all indexes,
even in the temp table, but I ran out of ideas.
Thanks.
Mike
"Ole Kristian Bangås" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns98075D16535A0olekristianbangaas@.207.46.248.16...
> If you're using SQL Server 2005, you should consider using Common Table
> Expressions (CTE) instead. Temporary tables suffer from writing data to
> the tempdb database, whereas CTEs are in memory only.
> A good walktrough is available at the MSDN site:
> http://msdn2.microsoft.com/en-us/library/ms186243.aspx
> Hope this helps
>
> "Mike" <none@.none.com> wrote in
> news:uMSJXrFrGHA.4112@.TK2MSFTNGP02.phx.gbl:
>> I wrote the query below, but I was wondering whether there was a
>> better way of doing so. I am basically trying to build a recursive
>> process.
>> Any suggestion would be greatly appreciated.
>> Thanks.
>> Mike
>>
>> **********************************************************************
> *
>> **********************
>> **********************************************************************
> *
>> **********************
>> CREATE PROCEDURE dbo.CustomersFiltered
>> (
>> @.filter varchar(50)
>> )
>> AS
>>
>> create table #t
>> (
>> dummyId bigint IDENTITY(1,1) PRIMARY KEY,
>> customerId bigint,
>> parentId bigint,
>> levelId int
>> )
>>
>> INSERT #t (customerId, parentId, levelId)
>> SELECT customerAutoId, parentCustomerAutoId, 0
>> FROM customers
>> WHERE [name] like @.filter
>>
>> declare @.dummyId bigint
>> select @.dummyId = 1
>>
>> declare @.customerId bigint
>> declare @.parentId bigint
>> declare @.levelId bigint
>> WHILE (SELECT COUNT(*) FROM #t WHERE dummyId = @.dummyId) > 0
>> BEGIN
>> select @.customerId = customerId,
>> @.parentId = parentId,
>> @.levelId = levelId
>> FROM #t
>> WHERE dummyId = @.dummyId
>> insert into #t (customerId, parentId, levelId)
>> select CustomerAutoId,
>> parentCustomerAutoId,
>> @.levelId - 1
>> from customers
>> where parentCustomerAutoId = @.customerId
>> group by CustomerAutoId,
>> parentCustomerAutoId
>> if (@.parentId is not null)
>> insert into #t (customerId, parentId, levelId)
>> select c.CustomerAutoId,
>> c.parentCustomerAutoId,
>> @.levelId + 1
>> from customers c
>> where c.customerAutoId = @.parentId
>> and not exists (select * from #t where c.customerAutoId =>> customerId)
>> group by CustomerAutoId,
>> parentCustomerAutoId
>>
>> select @.dummyId = @.dummyId + 1
>> END
>>
>> select t.levelId as LevelId,
>> t.customerId as CustomerAutoId,
>> c.[name] as [Name],
>> c.sic as SIC,
>> c.ParentCustomerAutoId as ParentCustomerAutoId
>> from #t t
>> inner join customers c on c.customerAutoId = t.customerId
>> group by t.levelId,
>> t.customerId,
>> c.[name],
>> c.sic,
>> c.ParentCustomerAutoId
>> order by levelId desc,
>> c.ParentCustomerAutoId,
>> c.[name]
>>
>> drop table #t
>> GO
> --
> Ole Kristian Bangås
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

Query out an xml element from a field containing xml data

'message' Field Data:
<xml>
...
<test>ABC</test>
...
</xml
How do I query out 'ABC' from this xml field data in a SQL Server?

Assume message field is in table 'Persons'What you could do, is write a user defined function. Something like

create function [dbo].[fn_GetText]
(
@.Text as XML
)
returns varchar(50)
begin
declare @.Result as varchar(50)
declare @.Count as int
declare @.Start as int

set @.Count = 1
set @.Start = 0

while @.Count <= len(@.Text)
begin
if substring(@.Text, @.Count, 6) = '<test>'
set @.Start = @.Count + 6

if substring(@.Text, @.Count, 7) = '<\test>' and @.Start <> 0
set @.Result = substring(@.Text, @.Start, @.Count - @.Start - 1)

set @.Count = @.Count + 1
end

return @.Result
end

go|||Hi Mike,

thanks for the reply.

As my comfort level is much higher with C#, I just wrote some string
parsing code and got my result :)

Next time I need to spin off a UDF, I will remember this.

Ranjith|||Ranjith (ranjithvenkatesh@.hotmail.com) writes:
> 'message' Field Data:
><xml>
> ...
> <test>ABC</test>
> ...
></xml>
> How do I query out 'ABC' from this xml field data in a SQL Server?
> Assume message field is in table 'Persons'

create table Persons (message xml NOT NULL)
go
insert Persons (message) VALUES('<xml><test>ABC</test></xml>')
go
select message.value(N'(/xml/test)[1]', 'varchar(10)')
from Persons
go
Drop table Persons

This is for SQL 2005. You did not mention which server of SQL Server
you are using. This matters a lot when XML is involved, as the XML
support in SQL 2005 is greatly extended over what is in SQL 2000,
including a new query language, XQuery which I'm using above.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx