February 20, 2012 at 12:32 am
What is the limit to the multivalue array or is there one ?
In my case the user selects all products and there are like 50 000
of them. The query ends up with an error message:
The query processor ran out of internal resources and could not produce a query plan.
I suppose the query could just ignore the selection condition
if the user wants all the products, but what if he wants
something like 49 000 of them ?
February 20, 2012 at 8:01 am
so are you saying that when you do for example select * from products, the server returns an error instead of returing all the rows of data?
***The first step is always the hardest *******
February 20, 2012 at 8:04 am
Please provide DDL for the table and DML for the select statement. Also, please include some sample data. It is possible that the server does not have enough memory to load all of the data because all data must be loaded into memory first. Can you also provide us SQL version, OS version, and memory amounts?
Jared
CE - Microsoft
February 20, 2012 at 11:36 pm
I try to describe my program:
First the user is able to browse all the products there are.
He can choose some of them or all of them or first all of them and
then exclude some of them.
The result will be (There's a temp table involved as well):
Insert into TempTable
Select Product from OrderLines
where Product in ('Product-1', 'Product-2' etc......, 'Product-500000')
This ends up in error:
The query processor ran out of internal resources and could not produce a query plan.
February 20, 2012 at 11:40 pm
February 20, 2012 at 11:58 pm
Here is the error log including the SQL.
I edited out the long product list.
[01/25/2012 17:21:18] Error
[ReportingServiceProxy.LogError::0]
Unable to execute dataset MainDataSet. Reason: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
SQL syntax:
------------------------------------------------
-- Myyntitilauskanta
------------------------------------------------
DECLARE @DefLang AS int
DECLARE @DefCur AS int, @DefCurISO AS char(3)
DECLARE @StSaleUnTxtTp AS int
DECLARE @ValGr3TxtTp AS int, @ValGr2TxtTp AS int, @ValGrTxtTp AS int
DECLARE @ValCustGr2TxtTp AS int, @ValCustGrTxtTp AS int
DECLARE @SelInd AS int, @DetailLevInd AS int, @LevInd AS int
DECLARE @TotalLineText AS varchar(80), @WeekText varchar(10)
DECLARE @Months TABLE (Name varchar (20),No varchar(2))
DECLARE @FromYYMMDD AS int
DECLARE @ToYYMMDD AS int
--
DECLARE @ISOLang AS VARCHAR(2)
IF 'fi-FI' = 'fi-FI' SET @ISOLang = 'FI'
IF 'fi-FI' = 'da-DK' SET @ISOLang = 'DK'
IF 'fi-FI' = 'sv-SE' SET @ISOLang = 'SE'
IF 'fi-FI' = 'nb-NO' SET @ISOLang = 'NO'
IF 'fi-FI' = 'de-DE' SET @ISOLang = 'DE'
IF 'fi-FI' NOT IN ('fi-FI', 'da-DK', 'sv-SE', 'nb-NO', 'de-DE') SET @ISOLang = 'GB'
--If ISO-codes are used:
SET @DefLang = (SELECT MIN(CtryNo) FROM Ctry WHERE ISO = @ISOLang)
SET @DefCur = (SELECT Cur FROM FrmData WHERE PK = 1)
IF @DefCur = 0 SET @DefCur = 99
SET @DefCurISO = (SELECT ISO FROM Cur WHERE CurNo = @DefCur)
SET @StSaleUnTxtTp = 16
SET @ValGrTxtTp = 42 SET @ValGr2TxtTp = 43 SET @ValGr3TxtTp = 44
SET @ValCustGrTxtTp = 24 SET @ValCustGr2TxtTp = 25
--
-- 04.10.2011 / map Käännökset ruotsiksi ja englanniksi
--
-- SET @WeekText = 'Viikko'
-- SET @TotalLineText = 'Y h t e e n s ä'
IF 'fi-FI' = 'fi-FI'
SET @WeekText = 'Viikko'
ELSE
IF 'fi-FI' = 'sv-SE'
SET @WeekText = 'Vecka'
ELSE
SET @WeekText = 'Week'
IF 'fi-FI' = 'fi-FI'
SET @TotalLineText = 'Y h t e e n s ä'
ELSE
IF 'fi-FI' = 'sv-SE'
SET @TotalLineText = 'T o t a l t'
ELSE
SET @TotalLineText = 'T o t a l'
SET @DetailLevInd = 4
IF 'fi-FI' = 'fi-FI'
BEGIN
INSERT INTO @Months (Name, No) VALUES ('Tammikuu', '01')
INSERT INTO @Months (Name, No) VALUES ('Helmikuu', '02')
INSERT INTO @Months (Name, No) VALUES ('Maaliskuu', '03')
INSERT INTO @Months (Name, No) VALUES ('Huhtikuu', '04')
INSERT INTO @Months (Name, No) VALUES ('Toukokuu', '05')
INSERT INTO @Months (Name, No) VALUES ('Kesäkuu', '06')
INSERT INTO @Months (Name, No) VALUES ('Heinäkuu', '07')
INSERT INTO @Months (Name, No) VALUES ('Elokuu', '08')
INSERT INTO @Months (Name, No) VALUES ('Syyskuu', '09')
INSERT INTO @Months (Name, No) VALUES ('Lokakuu', '10')
INSERT INTO @Months (Name, No) VALUES ('Marraskuu', '11')
INSERT INTO @Months (Name, No) VALUES ('Joulukuu', '12')
END
ELSE
IF 'fi-FI' = 'sv-SE'
BEGIN
INSERT INTO @Months (Name, No) VALUES ('Januari', '01')
INSERT INTO @Months (Name, No) VALUES ('Februari', '02')
INSERT INTO @Months (Name, No) VALUES ('Mars', '03')
INSERT INTO @Months (Name, No) VALUES ('April', '04')
INSERT INTO @Months (Name, No) VALUES ('Maj', '05')
INSERT INTO @Months (Name, No) VALUES ('Juni', '06')
INSERT INTO @Months (Name, No) VALUES ('Juli', '07')
INSERT INTO @Months (Name, No) VALUES ('Augusti', '08')
INSERT INTO @Months (Name, No) VALUES ('September', '09')
INSERT INTO @Months (Name, No) VALUES ('Oktober', '10')
INSERT INTO @Months (Name, No) VALUES ('November', '11')
INSERT INTO @Months (Name, No) VALUES ('December', '12')
END
ELSE
BEGIN
INSERT INTO @Months (Name, No) VALUES ('January', '01')
INSERT INTO @Months (Name, No) VALUES ('February', '02')
INSERT INTO @Months (Name, No) VALUES ('March', '03')
INSERT INTO @Months (Name, No) VALUES ('April', '04')
INSERT INTO @Months (Name, No) VALUES ('May', '05')
INSERT INTO @Months (Name, No) VALUES ('June', '06')
INSERT INTO @Months (Name, No) VALUES ('July', '07')
INSERT INTO @Months (Name, No) VALUES ('August', '08')
INSERT INTO @Months (Name, No) VALUES ('September', '09')
INSERT INTO @Months (Name, No) VALUES ('October', '10')
INSERT INTO @Months (Name, No) VALUES ('November', '11')
INSERT INTO @Months (Name, No) VALUES ('December', '12')
END
SET @ToYYMMDD=CONVERT(varchar(10), 20120131, 112)
SET @FromYYMMDD=CONVERT(varchar(10), 20120101, 112)
IF @FromYYMMDD = 0 SET @FromYYMMDD = 19900101
IF @ToYYMMDD = 0 SET @ToYYMMDD = 20501231
-- Taulun määritys
DECLARE @TmpTable TABLE (SelectIndex int,
Group1N int, Group1A varchar(40), Group2N int, Group2A varchar(40), Group3N int, Group3A varchar(40),
SortOrdN decimal(28,6), SortOrdA varchar(60), LevelNo int,
TrTp tinyint, OrdNo int, LnNo int, CustNo int, Nm varchar(80),SelBuy int,
ProdNo varchar(40), Descr varchar(60),NoInvoAb decimal(28,6), Price decimal(28,6),
Am decimal(28,6), DefCurAm decimal(28,6),StSaleUn int, StSaleUnTxt varchar(10),
OrdDt int, DelDt int, CfDelDt int, DeliveryDateN int, DeliveryDateX char(1),
W_Order varchar(20),
Cur int, CurISO char(3), ExRt decimal(28,6))
-- Insert : alin taso
SET @SelInd = 1 SET @LevInd = @DetailLevInd
INSERT INTO @TmpTable(
SelectIndex, LevelNo
, Group1N, Group1A, Group2N, Group2A, Group3N, Group3A, SortOrdN, SortOrdA
, TrTp, OrdNo, LnNo, CustNo, Nm, SelBuy, ProdNo, Descr
, NoInvoAb, Price, Am, DefCurAm, StSaleUn, StSaleUnTxt
, OrdDt, DelDt, CfDelDt, DeliveryDateN, DeliveryDateX
, W_Order, Cur, CurISO, ExRt)
SELECT@SelInd, @LevInd
, CASE '0'
WHEN 1 THEN OrdLn.R1
WHEN 2 THEN OrdLn.R2
WHEN 3 THEN OrdLn.R3
WHEN 4 THEN OrdLn.R4
WHEN 5 THEN OrdLn.R5
WHEN 6 THEN OrdLn.R6
WHEN 7 THEN OrdLn.R7
WHEN 8 THEN OrdLn.R8
WHEN 9 THEN OrdLn.R9
WHEN 10 THEN OrdLn.R10
WHEN 11 THEN OrdLn.R11
WHEN 12 THEN OrdLn.R12
WHEN 13 THEN OrdLn.CustNo
WHEN 14 THEN Actor.Gr
WHEN 15 THEN Actor.Gr2
WHEN 16 THEN Prod.Gr
WHEN 17 THEN Prod.Gr2
WHEN 18 THEN Prod.Gr3
WHEN 19 THEN OrdLn.SelBuy
WHEN 22 THEN Ord.OrdNo
WHEN 23 THEN
CASE OrdLn.CfDelDt
WHEN 0 THEN
SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 1, 4) +
CAST(DATEPART(WEEK, CONVERT(DATETIME, (SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 3, 6)), 12)) AS char(2))
ELSESUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 1, 4) +
CAST(DATEPART(WEEK, CONVERT(DATETIME, (SUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 3, 6)), 12)) AS CHAR(2))
END
ELSE0
END
, CASE '0'
WHEN 20 THEN
CASE OrdLn.CfDelDt
WHEN 0 THEN
SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 1, 4) +
SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 5, 2)
ELSESUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 1, 4) +
SUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 5, 2)
END
WHEN 21 THEN OrdLn.ProdNo
ELSE''
END
, CASE '0'
WHEN 1 THEN OrdLn.R1
WHEN 2 THEN OrdLn.R2
WHEN 3 THEN OrdLn.R3
WHEN 4 THEN OrdLn.R4
WHEN 5 THEN OrdLn.R5
WHEN 6 THEN OrdLn.R6
WHEN 7 THEN OrdLn.R7
WHEN 8 THEN OrdLn.R8
WHEN 9 THEN OrdLn.R9
WHEN 10 THEN OrdLn.R10
WHEN 11 THEN OrdLn.R11
WHEN 12 THEN OrdLn.R12
WHEN 13 THEN OrdLn.CustNo
WHEN 14 THEN Actor.Gr
WHEN 15 THEN Actor.Gr2
WHEN 16 THEN Prod.Gr
WHEN 17 THEN Prod.Gr2
WHEN 18 THEN Prod.Gr3
WHEN 19 THEN OrdLn.SelBuy
WHEN 22 THEN Ord.OrdNo
WHEN 23 THEN
CASE OrdLn.CfDelDt
WHEN 0 THEN
SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 1, 4) +
CAST(DATEPART(WEEK, CONVERT(DATETIME, (SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 3, 6)), 12)) AS char(2))
ELSESUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 1, 4) +
CAST(DATEPART(WEEK, CONVERT(DATETIME, (SUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 3, 6)), 12)) AS CHAR(2))
END
ELSE0
END
, CASE '0'
WHEN 20 THEN
CASE OrdLn.CfDelDt
WHEN 0 THEN
SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 1, 4) +
SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 5, 2)
ELSESUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 1, 4) +
SUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 5, 2)
END
WHEN 21 THEN OrdLn.ProdNo
ELSE''
END
, CASE '0'
WHEN 1 THEN OrdLn.R1
WHEN 2 THEN OrdLn.R2
WHEN 3 THEN OrdLn.R3
WHEN 4 THEN OrdLn.R4
WHEN 5 THEN OrdLn.R5
WHEN 6 THEN OrdLn.R6
WHEN 7 THEN OrdLn.R7
WHEN 8 THEN OrdLn.R8
WHEN 9 THEN OrdLn.R9
WHEN 10 THEN OrdLn.R10
WHEN 11 THEN OrdLn.R11
WHEN 12 THEN OrdLn.R12
WHEN 13 THEN OrdLn.CustNo
WHEN 14 THEN Actor.Gr
WHEN 15 THEN Actor.Gr2
WHEN 16 THEN Prod.Gr
WHEN 17 THEN Prod.Gr2
WHEN 18 THEN Prod.Gr3
WHEN 19 THEN OrdLn.SelBuy
WHEN 22 THEN Ord.OrdNo
WHEN 23 THEN
CASE OrdLn.CfDelDt
WHEN 0 THEN
SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 1, 4) +
CAST(DATEPART(WEEK, CONVERT(DATETIME, (SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 3, 6)), 12)) AS char(2))
ELSESUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 1, 4) +
CAST(DATEPART(WEEK, CONVERT(DATETIME, (SUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 3, 6)), 12)) AS CHAR(2))
END
ELSE0
END
, CASE '0'
WHEN 20 THEN
CASE OrdLn.CfDelDt
WHEN 0 THEN
SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 1, 4) +
SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 5, 2)
ELSESUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 1, 4) +
SUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 5, 2)
END
WHEN 21 THEN OrdLn.ProdNo
ELSE''
END
-- Lajittelukenttä, numeerinen
, 0
-- Lajittelukenttä, aakkosellinen
, CASE '0'
WHEN 0 THEN OrdLn.ProdNo
WHEN 1 THEN
CASE OrdLn.CfDelDt
WHEN 0 THEN
SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 1, 4) +
SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 5, 2) +
SUBSTRING(CAST(OrdLn.DelDt AS varchar(10)), 7, 2)
ELSESUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 1, 4) +
SUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 5, 2) +
SUBSTRING(CAST(OrdLn.CfDelDt AS varchar(10)), 7, 2)
END
ELSE''
END
, Ord.TrTp, Ord.OrdNo, OrdLn.LnNo, Ord.CustNo, Actor.Nm, OrdLn.SelBuy, OrdLn.ProdNo, Prod.Descr
, OrdLn.NoInvoAb, OrdLn.Price, OrdLn.Am, OrdLn.Am / OrdLn.ExRt, Prod.StSaleUn, NULL
, Ord.OrdDt, OrdLn.DelDt, OrdLn.CfDelDt
, CASE OrdLn.CfDelDt
WHEN 0 THEN OrdLn.DelDt
ELSE OrdLn.CfDelDt
END As DeliveryDateN
, CASE WHEN (OrdLn.CfDelDt = 0 AND OrdLn.DelDt = 0)
THEN 'X'
ELSE ' '
END AS DeliveryDateX
, CAST(Ord.OrdNo AS varchar(20)) + ' / ' + CAST(OrdLn.LnNo AS varchar(20))
, OrdLn.Cur
, CASE OrdLn.Cur WHEN @DefCur THEN @DefCurISO END
, OrdLn.ExRt
FROM OrdLnLEFT JOIN Ord ON OrdLn.OrdNo = Ord.OrdNo
INNER JOIN Actor ON Ord.CustNo = Actor.CustNo
LEFT JOIN Prod ON OrdLn.ProdNo = Prod.ProdNo
WHEREOrd.TrTp = 1 AND Ord.OrdTp IN (1, 2) AND OrdLn.NoinvoAb > 0 AND Ord.CustNo > 0 AND
OrdLn.CustNo IN ('10000','10008','10012','10022','10024','10026','10032','10033','10040','10047','10069','10072','10079','10089','10101','10128','10134','10135','10139','10145','10150','10167','10168','10174','10175','10178','10186','10206','10208','10209','10211','10219','10220','10221','10231','10256','10265','10276','10279','10280','10283','10290','10293','10356','10362','10366','10378','10383','10389','10391','10406','10446','10451','10452','10478','10480','10488','10538','10578','10691','10734','10748','10762','10778','10781','10785','10792','10797','10845','10847','10871','10888','10894','11004','11039','11043','11086','11093','11101','11128','11156','11160','11164','11165','11166','11169','11181','11189','11194','11195','12008','12012','12027','12037','12049','12057','12065','12069','12070','12071','12077','12080','12090','12091','12093','12094','12098','12099','12102','12105','12108','12109','12112','12113','12114','12116','12118','12122','12128','12131','12137','12140','12141','12143','12144','12145','12148','12152','12157','12164','12177','12185','12195','12204','12207','12208','12219','12221','12222','12230','12235','12240','12241','12248','12251','12252','12257','12262','12263','12268','12270','12271','12275','12277','12279','12280','12281','12282','12289','12292','12307','12312','12321','12324','12341','12343','12346','12347','12348','12349','12350','12351','12352','12353','12354','12355','12356','12357','12358','12359','12360','12361','12362','12363','12364','12365','12366') AND
Actor.Gr IN ('0') AND
Actor.Gr2 IN ('0') AND
OrdLn.SelBuy IN ('0','1','3','4','5') AND
Prod.Gr IN ('0') AND
Prod.Gr2 IN ('0') AND
Prod.Gr3 IN ('0') AND
OrdLn.ProdNo IN ('00066','00060' -- Here's the list of the 500000 Products -- ,'ZRRRRR','ZZZZ') AND
('1' = 1 AND
((OrdLn.CfDelDt = 0 AND OrdLn.DelDt >= @FromYYMMDD AND OrdLn.DelDt <= @ToYYMMDD) OR
(OrdLn.CfDelDt <> 0 AND OrdLn.CfDelDt >= @FromYYMMDD AND OrdLn.CfDelDt <= @ToYYMMDD) OR
(OrdLn.CfDelDt = 0 AND OrdLn.DelDt = 0) )
OR '1' = 2 AND
((Ord.OrdDt >= @FromYYMMDD AND Ord.OrdDt <= @ToYYMMDD) OR Ord.OrdDt = 0 )
)
-- Update : haetaan yksikön selite
UPDATE @TmpTableSET StSaleUnTxt = Txt.Txt
FROM @TmpTable TmpA INNER JOIN Txt ON Txt.TxtNo = TmpA.StSaleUn
WHERE LevelNo = @DetailLevInd AND Txt.Lang = @DefLang AND Txt.TxtTp = @StSaleUnTxtTp
-- Update : haetaan valuutan tiedot
UPDATE @TmpTableSET CurISO = Cur.ISO
FROM @TmpTable TmpA INNER JOIN Cur ON Cur.CurNo = TmpA.Cur
WHERE TmpA.LevelNo = @DetailLevInd
-- Insert: ryhmittely Group1:n, Group2:n ja Group3:n mukaan
IF '0' <> 0 BEGIN
SET @SelInd = @SelInd + 1 SET @LevInd = @LevInd - 1
INSERT INTO @TmpTable(SelectIndex, LevelNo, Group1N, Group1A, Group2N, Group2A, Group3N, Group3A, Descr, Am, DefCurAm, ExRt, Cur, CurISO, DeliveryDateX)
SELECT@SelInd, @LevInd, Group1N, Group1A, Group2N, Group2A, Group3N, Group3A
, CASE '0'
WHEN 1 THEN (SELECT R1.Nm FROM R1
WHERE R1.Rno = Tmp3.Group3N
GROUP BY R1.Nm)
WHEN 2 THEN (SELECT R2.Nm FROM R2
WHERE R2.Rno = Tmp3.Group3N
GROUP BY R2.Nm)
WHEN 3 THEN (SELECT R3.Nm FROM R3
WHERE R3.Rno = Tmp3.Group3N
GROUP BY R3.Nm)
WHEN 4 THEN (SELECT R4.Nm FROM R4
WHERE R4.Rno = Tmp3.Group3N
GROUP BY R4.Nm)
WHEN 5 THEN (SELECT R5.Nm FROM R5
WHERE R5.Rno = Tmp3.Group3N
GROUP BY R5.Nm)
WHEN 6 THEN (SELECT R6.Nm FROM R6
WHERE R6.Rno = Tmp3.Group3N
GROUP BY R6.Nm)
WHEN 7 THEN (SELECT R7.Nm FROM R7
WHERE R7.Rno = Tmp3.Group3N
GROUP BY R7.Nm)
WHEN 8 THEN (SELECT R8.Nm FROM R8
WHERE R8.Rno = Tmp3.Group3N
GROUP BY R8.Nm)
WHEN 9 THEN (SELECT R9.Nm FROM R9
WHERE R9.Rno = Tmp3.Group3N
GROUP BY R9.Nm)
WHEN 10 THEN (SELECT R10.Nm FROM R10
WHERE R10.Rno = Tmp3.Group3N
GROUP BY R10.Nm)
WHEN 11 THEN (SELECT R11.Nm FROM R11
WHERE R11.Rno = Tmp3.Group3N
GROUP BY R11.Nm)
WHEN 12 THEN (SELECT R12.Nm FROM R12
WHERE R12.Rno = Tmp3.Group3N
GROUP BY R12.Nm)
WHEN 13 THEN (SELECT Actor.Nm FROM Actor
WHERE Actor.CustNo = Tmp3.Group3N
GROUP BY Actor.Nm)
WHEN 14 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp3.Group3N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValCustGrTxtTp
GROUP BY Txt.Txt)
WHEN 15 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp3.Group3N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValCustGr2TxtTp
GROUP BY Txt.Txt)
WHEN 16 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp3.Group3N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValGrTxtTp
GROUP BY Txt.Txt)
WHEN 17 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp3.Group3N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValGr2TxtTp
GROUP BY Txt.Txt)
WHEN 18 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp3.Group3N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValGr3TxtTp
GROUP BY Txt.Txt)
WHEN 19 THEN (SELECT MAX(Actor.Nm) FROM Actor
WHERE Actor.EmpNo = Tmp3.Group3N AND Tmp3.Group3N > 0)
WHEN 20 THEN (SELECT Tmp3_1.Name + ' ' + (SUBSTRING(CAST(Tmp3.Group3A AS varchar(4)), 1, 4)) FROM @Months Tmp3_1
WHERE (SUBSTRING(CAST(Tmp3.Group3A AS varchar(6)), 5, 2)) = Tmp3_1.No)
WHEN 21 THEN (SELECT Prod.Descr FROM Prod
WHERE Tmp3.Group3A = Prod.ProdNo
GROUP BY Prod.Descr)
WHEN 23 THEN
@WeekText+ ' ' +SUBSTRING(CAST(Tmp3.Group3N AS VARCHAR(8)), 5, 2) + '/' +
SUBSTRING(CAST(Tmp3.Group3N AS VARCHAR(8)), 1, 4)
ELSE NULL
END
, SUM(Tmp3.DefCurAm)
, SUM(Tmp3.DefCurAm)
, 1, @DefCur, @DefCurISO, 'X' AS DeliveryDateX
FROM @TmpTable Tmp3
WHERE LevelNo = @LevInd + 1
GROUP BY SelectIndex, LevelNo, Group1N, Group1A, Group2N, Group2A, Group3N, Group3A
END
-- Insert: ryhmittely Group1:n ja Group2:n mukaan
IF '0' <> 0 BEGIN
SET @SelInd = @SelInd + 1 SET @LevInd = @LevInd - 1
INSERT INTO @TmpTable(SelectIndex, LevelNo, Group1N, Group1A, Group2N, Group2A, Descr, Am, DefCurAm, ExRt, Cur, CurISO, DeliveryDateX)
SELECT@SelInd, @LevInd, Group1N, Group1A, Group2N, Group2A
, CASE '0'
WHEN 1 THEN (SELECT R1.Nm FROM R1
WHERE R1.Rno = Tmp2.Group2N
GROUP BY R1.Nm)
WHEN 2 THEN (SELECT R2.Nm FROM R2
WHERE R2.Rno = Tmp2.Group2N
GROUP BY R2.Nm)
WHEN 3 THEN (SELECT R3.Nm FROM R3
WHERE R3.Rno = Tmp2.Group2N
GROUP BY R3.Nm)
WHEN 4 THEN (SELECT R4.Nm FROM R4
WHERE R4.Rno = Tmp2.Group2N
GROUP BY R4.Nm)
WHEN 5 THEN (SELECT R5.Nm FROM R5
WHERE R5.Rno = Tmp2.Group2N
GROUP BY R5.Nm)
WHEN 6 THEN (SELECT R6.Nm FROM R6
WHERE R6.Rno = Tmp2.Group2N
GROUP BY R6.Nm)
WHEN 7 THEN (SELECT R7.Nm FROM R7
WHERE R7.Rno = Tmp2.Group2N
GROUP BY R7.Nm)
WHEN 8 THEN (SELECT R8.Nm FROM R8
WHERE R8.Rno = Tmp2.Group2N
GROUP BY R8.Nm)
WHEN 9 THEN (SELECT R9.Nm FROM R9
WHERE R9.Rno = Tmp2.Group2N
GROUP BY R9.Nm)
WHEN 10 THEN (SELECT R10.Nm FROM R10
WHERE R10.Rno = Tmp2.Group2N
GROUP BY R10.Nm)
WHEN 11 THEN (SELECT R11.Nm FROM R11
WHERE R11.Rno = Tmp2.Group2N
GROUP BY R11.Nm)
WHEN 12 THEN (SELECT R12.Nm FROM R12
WHERE R12.Rno = Tmp2.Group2N
GROUP BY R12.Nm)
WHEN 13 THEN (SELECT Actor.Nm FROM Actor
WHERE Actor.CustNo = Tmp2.Group2N
GROUP BY Actor.Nm)
WHEN 14 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp2.Group2N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValCustGrTxtTp
GROUP BY Txt.Txt)
WHEN 15 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp2.Group2N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValCustGr2TxtTp
GROUP BY Txt.Txt)
WHEN 16 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp2.Group2N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValGrTxtTp
GROUP BY Txt.Txt)
WHEN 17 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp2.Group2N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValGr2TxtTp
GROUP BY Txt.Txt)
WHEN 18 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp2.Group2N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValGr3TxtTp
GROUP BY Txt.Txt)
WHEN 19 THEN (SELECT MAX(Actor.Nm) FROM Actor
WHERE Actor.EmpNo = Tmp2.Group2N AND Tmp2.Group2N > 0)
WHEN 20 THEN (SELECT Tmp2_1.Name + ' ' + (SUBSTRING(CAST(Tmp2.Group2A AS varchar(4)), 1, 4)) FROM @Months Tmp2_1
WHERE (SUBSTRING(CAST(Tmp2.Group2A AS varchar(6)), 5, 2)) = Tmp2_1.No)
WHEN 21 THEN (SELECT Prod.Descr FROM Prod
WHERE Tmp2.Group2A = Prod.ProdNo
GROUP BY Prod.Descr)
WHEN 23 THEN
@WeekText+ ' ' +SUBSTRING(CAST(Tmp2.Group2N AS VARCHAR(8)), 5, 2) + '/' +
SUBSTRING(CAST(Tmp2.Group2N AS VARCHAR(8)), 1, 4)
ELSE NULL
END
, SUM(Tmp2.DefCurAm)
, SUM(Tmp2.DefCurAm)
, 1, @DefCur, @DefCurISO, 'X' AS DeliveryDateX
FROM @TmpTable Tmp2
WHERE LevelNo = @LevInd + 1
GROUP BY SelectIndex, LevelNo, Group1N, Group1A, Group2N, Group2A
END
-- Insert: ryhmittely Group1:n mukaan
IF '0' <> 0 BEGIN
SET @SelInd = @SelInd + 1 SET @LevInd = @LevInd - 1
INSERT INTO @TmpTable(SelectIndex, LevelNo, Group1N, Group1A, Descr, Am, DefCurAm, ExRt, Cur, CurISO, DeliveryDateX)
SELECT@SelInd, @LevInd, Group1N, Group1A
, CASE '0'
WHEN 1 THEN (SELECT R1.Nm FROM R1
WHERE R1.Rno = Tmp1.Group1N
GROUP BY R1.Nm)
WHEN 2 THEN (SELECT R2.Nm FROM R2
WHERE R2.Rno = Tmp1.Group1N
GROUP BY R2.Nm)
WHEN 3 THEN (SELECT R3.Nm FROM R3
WHERE R3.Rno = Tmp1.Group1N
GROUP BY R3.Nm)
WHEN 4 THEN (SELECT R4.Nm FROM R4
WHERE R4.Rno = Tmp1.Group1N
GROUP BY R4.Nm)
WHEN 5 THEN (SELECT R5.Nm FROM R5
WHERE R5.Rno = Tmp1.Group1N
GROUP BY R5.Nm)
WHEN 6 THEN (SELECT R6.Nm FROM R6
WHERE R6.Rno = Tmp1.Group1N
GROUP BY R6.Nm)
WHEN 7 THEN (SELECT R7.Nm FROM R7
WHERE R7.Rno = Tmp1.Group1N
GROUP BY R7.Nm)
WHEN 8 THEN (SELECT R8.Nm FROM R8
WHERE R8.Rno = Tmp1.Group1N
GROUP BY R8.Nm)
WHEN 9 THEN (SELECT R9.Nm FROM R9
WHERE R9.Rno = Tmp1.Group1N
GROUP BY R9.Nm)
WHEN 10 THEN (SELECT R10.Nm FROM R10
WHERE R10.Rno = Tmp1.Group1N
GROUP BY R10.Nm)
WHEN 11 THEN (SELECT R11.Nm FROM R11
WHERE R11.Rno = Tmp1.Group1N
GROUP BY R11.Nm)
WHEN 12 THEN (SELECT R12.Nm FROM R12
WHERE R12.Rno = Tmp1.Group1N
GROUP BY R12.Nm)
WHEN 13 THEN (SELECT Actor.Nm FROM Actor
WHERE Actor.CustNo = Tmp1.Group1N
GROUP BY Actor.Nm)
WHEN 14 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp1.Group1N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValCustGrTxtTp
GROUP BY Txt.Txt)
WHEN 15 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp1.Group1N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValCustGr2TxtTp
GROUP BY Txt.Txt)
WHEN 16 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp1.Group1N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValGrTxtTp
GROUP BY Txt.Txt)
WHEN 17 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp1.Group1N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValGr2TxtTp
GROUP BY Txt.Txt)
WHEN 18 THEN (SELECT Txt.Txt FROM Txt
WHERE Txt.TxtNo = Tmp1.Group1N AND Txt.Lang = @DefLang AND Txt.TxtTp = @ValGr3TxtTp
GROUP BY Txt.Txt)
WHEN 19 THEN (SELECT MAX(Actor.Nm) FROM Actor
WHERE Actor.EmpNo = Tmp1.Group1N AND Tmp1.Group1N > 0)
WHEN 20 THEN (SELECT Tmp1_1.Name + ' ' + (SUBSTRING(CAST(Tmp1.Group1A AS varchar(4)), 1, 4)) FROM @Months Tmp1_1
WHERE (SUBSTRING(CAST(Tmp1.Group1A AS varchar(6)), 5, 2)) = Tmp1_1.No)
WHEN 21 THEN (SELECT Prod.Descr FROM Prod
WHERE Tmp1.Group1A = Prod.ProdNo
GROUP BY Prod.Descr)
WHEN 23 THEN
@WeekText+ ' ' +SUBSTRING(CAST(Tmp1.Group1N AS VARCHAR(8)), 5, 2) + '/' +
SUBSTRING(CAST(Tmp1.Group1N AS VARCHAR(8)), 1, 4)
ELSE NULL
END
, SUM(Tmp1.DefCurAm)
, SUM(Tmp1.DefCurAm)
, 1, @DefCur, @DefCurISO, 'X' AS DeliveryDateX
FROM @TmpTable Tmp1
WHERE LevelNo = @LevInd + 1
GROUP BY SelectIndex, LevelNo, Group1N, Group1A
END
-- Insert: Kaikki yhteensä
SET @SelInd = @SelInd + 1 SET @LevInd = @LevInd - 1
INSERT INTO @TmpTable(SelectIndex, LevelNo, Group1N, Group1A, Descr, Am, DefCurAm, ExRt, Cur, CurISO, DeliveryDateX)
SELECT @SelInd, 0
, 0, ''
, @TotalLineText
, SUM(Tmp0.DefCurAm)
, SUM(Tmp0.DefCurAm)
, 1, @DefCur, @DefCurISO, 'X' AS DeliveryDateX
FROM @TmpTable Tmp0
WHERE LevelNo = @LevInd + 1
GROUP BY SelectIndex, LevelNo
-- Lopullinen select
SELECT Tmpx.*
, CASE WHEN DeliveryDateN <> 0
THEN CONVERT(datetime, CAST(DeliveryDateN AS varchar), 112)
ELSE 0 END AS DeliveryDateA
FROM @TmpTable Tmpx
ORDER BY Group1N, Group1A, Group2N, Group2A, Group3N, Group3A, LevelNo, SortOrdN, SortOrdA
Type : Visma.Services.Reporting.ReportingSQLException
Stack : at Visma.Services.Reporting.Provider.DataProvider.ExecDataSet(String reportPath, String sqlCommand, String applicationType, String datasetName, Boolean useNumberOfRowsRestriction, CompanyItem company)
at Visma.Services.Reporting.Provider.DataProvider.GetCompanyData(String reportPath, RdlDataSet dataSetDefinition, List`1 parameters, Boolean useNumberOfRowsResctiction, User user, CompanyItem company)
at Visma.Services.Reporting.Provider.ProviderManager.GetDataSources(String reportPath, IEnumerable`1 dataSetDefinitions, ReportType reportType, List`1 parameters, Boolean useNumberOfRowsResctiction, User user, List`1 companies)
at Visma.Services.Reporting.Provider.ProviderManager.GetReportDataSources(String reportPath, List`1 parameters, User user, List`1 companies)
at Visma.Services.Reporting.Server.ReportingServiceProxyBase.GetReportDataSources(String reportPath, List`1 parameters, List`1 companies)
Source : Visma.Services.Reporting.Provider
Target : System.Data.DataTable ExecDataSet(System.String, System.String, System.String, System.String, Boolean, Visma.Services.Reporting.CompanyItem)
------- Inner Exception ------
Message: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
Type : System.Data.OleDb.OleDbException
Stack : at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)
at System.Data.OleDb.OleDbDataReader.NextResult()
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader()
at Visma.Services.Reporting.Provider.DataProvider.ExecDataSet(String reportPath, String sqlCommand, String applicationType, String datasetName, Boolean useNumberOfRowsRestriction, CompanyItem company)
Source : Microsoft OLE DB Provider for SQL Server
Target : Void ProcessResults(System.Data.OleDb.OleDbHResult)
February 21, 2012 at 4:28 am
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/a30ef396-7b26-4d18-81d2-291412a67e7a
While it would be a good idea to fix your query , try the steps in the above post. Try also assigning a sepearet application pool to reporting services.
Do you get the same error when ur try filtering for a smaller result set.
btw in ur query did u notice the flaw with the below statement
DECLARE @ISOLang AS VARCHAR(2)
IF 'fi-FI' = 'fi-FI' SET @ISOLang = 'FI'
IF 'fi-FI' = 'da-DK' SET @ISOLang = 'DK'
IF 'fi-FI' = 'sv-SE' SET @ISOLang = 'SE'
IF 'fi-FI' = 'nb-NO' SET @ISOLang = 'NO'
IF 'fi-FI' = 'de-DE' SET @ISOLang = 'DE'
IF 'fi-FI' NOT IN ('fi-FI', 'da-DK', 'sv-SE', 'nb-NO', 'de-DE') SET @ISOLang = 'GB'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply