The Query cannot be completed...

[Kladdi]

Hallo,

möchte eine Tabelle mit 19 Spalten befüllen, von diesen 19 Spalten sind 5 Referenzen auf 5 andere Tabellen.
114 Datensätze sollen eingefügt werden und von der Syntax her sollte die Abfrage auch ordnungsgemäß laufen,
trotzdem erhalte ich diese Fehlermeldung:

The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result.

Habe bereits nebenbei den Task-Manager laufen lassen und bei 4 GB RAM stößt er nicht an seine Grenzen. Auch die Datenbank überschreitet nicht mal die 10 mb.

Woran kann das noch liegen?

Mfg Kladdi

EDIT: Hier noch meine Abfrage:

Code [Auswählen] Erweitern

INSERT INTO tblBooking ( [boRevenue Recognition Fiscal Year Month Display Code], [boManufacturing Product Global Business Unit Description], [boSource System Name], [boInternal Process Code], [boSales Channel Route Name], [boManufacturing Product Family Description], [boManufacturing Product Type Name], [boDerived End Customer Source Industry Segment Name], [boRevenue Recognition Calendar Date MM DD YYYY Code], boRevUSD, [boTotal Cost Of Sales US Dollar Amount], [boGross Margin US Dollar Amount], [boGross Margin Percent], [boUnit Quantitiy], [boBusiness Area CodeRef], [boProfit Center DescriptionRef], [boLegacy Sales Order IdentifierRef], [boSold To AMID Level 2 IdentifierRef], [boDerived End Customer_Ref] )
SELECT DISTINCT R.[Revenue Recognition Fiscal Year Month Display Code], R.[Manufacturing Product Global Business Unit Description], R.[Source System Name], R.[Internal Process Code], R.[Sales Channel Route Name], R.[Manufacturing Product Family Description], R.[Manufacturing Product Type Name], R.[Derived End Customer Source Industry Segment Name], R.[Revenue Recognition Calendar Date MM DD YYYY Code], R.[Net Revenue US Dollar Amount], R.[Total Cost Of Sales US Dollar Amount], R.[Gross Margin US Dollar Amount], R.[Gross Margin Percent], R.[Unit Quantity], B3.[L3ID], S.[salID], P.[profID], L2.[soID], D.[deID]
FROM
tblDerivedEndCustomerAMIDLevel2Identifier AS D INNER JOIN
(tblSoldToAMIDLevel2ID AS L2 INNER JOIN
(tblSalesOrderIdentifierBilling AS S INNER JOIN
(tblProfitCenter AS P INNER JOIN (tblBUL3 AS B3 INNER JOIN Report AS R
ON R.[Business Area Code] = B3.[L3Business Area Code]) ON R.[Profit Center Description] = P.[profProfit Center Description])
ON R.[Legacy Sales Order Identifier] = S.[salLegacy Sales Order Identifier])
ON R.[Sold To AMID Level 2 Identifier] = L2.[soSold To AMID Level 2 Identifier])
ON R.[Derived End Customer AMID Level 2 Identifier] = D.[deDerived End Customer AMID Level 2 Identifier]
WHERE NOT Exists
(SELECT Null
FROM
tblBooking AS B
WHERE
B.[boRevenue Recognition Fiscal Year Month Display Code] = R.[Revenue Recognition Fiscal Year Month Display Code] AND
B.[boManufacturing Product Global Business Unit Description] = R.[Manufacturing Product Global Business Unit Description] AND
B.[boSource System Name] = R.[Source System Name] AND
B.[boInternal Process Code] = R.[Internal Process Code] AND
B.[boSales Channel Route Name] = R.[Sales Channel Route Name] AND
B.[boManufacturing Product Family Description] = R.[Manufacturing Product Family Description] AND
B.[boManufacturing Product Type Name] =  R.[Manufacturing Product Type Name] AND
B.[boDerived End Customer Source Industry Segment Name] = R.[Derived End Customer Source Industry Segment Name] AND
B.[boRevenue Recognition Calendar Date MM DD YYYY Code] = R.[Revenue Recognition Calendar Date MM DD YYYY Code] AND
B.[boRevUSD] = R.[Net Revenue US Dollar Amount] AND
B.[boTotal Cost Of Sales US Dollar Amount] = R.[Total Cost Of Sales US Dollar Amount] AND
B.[boGross Margin US Dollar Amount] = R.[Gross Margin US Dollar Amount] AND
B.[boGross Margin Percent] = R.[Gross Margin Percent] AND
B.[boUnit Quantitiy] = R.[Unit Quantity] AND
B.[boBusiness Area CodeRef] = B3.[L3ID] AND
B.[boProfit Center DescriptionRef] = P.[profID] AND
B.[boLegacy Sales Order IdentifierRef] = S.[salID] AND
B.[boSold To AMID Level 2 IdentifierRef] = L2.[soID] AND
B.[boDerived End Customer_Ref] = D.[deID]
);


[ebs17]

Als erstes würde ich über die Accessspezifikationen prüfen, ob da ein Wert überschritten wird (Länge des Kriteriums).

MfGA
ebs

[Kladdi]

Da haben wir den Fehler!
Danke dir 

Anzahl der Zeichen in einer Zelle des Abfrageentwurfsbereichs    1.024

[ebs17]

Wenn Du aber weißt, was Du schreiben willst, dann schickst Du den Abfrageentwurf einfach über den Jordan.

Eine SQL-Anweisung selber darf bis etwa 64.000 Zeichen enthalten, und wenn Du in VBA Deine Anweisung etwas geschickt zusammenbaust und dort auch ausführst, kommst Du dem Abfrageentwurf nicht zu nahe und erhältst eine zweite Chance.

Ggf. müsste man die Abfrage aber auch etwas umformulieren. Eine Lösung ist nicht immer die einzige Lösung.

MfGA
ebs

[Kladdi]

Ich bin noch neu in SQL und bin froh dass ich überhaupt diese Abfrage hinbekommen hab.  
Du siehst nicht zufällig den Syntax Error in der FROM Klausel der mir hier angezeigt wird?!  

Code [Auswählen] Erweitern

        sSQL = "INSERT INTO tblBooking ( [boRevenue Recognition Fiscal Year Month Display Code], [boManufacturing Product Global Business Unit Description], [boSource System Name], [boInternal Process Code], [boSales Channel Route Name], [boManufacturing Product Family Description], [boManufacturing Product Type Name], [boDerived End Customer Source Industry Segment Name], [boRevenue Recognition Calendar Date MM DD YYYY Code], [boRevUSD], [boTotal Cost Of Sales US Dollar Amount], [boGross Margin US Dollar Amount], [boGross Margin Percent], [boUnit Quantitiy], [boBusiness Area CodeRef], [boProfit Center DescriptionRef], [boLegacy Sales Order IdentifierRef], [boSold To AMID Level 2 IdentifierRef], [boDerived End Customer_Ref] )" & _
"SELECT DISTINCT R.[Revenue Recognition Fiscal Year Month Display Code], R.[Manufacturing Product Global Business Unit Description], R.[Source System Name], R.[Internal Process Code], R.[Sales Channel Route Name], R.[Manufacturing Product Family Description], R.[Manufacturing Product Type Name], R.[Derived End Customer Source Industry Segment Name], R.[Revenue Recognition Calendar Date MM DD YYYY Code], R.[Net Revenue US Dollar Amount], R.[Total Cost Of Sales US Dollar Amount], R.[Gross Margin US Dollar Amount], R.[Gross Margin Percent], R.[Unit Quantity], B3.[L3ID], P.[profID], L.[salID], S.[soID], D.[deID]" & _
"FROM ([tblBUL3] AS B3 INNER JOIN" & _
"([tblProfitCenter] AS P INNER JOIN" & _
"([tblSalesOrderIdentifierBilling] AS L INNER JOIN" & _
"([tblSoldToAMIDLevel2ID] AS S INNER JOIN" & _
"([tblDerivedEndCustomerAMIDLevel2Identifier] AS D INNER JOIN [Report] AS R" & _
"ON R.[Derived End Customer AMID Level 2 Identifier] = D.[deDerived End Customer AMID Level 2 Identifier])" & _
"ON R.[Sold To AMID Level 2 Identifier] = S.[soSold To AMID Level 2 Identifier])" & _
"ON R.[Legacy Sales Order Identifier] = L.[salLegacy Sales Order Identifier])" & _
"ON R.[Profit Center Description] = P.[profProfit Center Description])" & _
"ON R.[Business Area Code] = B3.[L3Business Area Code])" & _
"WHERE NOT Exists" & _
"(SELECT Null FROM [tblBooking] As B WHERE" & _
"B.[boRevenue Recognition Fiscal Year Month Display Code] = R.[Revenue Recognition Fiscal Year Month Display Code] AND B.[boManufacturing Product Global Business Unit Description] = R.[Manufacturing Product Global Business Unit Description] AND" & _
"B.[boSource System Name] = R.[Source System Name] AND B.[boInternal Process Code] = R.[Internal Process Code] AND" & _
"B.[boSales Channel Route Name] = R.[Sales Channel Route Name] AND B.[boManufacturing Product Family Description] = R.[Manufacturing Product Family Description] AND" & _
"B.[boManufacturing Product Type Name] =  R.[Manufacturing Product Type Name] AND B.[boDerived End Customer Source Industry Segment Name] = R.[Derived End Customer Source Industry Segment Name] AND" & _
"B.[boRevenue Recognition Calendar Date MM DD YYYY Code] = R.[Revenue Recognition Calendar Date MM DD YYYY Code] AND B.[boRevUSD] = R.[Net Revenue US Dollar Amount] AND" & _
"B.[boTotal Cost Of Sales US Dollar Amount] =  R.[Total Cost Of Sales US Dollar Amount] AND B.[boGross Margin US Dollar Amount] = R.[Gross Margin US Dollar Amount] AND" & _
"B.[boGross Margin Percent] = R.[Gross Margin Percent] AND B.[boUnit Quantitiy] = R.[Unit Quantity] AND" & _
"B.[boBusiness Area CodeRef] = B3.[L3ID] AND B.[boProfit Center DescriptionRef] = P.[profID] AND B.[boLegacy Sales Order IdentifierRef] = L.[salID] AND" & _
"B.[boSold To AMID Level 2 IdentifierRef] = S.[soID] AND B.[boDerived End Customer_Ref] = D.[deID]);"

