Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Exceeds 256 table limit

Posted on 2002-03-27
10
Medium Priority
?
1,546 Views
Last Modified: 2012-06-21
When i am trying to run query i get the following error:

Server: Msg 4408, Level 16, State 1, Line 1
The query and the views or functions in it exceed the limit of 256 tables.

the query command is long, but i have not had this problem before running the exact same query.  any possible solutions?


here is the query, like i said it is pretty long.  it is pretty repetitive, actually it is the exact same query, just repeated for as many months as the user selects.  the max # of months is 12 months, the code below shows the query for 12 months.  oh one more thing, if i run the query for 11 months instead of 12, i get no error.  

thanks for any help you can give.

select subdiv,g00, (g00-b00) b00, g01, (g01-b01) b01, g02, (g02-b02) b02, g03,
(g03-b03) b03, g04, (g04-b04) b04, g05, (g05-b05) b05, g06, (g06-b06) b06, g07,
(g07-b07) b07, g08, (g08-b08) b08, g09, (g09-b09) b09, g10, (g10-b10) b10, g11,
(g11-b11) b11, (g00+g01+g02+g03+g04+g05+g06+g07+g08+g09+g10+g11) as gTotal,
((g00-b00)+(g01-b01)+(g02-b02)+(g03-b03)+(g04-b04)+(g05-b05)+(g06-b06)+(g07-b07)+
(g08-b08)+(g09-b09)+(g10-b10)+(g11-b11)) as bTotal,
subabv,subnam, regn#, regnam
from
(select s.subdiv, isnull(g00.stat,0) as g00, isnull(b00.stat,0) as b00, isnull(g01.stat,0) as g01,
 isnull(b01.stat,0) as b01, isnull(g02.stat,0) as g02, isnull(b02.stat,0) as b02, isnull(g03.stat,0) as g03,
isnull(b03.stat,0) as b03, isnull(g04.stat,0) as g04, isnull(b04.stat,0) as b04, isnull(g05.stat,0) as g05,
 isnull(b05.stat,0) as b05, isnull(g06.stat,0) as g06, isnull(b06.stat,0) as b06, isnull(g07.stat,0) as g07,
isnull(b07.stat,0) as b07, isnull(g08.stat,0) as g08, isnull(b08.stat,0) as b08, isnull(g09.stat,0) as g09,
isnull(b09.stat,0) as b09, isnull(g10.stat,0) as g10, isnull(b10.stat,0) as b10, isnull(g11.stat,0) as g11,
isnull(b11.stat,0) as b11, s.subabv, s.subnam, r.regn#, r.regnam from
(select distinct subdiv,subnam,subabv, regn# from SBLDRRHI.dbo.v_sales_data
WHERE (slsdte <= '20021231' and slsdte >='20020101') OR (candte <='20021231' AND candte >='20021202')) s
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020101' and slsdte <= '20020127' AND trnfdt = '' group by subdiv, regn#) g00
on s.subdiv = g00.subdiv and s.regn# = g00.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data w
here candte >= '20020101' and candte <= '20020127' AND trtodt = '' group by subdiv, regn#) b00
on s.subdiv = b00.subdiv and s.regn# = b00.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020128' and slsdte <= '20020224' AND trnfdt = '' group by subdiv, regn#) g01
on s.subdiv = g01.subdiv and s.regn# = g01.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020128' and candte <= '20020224' AND trtodt = '' group by subdiv, regn#) b01
on s.subdiv = b01.subdiv and s.regn# = b01.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020225' and slsdte <= '20020331' AND trnfdt = '' group by subdiv, regn#) g02
on s.subdiv = g02.subdiv and s.regn# = g02.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020225' and candte <= '20020331' AND trtodt = '' group by subdiv, regn#) b02
on s.subdiv = b02.subdiv and s.regn# = b02.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020401' and slsdte <= '20020428' AND trnfdt = '' group by subdiv, regn#) g03
on s.subdiv = g03.subdiv and s.regn# = g03.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020401' and candte <= '20020428' AND trtodt = '' group by subdiv, regn#) b03
on s.subdiv = b03.subdiv and s.regn# = b03.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020429' and slsdte <= '20020526' AND trnfdt = '' group by subdiv, regn#) g04
on s.subdiv = g04.subdiv and s.regn# = g04.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020429' and candte <= '20020526' AND trtodt = '' group by subdiv, regn#) b04
on s.subdiv = b04.subdiv and s.regn# = b04.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020527' and slsdte <= '20020630' AND trnfdt = '' group by subdiv, regn#) g05
on s.subdiv = g05.subdiv and s.regn# = g05.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020527' and candte <= '20020630' AND trtodt = '' group by subdiv, regn#) b05
on s.subdiv = b05.subdiv and s.regn# = b05.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020701' and slsdte <= '20020728' AND trnfdt = '' group by subdiv, regn#) g06
on s.subdiv = g06.subdiv and s.regn# = g06.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020701' and candte <= '20020728' AND trtodt = '' group by subdiv, regn#) b06
on s.subdiv = b06.subdiv and s.regn# = b06.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020729' and slsdte <= '20020901' AND trnfdt = '' group by subdiv, regn#) g07
on s.subdiv = g07.subdiv and s.regn# = g07.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020729' and candte <= '20020901' AND trtodt = '' group by subdiv, regn#) b07
on s.subdiv = b07.subdiv and s.regn# = b07.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020902' and slsdte <= '20020929' AND trnfdt = '' group by subdiv, regn#) g08
on s.subdiv = g08.subdiv and s.regn# = g08.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020902' and candte <= '20020929' AND trtodt = '' group by subdiv, regn#) b08
on s.subdiv = b08.subdiv and s.regn# = b08.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20020930' and slsdte <= '20021103' AND trnfdt = '' group by subdiv, regn#) g09
on s.subdiv = g09.subdiv and s.regn# = g09.regn#
 left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20020930' and candte <= '20021103' AND trtodt = '' group by subdiv, regn#) b09
on s.subdiv = b09.subdiv and s.regn# = b09.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20021104' and slsdte <= '20021201' AND trnfdt = '' group by subdiv, regn#) g10
on s.subdiv = g10.subdiv and s.regn# = g10.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20021104' and candte <= '20021201' AND trtodt = '' group by subdiv, regn#) b10
on s.subdiv = b10.subdiv and s.regn# = b10.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where slsdte >= '20021202' and slsdte <= '20021231' AND trnfdt = '' group by subdiv, regn#) g11
on s.subdiv = g11.subdiv and s.regn# = g11.regn#
left outer join (select subdiv, sum(slspct) stat, regn# from SBLDRRHI.dbo.v_sales_data
where candte >= '20021202' and candte <= '20021231' AND trtodt = '' group by subdiv, regn#) b11
on s.subdiv = b11.subdiv and s.regn# = b11.regn# left outer join SBLDRRHI.dbo.region r
on r.regn# = s.regn#) x
0
Comment
Question by:houmerlin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
10 Comments
 
LVL 2

Expert Comment

by:JamesT
ID: 6899392
Can you post the query?
0
 
LVL 2

Accepted Solution

by:
JamesT earned 400 total points
ID: 6899845
From Books On Line:

Maximum Tables per SELECT statement 256

In your select statement you have

Select ....
from (select ......)

In the second select statement, for each value you receive back must count as one table. You must be getting back more than 256 values and hence count as more than 256 tables. To test this, take the second select out and run it by itself. If you get back more than 256 results this confirms. If not, I have no idea.

Just a thought.
0
 

Author Comment

by:houmerlin
ID: 6900088
is there any way increase the system value for 256 tables allowed?
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 2

Expert Comment

by:JamesT
ID: 6900318
No. That is a limit of SQL Server. Sorry. Did you copy just the second select out and run it? Did it return more than 256 results?
0
 

Author Comment

by:houmerlin
ID: 6900334
yes i copied the second select and ran it and i get the same results.  

i have found that if i comment out the last two left outer joins everything works.  it is very frustrating.  i will figure something out.

thanks for your help.

-paul
0
 
LVL 2

Expert Comment

by:JamesT
ID: 6900391
No problem Wish I was of more help.
0
 

Author Comment

by:houmerlin
ID: 6900756
james,

i found the source of the problem.  the view had a large number of columns in the view.  i created a new view which only contained the columns we needed and it seems everything it working perfectly.  

thanks again for your help.

paul
0
 
LVL 2

Expert Comment

by:JamesT
ID: 6900790
So the view had more than 256 columns? Glad to help.
0
 

Expert Comment

by:CleanupPing
ID: 9280686
houmerlin:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

636 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