NAV 2015 upgrade dimension update query blocked

davmac1

I have run into a problem for the first time on the dimension update in the upgrade process.
This is my first time using CU8.
The code that builds the query to build the new dimensions works fine, but when it executes the query it hangs - running from the developer.
I ran the blocking transaction report and it only shows one other transaction - the development environment - with nothing blocked - but it is doing the blocking!

The sql statement itself is ugly - I pasted it below.
Anyone run into this before?

(I also tried running as administrator from the admin shell and it does not get hung up - but has another strange problem - not getting a record that does not exist, then failing on the insert because it already exists..)




select DISTINCT LED., LED.[Journal Template Name], LED.[Journal Batch Name], LED.[Journal Line No_], LED.[Allocation Line No_], LED.[CBG Statement No_], 0 as DimSetID,D1.[Dimension Value Code] as DimVal1,D2.[Dimension Value Code] as DimVal2,D3.[Dimension Value Code] as DimVal3,D4.[Dimension Value Code] as DimVal4,D5.[Dimension Value Code] as DimVal5,D6.[Dimension Value Code] as DimVal6,D7.[Dimension Value Code] as DimVal7,D8.[Dimension Value Code] as DimVal8,D9.[Dimension Value Code] as DimVal9,D10.[Dimension Value Code] as DimVal10
into [General Company long Name$upgrade356_Dim]
from [General Company long Name$Journal Line Dimension] as LED
left outer join [General Company long Name$Journal Line Dimension] as D1
on LED. = D1. and LED.[Journal Template Name] = D1.[Journal Template Name] and LED.[Journal Batch Name] = D1.[Journal Batch Name] and LED.[Journal Line No_] = D1.[Journal Line No_] and LED.[Allocation Line No_] = D1.[Allocation Line No_] and LED.[CBG Statement No_] = D1.[CBG Statement No_] and D1.[Dimension Code] = @Dim1
left outer join [General Company long Name$Journal Line Dimension] as D2 on LED. = D2. and LED.[Journal Template Name] = D2.[Journal Template Name] and LED.[Journal Batch Name] = D2.[Journal Batch Name] and LED.[Journal Line No_] = D2.[Journal Line No_] and LED.[Allocation Line No_] = D2.[Allocation Line No_] and LED.[CBG Statement No_] = D2.[CBG Statement No_] and D2.[Dimension Code] = @Dim2
left outer join [General Company long Name$Journal Line Dimension] as D3 on LED. = D3. and LED.[Journal Template Name] = D3.[Journal Template Name] and LED.[Journal Batch Name] = D3.[Journal Batch Name] and LED.[Journal Line No_] = D3.[Journal Line No_] and LED.[Allocation Line No_] = D3.[Allocation Line No_] and LED.[CBG Statement No_] = D3.[CBG Statement No_] and D3.[Dimension Code] = @Dim3
left outer join [General Company long Name$Journal Line Dimension] as D4 on LED. = D4. and LED.[Journal Template Name] = D4.[Journal Template Name] and LED.[Journal Batch Name] = D4.[Journal Batch Name] and LED.[Journal Line No_] = D4.[Journal Line No_] and LED.[Allocation Line No_] = D4.[Allocation Line No_] and LED.[CBG Statement No_] = D4.[CBG Statement No_] and D4.[Dimension Code] = @Dim4
left outer join [General Company long Name$Journal Line Dimension] as D5 on LED. = D5. and LED.[Journal Template Name] = D5.[Journal Template Name] and LED.[Journal Batch Name] = D5.[Journal Batch Name] and LED.[Journal Line No_] = D5.[Journal Line No_] and LED.[Allocation Line No_] = D5.[Allocation Line No_] and LED.[CBG Statement No_] = D5.[CBG Statement No_] and D5.[Dimension Code] = @Dim5
left outer join [General Company long Name$Journal Line Dimension] as D6 on LED. = D6. and LED.[Journal Template Name] = D6.[Journal Template Name] and LED.[Journal Batch Name] = D6.[Journal Batch Name] and LED.[Journal Line No_] = D6.[Journal Line No_] and LED.[Allocation Line No_] = D6.[Allocation Line No_] and LED.[CBG Statement No_] = D6.[CBG Statement No_] and D6.[Dimension Code] = @Dim6

mohana_cse06

Hi David,

May I know which localization is it?

davmac1

NL - Netherlands

mohana_cse06

Please add COMMIT at the end of UpdateCBGStatementDim and UpdateCBGStatementLineDim functions in 104052 Codeunit and try

davmac1

Thanks - that makes it work.
I am disappointed they have not fixed the problem yet.

Plus they also have the problem - when there is any data in table 11 milion, the dimension conversion fails - just like it does in the NA edition if there is any data in the bank deposit header.