Reply To: Getting error while upgrading the AX database from CU6 to CU7

#20982

Locus IT
Participant

Hello Andrew,

Background: Date-Effective tables are a big deal. They allow you to track the history of when a record is valid for example. For example, if a customer changes address, you may want to be able to record that a customer lived in Oklahoma from June 2002 till June 2006 and Texas from 2006 till current. You can see why these are so important.

But to make this happen, fundamental changes needed to be introduced in tables. The problem with this is that AX populates the record dates as January 1st 1900 for records that existed before time recording was introduced. This leads to errors as the SQL indexes can’t create because of these duplicate values.

Solution: since 1900 is just a dummy value, keep on populating the date-effective tables with dummy values for historical data that existed before the real data starts coming in. You just need to tell everyone that anything in 1900 should not be assumed to be a real data year. To do this, I’m going to use a classic sql cursor statement. Here is an example of a script to resolve the errors if you need to copy and paste

Declare
@datevalue
datetime,
@modifieddatetime datetime,
@basedate datetime

SET
@basedate
=
’01/01/1900′

Declare
resolve_axupgrade
Cursor
For

Select
ModifiedDateTime
— replace this column with the effective date column

from
HCMPositionForecastLastModified
— replace this table with the name of the one causing sync errors

for
update
of
ModifiedDateTime

open
resolve_axupgrade

Fetch
resolve_axupgrade

into
@datevalue

WHILE
@@FETCH_STATUS
= 0

BEGIN

update
dbo.HCMPOSITIONFORECASTLASTMODIFIED

set
MODIFIEDDATETIME
=
@modifieddatetime

where
current
of
resolve_axupgrade

SET
@basedate
=
DATEADD(day,1,@basedate)

SET
@modifieddatetime
=
@basedate

FETCH
resolve_axupgrade
INTO
@datevalue

END

Close
resolve_axupgrade

DEALLOCATE
resolve_axupgrade

Skip to toolbar