Mfg Kladdi

[DF6GL]

Hallo,


mindestens am Zeilen-Beginn der Folgezeilen fehlen Leerzeichen....

"   SELECT DISTINCT R.[Revenue Recognition Fiscal Ye..................
........
.........

[Kladdi]

Mhh ja das muss es gewesen sein...jedenfalls tritt der Fehler nicht mehr auf...Sondern wird von diesem abgelöst

Run-time error 3035:
System resource exceeded.

soviel dazu:

von der Syntax her sollte die Abfrage auch ordnungsgemäß laufen

mfg Kladdi

[ebs17]

Es kann auch sein, dass Jet-SQL etwas überfordert ist.

Persönlich wäre ich ob der Verwendung eines Mehrfelderschlüssels über 14 Felder (zwischen den Tabellen B und R) etwas skeptisch.

MfGA
ebs

[Kladdi]

Naja eine direkte feste Beziehung besteht zwischen B und R ja nicht...
R ist lediglich eine verlinkte Tabelle die importiert wird und im Anschluss an die Abfrage wieder gelöscht.

[Kladdi]

UPDATE:
Hab die Abfrage einfach mal gesplittet, immernoch derselbe Fehler aber immerhin konnt ich die Fehlerquelle ein bisschen einschränken.
Der sollte sich irgendwo hier befinden, ich vermute bei den Referenzen besteht das Problem. Aber wie gesagt, bin auch nur ein Neuling.

Code [Auswählen] Erweitern

zSQL = " INSERT INTO tblBooking ( [boRevUSD], [boTotal Cost Of Sales US Dollar Amount], [boGross Margin US Dollar Amount], [boGross Margin Percent], [boUnit Quantitiy], [boBusiness Area CodeRef], [boProfit Center DescriptionRef], [boLegacy Sales Order IdentifierRef], [boSold To AMID Level 2 IdentifierRef], [boDerived End Customer_Ref] )" & _
" SELECT DISTINCT R.[Net Revenue US Dollar Amount], R.[Total Cost Of Sales US Dollar Amount], R.[Gross Margin US Dollar Amount], R.[Gross Margin Percent], R.[Unit Quantity], B3.[L3ID], P.[profID], L.[salID], S.[soID], D.[deID]" & _
" FROM([tblBUL3] AS B3 INNER JOIN" & _
" ([tblProfitCenter] AS P INNER JOIN" & _
" ([tblSalesOrderIdentifierBilling] AS L INNER JOIN" & _
" ([tblSoldToAMIDLevel2ID] AS S INNER JOIN" & _
" ([tblDerivedEndCustomerAMIDLevel2Identifier] AS D INNER JOIN [Report] AS R" & _
" ON R.[Derived End Customer AMID Level 2 Identifier] = D.[deDerived End Customer AMID Level 2 Identifier])" & _
" ON R.[Sold To AMID Level 2 Identifier] = S.[soSold To AMID Level 2 Identifier])" & _
" ON R.[Legacy Sales Order Identifier] = L.[salLegacy Sales Order Identifier])" & _
" ON R.[Profit Center Description] = P.[profProfit Center Description])" & _
" ON R.[Business Area Code] = B3.[L3Business Area Code])" & _
" WHERE NOT Exists" & _
" (Select null FROM tblBooking AS B WHERE" & _
" B.[boRevUSD] = R.[Net Revenue US Dollar Amount] AND" & _
" B.[boTotal Cost Of Sales US Dollar Amount] =  R.[Total Cost Of Sales US Dollar Amount] AND B.[boGross Margin US Dollar Amount] = R.[Gross Margin US Dollar Amount] AND" & _
" B.[boGross Margin Percent] = R.[Gross Margin Percent] AND B.[boUnit Quantitiy] = R.[Unit Quantity] AND" & _
" B.[boBusiness Area CodeRef] = B3.[L3ID] AND B.[boProfit Center DescriptionRef] = P.[profID] AND B.[boLegacy Sales Order IdentifierRef] = L.[salID] AND" & _
" B.[boSold To AMID Level 2 IdentifierRef] = S.[soID] AND B.[boDerived End Customer_Ref] = D.[deID]);"

CurrentDb.Execute zSQL, dbFailOnError

[ebs17]

Irgendwann ist eine Anweisung nicht mehr allein sprechend, sondern nur im Zusammenhang mit ihrer Umgebung zu beurteilen, bei verminderter Abstrahierfähigkeit um so eher.

MfGA ebs