eladr
asked on
"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,
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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the problem is that i have to repeat the query 32 times.
which part of the query changes
which part of the query changes
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
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
ASKER
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
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
ASKER
any short code example ?
10x