strange error message - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

strange error message

Author

Topic

DavidD
Yak Posting Veteran

73 Posts

Posted - 2004-01-15 : 20:45:32

Dear all,

I am getting the error message:

Server: Msg 4414, Level 16, State 1, Procedure prc_MTAllMATSales, Line 6
Could not allocate ancillary table for view or function resolution. The
maximum number of tables in a query (256) was exceeded.

when I try to run a proc, the proc itself uses only one view, which is based on 5 tables. The proc uses correlated sub-queries so I am wondering if these create temporary tables? The proc also works when I break it down and it compiles fine. Any ideas anyone?

I have enclosed the proc, it is large, but shouldn't kill a production server!!

Thanks in advance
David

alter proc prc_MTAllMATSales @product varchar(50), @startdate datetime,
@enddate datetime
as
--first the normal single products
if @product in ('COV2MG', 'COV4MG', 'COVPLU', 'AERODI', 'DIAMIC', 'DIAMMR',
'NATR30', 'GLYADE', 'MUPHOR', 'NATR30', 'NATRSR', 'NXSR90', 'NATR90')
begin
select g1.SalesDate, g1.NSW, [NSW MAT]
= (select sum(g2.NSW)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
[NSW MMA]
= (select avg(g2.NSW)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
[NSW MQT]
= (select sum(g2.NSW)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -3, g1.SalesDate)
and g2.Product = @product),
g1.Vic, [Vic MAT]
= (select sum(g2.Vic)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
[VIC MMA]
= (select avg(g2.Vic)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
g1.Qld, [Qld MAT]
= (select sum(g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
[Qld MMA]
= (select avg(g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
g1.Sa, [Sa MAT]
= (select sum(g2.Sa)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
[Sa MMA]
= (select avg(g2.Sa)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
g1.Wa, [Wa MAT]
= (select sum(g2.Wa)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
[Wa MMA]
= (select avg(g2.Wa)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
g1.Tas, [Tas MAT]
= (select sum(g2.Tas)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
[Tas MMA]
= (select avg(g2.Tas)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
g1.NZ, [NZ MAT]
= (select sum(g2.NZ)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
[NZ MMA]
= (select avg(g2.NZ)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product),
[MQT]
= (select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -3, g1.SalesDate)
and g2.Product = @product),
[MAT Growth]
= (select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product)/
(select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate between dateadd(mm, -23, g1.SalesDate) and dateadd(mm,
-12, g1.SalesDate)
and g2.Product = @product) * 100 - 100,
[MQT Growth]
= (select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -3, g1.SalesDate)
and g2.Product = @product)/
(select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate between dateadd(mm, -14, g1.SalesDate) and dateadd(mm,
-12, g1.SalesDate)
and g2.Product = @product) * 100 - 100,
[MAT Uplift]
= (select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product = @product) -
(select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate between dateadd(mm, -23, g1.SalesDate) and dateadd(mm,
-12, g1.SalesDate)
and g2.Product = @product),
[MQT Uplift]
= (select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -3, g1.SalesDate)
and g2.Product = @product) -
(select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate between dateadd(mm, -14, g1.SalesDate) and dateadd(mm,
-12, g1.SalesDate)
and g2.Product = @product)
from
vw_AllProducts g1
where Product = @product
and SalesDate between @startdate and @enddate
order by g1.SalesDate
end

--now the combination products
else if @product in ('Total Cov', 'Total Nat', 'Total Dim')
begin
if @product = 'Total Cov' set @product = '(''COV2MG'', ''COV4MG'',
''COVPLU'')'
else if @product = 'Total Nat' set @product = '(''NATR30'', ''NATRSR'')'
else if @product = 'Total Dim' set @product = '(''DIAMIC'', ''DIAMMR'')'

declare @sql varchar(5000)

set @sql =
'select g1.SalesDate, sum(g1.NSW) as NSW, [NSW MAT]
= (select sum(g2.NSW)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
[NSW MMA]
= (select avg(g2.NSW)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
sum(g1.Vic) as Vic, [Vic MAT]
= (select sum(g2.Vic)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
[Vic MMA]
= (select avg(g2.Vic)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
sum(g1.Qld) as Qld, [Qld MAT]
= (select sum(g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
[Qld MMA]
= (select avg(g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
sum(g1.Sa) as Sa, [Sa MAT]
= (select sum(g2.Sa)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
[Sa MMA]
= (select avg(g2.Sa)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
sum(g1.Wa) as Wa, [Wa MAT]
= (select sum(g2.Wa)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
[Wa MMA]
= (select avg(g2.Wa)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
sum(g1.Tas) as Tas, [Tas MAT]
= (select sum(g2.Tas)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
[Tas MMA]
= (select avg(g2.Tas)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
sum(g1.NZ) as NZ, [NZ MAT]
= (select sum(g2.NZ)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
[NZ MMA]
= (select avg(g2.NZ)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + '),
[MQT]
= (select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -3, g1.SalesDate)
and g2.Product in ' + @product + '),
[MAT Growth]
= (select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + ')/
(select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate between dateadd(mm, -23, g1.SalesDate) and dateadd(mm,
-12, g1.SalesDate)
and g2.Product in ' + @product + ') * 100 - 100,
[MQT Growth]
= (select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -3, g1.SalesDate)
and g2.Product in ' + @product + ')/
(select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate between dateadd(mm, -14, g1.SalesDate) and dateadd(mm,
-12, g1.SalesDate)
and g2.Product in ' + @product + ') * 100 - 100,
[MAT Uplift]
= (select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -12, g1.SalesDate)
and g2.Product in ' + @product + ') -
(select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate between dateadd(mm, -23, g1.SalesDate) and dateadd(mm,
-12, g1.SalesDate)
and g2.Product in ' + @product + '),
[MQT Uplift]
= (select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate > dateadd(mm, -3, g1.SalesDate)
and g2.Product in ' + @product + ') -
(select sum(g2.NSW + g2.Vic + g2.Sa + g2.Wa + g2.Tas + g2.Qld)
from vw_AllProducts g2
where g2.SalesDate
<= g1.SalesDate and
g2.SalesDate between dateadd(mm, -14, g1.SalesDate) and dateadd(mm,
-12, g1.SalesDate)
and g2.Product in ' + @product + ')
from
vw_AllProducts g1
where Product in ' + @product + '
and SalesDate between ''' + cast(@startdate as varchar(100)) + ''' and ''' +
cast(@enddate as varchar(100)) + '''
group by g1.SalesDate
order by g1.SalesDate'

--print @sql
exec(@sql)
end

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-01-15 : 22:16:33

Frankly I can't even BEGIN to figure out what this query is supposed to return, but yes, you are using way too many subqueries, and yes, they do use worktables to generate intermediate results if the optimizer needs them. As far as killing a production server, I'm amazed that it literally isn't pouring smoke out at you after running this.

Seriously, this is not the way you should be writing SQL, and I think you would be better off dumping this entirely and starting over. The CASE expression (see Books Online) will probably do all the work that this query does without a single subquery, but in order for us to help you'll need to explain what the results are supposed to look like. Right now the only thing I can make sense of is that almost all of the subqueries are identical, and that I don't have any idea why the dynamic SQL was used.

DavidD
Yak Posting Veteran

73 Posts

Posted - 2004-01-15 : 23:08:18

Thanks for the reply Rob,

The query is used to generate results for an MAT (Moving Annual Total) financial report, I have solved the problem by breaking up the results into temp tables and then joining them - actually making more work for the server, but obviously not having as many dynamic tables being opened to do the work.
I don't know of another way to calculate MATs other than using correlated sub-queries, and 17 in a report is no NASA calculation - I still think sql Server should have coped.

Regards
David

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-01-15 : 23:21:24

quote:

is no NASA calculation - I still think Sql Server should have coped.

If you turned this SQL query into its equivalent relational alegbra it would look like a NASA calculation!

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources