Showing posts with label amount. Show all posts
Showing posts with label amount. Show all posts

Friday, March 30, 2012

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
>

Wednesday, March 28, 2012

Query problem

Need answer pls to this problem:
Select charge_id,name,amount from tblcharge t inner join
tblpayments p on p.id = t.id where p.charge_id not in
(select t.charge_id from from tblcharge t inner join
tblpayments p on p.id = t.id where t.amount - p.amount = 0)
This works if data is:
charge_id name amount
1 Johnson -50.00 (from payment table)
1 Johnson 50.00 (from charge table)
but not if
charge_id name amount
1 Johnson -38.60 (from payment table)
1 Johnson -11.40 (from payment table)
1 Johnson 50.00 (from charge table)
Essentially not working if two or more payments were
received.
Pls. help ASAP. Thanks.Select charge_id,name,amount from tblcharge t inner join
tblpayments p on p.id = t.id where p.charge_id not in
(select t.charge_id from from tblcharge t inner join
tblpayments p on p.id = t.id where t.amount - sum(p.amount) = 0
group by t.charge_id )
see if this helps you, don't have the complete DDL so don't know if it is
100% correct
"Merwin12" <anonymous@.discussions.microsoft.com> wrote in message
news:152b01c530b0$8cba8ae0$a601280a@.phx.gbl...
> Need answer pls to this problem:
> Select charge_id,name,amount from tblcharge t inner join
> tblpayments p on p.id = t.id where p.charge_id not in
> (select t.charge_id from from tblcharge t inner join
> tblpayments p on p.id = t.id where t.amount - p.amount = 0)
> This works if data is:
> charge_id name amount
> 1 Johnson -50.00 (from payment table)
> 1 Johnson 50.00 (from charge table)
> but not if
> charge_id name amount
> 1 Johnson -38.60 (from payment table)
> 1 Johnson -11.40 (from payment table)
> 1 Johnson 50.00 (from charge table)
> Essentially not working if two or more payments were
> received.
> Pls. help ASAP. Thanks.|||Try,
Select
t.charge_id,
max(t.[name]),
t.amount
from
tblcharge t
left join
tblpayments p
on p.id = t.id
group by
t.charge_id
having
sum(t.amount) != isnull(sum(p.amount), 0)
go
AMB
"Merwin12" wrote:

> Need answer pls to this problem:
> Select charge_id,name,amount from tblcharge t inner join
> tblpayments p on p.id = t.id where p.charge_id not in
> (select t.charge_id from from tblcharge t inner join
> tblpayments p on p.id = t.id where t.amount - p.amount = 0)
> This works if data is:
> charge_id name amount
> 1 Johnson -50.00 (from payment table)
> 1 Johnson 50.00 (from charge table)
> but not if
> charge_id name amount
> 1 Johnson -38.60 (from payment table)
> 1 Johnson -11.40 (from payment table)
> 1 Johnson 50.00 (from charge table)
> Essentially not working if two or more payments were
> received.
> Pls. help ASAP. Thanks.
>|||Correction,
Select
t.charge_id,
max(t.[name]),
max(t.amount)
from
tblcharge t
left join
tblpayments p
on p.id = t.id
group by
t.charge_id
having
sum(t.amount) != isnull(sum(p.amount), 0)
go
AMB
"Alejandro Mesa" wrote:
> Try,
> Select
> t.charge_id,
> max(t.[name]),
> t.amount
> from
> tblcharge t
> left join
> tblpayments p
> on p.id = t.id
> group by
> t.charge_id
> having
> sum(t.amount) != isnull(sum(p.amount), 0)
> go
>
> AMB
> "Merwin12" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. And stop using that sillly redundant "tbl-" -- this is
SQL, not 1960's BASIC; read ISO-11179 for the rules on data element
names.
You also neve said what you wanted to do, but only posted code that was
not working right. I will further guess that you want the people who
have paid off their charges:
SELECT X.charge_id, X.name
FROM ( SELECT charge_id, name, amt
FROM Charges
UNION ALL
SELECT charge_id, name, amt
FROM Payments) AS X(charge_id, name, amt)
GROUP BY X.charge_id, X.name
HAVING SUM(amt) = 0.00;

Tuesday, March 20, 2012

Query Parameter problem

hi ,
I have created 2 datasets. my first dataset quary parameter is BU. query is like following.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtoset(@.BU)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

my second dataset query is like following,

SET [FilteredBUList] AS strtoset(@.BU)

SET [customtimeset] AS.....

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
intersect( [FilteredBUList],...) on rows

FROM Profitability

i need to map previous query BU parameter to the second dataset parameter. pls tel m the proper way.
cant access report parameter in this way from different dataset.
WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtotuple(@.BU)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

Wednesday, March 7, 2012

Query of counts

I have a table with three fields: AcctNo INT, Code CHAR(1), Amount MONEY

The Code has three values: 'A', 'B', or 'C'.

Each AcctNo has 1, 2, or all 3 of the Codes assigned to it (ie. AcctNo is not unique)

I need to know how many AcctNos have one value assigned to the Code field, how many have two, and how many have three and I need to know the sum of the Amount for each group.

Can this be done in one statement or do I need three statements?

Fred

The query below returns the results as requested. Is this what you're looking for?

Chris

DECLARE @.Values TABLE (AcctNo INT, Code CHAR(1), Amount MONEY)

INSERT INTO @.Values(AcctNo, Code, Amount)

SELECT 1, 'A', 1.00 UNION

SELECT 1, 'B', 2.50 UNION

SELECT 2, 'C', 1.25 UNION

SELECT 3, 'C', 1.43 UNION

SELECT 3, 'A', 1.96 UNION

SELECT 3, 'B', 2.00 UNION

SELECT 4, 'C', 1.43 UNION

SELECT 4, 'A', 1.96 UNION

SELECT 4, 'B', 2.10 UNION

SELECT 5, 'B', 0.92 UNION

SELECT 5, 'A', 1.24 UNION

SELECT 6, 'C', 0.02 UNION

SELECT 7, 'B', 0.11

SELECT SUM([AccountNoCount]), SUM(TotalAmount), [CodeCount]

FROM (SELECT COUNT(DISTINCT AcctNo) AS [AccountNoCount], SUM(Amount) AS TotalAmount, COUNT(Code) AS [CodeCount]

FROM @.Values

GROUP BY AcctNo) t

GROUP BY [CodeCount]

|||

Yes, that is what I wanted first.

But I need to add another level of complexity. There are duplicates of the AccountNo and Code, only the Amount is different. I need to count duplicates as one. (or anything more than one; there were some with three times and four)

Thanks,

Fred

|||

All you should need is an extra DISTINCT, see below.

Chris

SELECT SUM([AccountNoCount]), SUM(TotalAmount), [CodeCount]

FROM (SELECT COUNT(DISTINCT AcctNo) AS [AccountNoCount], SUM(Amount) AS TotalAmount, COUNT(DISTINCT Code) AS [CodeCount]

FROM @.Values

GROUP BY AcctNo) t

GROUP BY [CodeCount]