Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

"Maximum number of tables in a query (256) exceeded" - error

Posted on 2005-04-28
5
Medium Priority
?
2,230 Views
Last Modified: 2010-08-05
Hi All...
well, i have stored procedure that contain this query :

SELECT fields
FROM tbl
INNER JOIN
tbl2
ON field = field
INNER JOIN
tbl3
ON field = field
INNER JOIN
tbl4
ON field = field
INNER JOIN tbl5
ON field = field
WHERE (field = field)
AND (field = field LIKE  @var + '%')
AND (field >= @var1)
AND (field >= @var2)
AND (field > @var3)
AND (ISNULL(field,@var4) BETWEEN @var5 AND @var6)
AND field = 5

the problem is that i have to repeat the query 32 times.
In addition, i have 8 combinations of running it...
so,im reaching the 256 limit and im getting this error:

"Could not allocate ancillary table for a subquery. Maximum number of tables in a query (256) exceeded."

any ideas? i read in microsoft KB that i can install SP4 and then it enlarge the limit to 260.
also to fix the problem i i made it 31 times and instead of doing it more time i built user defined function
that do the query.
this was fixed the compiling error.Is it worth to do all the queries with user defined functions to avoid the error?

thanks,


0
Comment
Question by:eladr
  • 2
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
ispaleny earned 400 total points
ID: 13884564
You can store repeating part of SQL result into temporary table and use this table in your final query.
0
 
LVL 1

Author Comment

by:eladr
ID: 13884637
Which part should be in temporary table ?
any short code example ?

10x
0
 
LVL 8

Expert Comment

by:Julianva
ID: 13884768
the problem is that i have to repeat the query 32 times.

which part of the query changes
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 13884775
From your short question, I am not able to identify repeated blocks of code.

For example

select ...
from TableA
join
(select ...
 from TableB
 join Table C on ...
 where ...
 group by ...
) X
union all
select
from TableD
join
(select ...
 from TableB
 join Table C on ...
 where ...
 group by ...
) X on ...
where ...

I can rewrite to

select ...
into #q
 from TableB
 join Table C on ...
 where ...
 group by ...

select ...
from TableA
join #q X on ...
where ...
union all
select ...
from TableD
join #q X on ...
where ...

reducing table limit by 2



0
 
LVL 1

Author Comment

by:eladr
ID: 13884823
the code that changes is the first join

SELECT fields
FROM tbl
INNER JOIN

--REPEAT WITH 32 TABLES
TABLE_1 TABLE_2 and so on


ON field = field
INNER JOIN
tbl3
ON field = field
INNER JOIN
tbl4
ON field = field
INNER JOIN tbl5
ON field = field
WHERE (field = field)
AND (field = field LIKE  @var + '%')
AND (field >= @var1)
AND (field >= @var2)
AND (field > @var3)
AND (ISNULL(field,@var4) BETWEEN @var5 AND @var6)
AND field = 5
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question