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

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,


LVL 1
eladrAsked:
Who is Participating?
 
ispalenyCommented:
You can store repeating part of SQL result into temporary table and use this table in your final query.
0
 
eladrAuthor Commented:
Which part should be in temporary table ?
any short code example ?

10x
0
 
JulianvaCommented:
the problem is that i have to repeat the query 32 times.

which part of the query changes
0
 
ispalenyCommented:
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
 
eladrAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.