RecVersion-field and Check/synchronize
Mikael Sorensen
We have recently updated a number of Axapta installations with Axapta 3.0
Kernel Rollup 2 (KR2). Some of these databases are quite large (SQL Server
2000, 100 - 300 GB data).
When using the KR2-client a new field, "RecVersion", is added to every
table. Due to performance-reasons during upgrade this field is initialized
to "Null".
However when you afterwards perform the "Check/Synchronize" function from
"Administration, Periodic, SQL Administration", this will result in the
following error message:
Fixed Table Field (*=Index) Description
-------------------------------------------------------------------------------------------------
No [TABLENAME] RECVERSION Null attribute for field
differs - not modifiable
What would be the best approach to fix this error?
1. Run scripts on database
From SQL Server Query Analyzer you can run these two scripts to update each
table ("[TABLENAME]" should be replaced with the actual table name). E.g.:
UPDATE [TABLENAME] SET RecVersion = 1 WHERE RecVersion IS NULL;
ALTER TABLE [TABLENAME] ALTER COLUMN RecVersion INT NOT NULL;
(Use stored procedure sp_MSforeachtable to create update/alter statements
for each table)
I would expect a poor performance from this approach (which I guess is why
Microsoft did not do this in the first place), since I have a number of
tables with 100.000.000+ records. - Hence the update will result in a
massive number of page splits in the database.
or
2. Remove check from code on field "RecVersion".
You could remove the check from the class SqlScanMain (method handleFields).
Add "if (fieldDB.getName() != "RecVersion")" in line 20.
I do not like this approach, since important error message could be
suppressed.
Also having a field with a null value in one database (which allows
null-values in the table) will make it harder to transfer that record to
another database that does not allow null values in the same field (The
Recversion field will be initialized to "NOT NULL" if you create an empty
database and transfers data into that using DTS or a similar tool).
or
3. Something else?
What would be the better approach to fix the error?
Is OK to set all NULL values = 1?
The soultion that I have to come up with should not make it harder to
upgrade from Axapta 3.0 to 4.0 (I'm currently unsure how the BCP tool will
handle null values in an integer field that has the "not null" constraint
set).
I need to come up with a solution that can fix the error within a 48 hour
(weekend) period.
Any thoughts will be appreciated.
Best Regards
Mikael Sorensen
Helmut Wimmer
I have updated our test database (Oracle 9.2) to KR2 and the RecVersion
field was initialized with "1" automatically. (This was also the case in
the other 3 test migrations I did). Are you working with MSSQL?
Best regards
Helmut
http://axaptafreak.blogspot.com
Mikael Sorensen
I'm using SQL Server 2000 SP4. I have not tested it with Oracle.
I'm upgrading from Axapta 3.0 SP3.
Did you upgrade to KR1 before upgrading to KR2 (I didn't)? That could
explain that the RecVersion field is initialized to 1.
The initialization has changed for Oracle in KR2.
See this description from the "KR2 Fix list.htm" document:
15657
917430
Poor performance of kernel installed on Oracle based Axapta systems
post OCC (Optimistic Concurrency Checking) implementation.
· Problem
The process of creating RECVERSION columns on database upgrade took
very much time on Oracle database. The problem was with SQL statement used
for creating RECVERSION columns: it used "ALTER TABLE ... ADD RECVERSION INT
DEFAULT 1" statment causing Oracle to actually allocate space for new column
in all table rows and fill that column with default value.
· Solution
The fix is to use "ALTER TABLE ... ADD RECVERSION INT" statement so
that Oracle would create columns using default NULL value. This way space
wouldn't need to be allocated at once for all table rows.
From the description above it seems OK to do the update (RecVersion=1)
manually after the upgrade as the only reason for not doing it during the
upgrade is performance/diskspace.
Best Regards,
Mikael Sorensen
http://dynamicsdoodles.blogspot.com
"Helmut Wimmer" <n...@mail.com> wrote in message
news:uqENzzIr...@TK2MSFTNGP02.phx.gbl...
Helmut Wimmer
Well, I didn't upgrade to KR1, took the direct way to KR2. From what the
fixlist says, it now seems even more confusing to me. The fixlist says
it removed the "DEFAULT 1" but all my RecVersion fields were initialized
with 1????
Mikael Sorensen
I've realized that if you upgrade from SP3 to SP5 to SP5+KR2 the RecVersion
field is actually created by the SP5 client, not the KR2 client.
Therefore you should be able to reproduce RecVersion=NULL on Oracle if you
upgrade from SP2,SP3 or SP4 to KR2.
Best Regards,
Mikael
"Helmut Wimmer" <n...@mail.com> wrote in message
news:%23k6lgMu...@TK2MSFTNGP05.phx.gbl...
Helmut Wimmer
I'm getting more and more confused. I upgraded from Axapta 3.0 SP3
directly to KR2, so no SP5 or KR1 was involved. Still, my RecVersion
fields have been created with value 1 ?!?
Best regards
Helmut
Helmut Wimmer
Last thursday, I upgraded our Live system to KR2. (directly from 3.0SP3)
All RecVersion fields have the value 1. At the first start of the
application, it took about 5 minutes until Ax client was opened, I guess
that's when it created the RecVersion field. Our database is about 10GB.
Running on Oracle 9.2
Best regards
Helmut
http://axaptafreak.blogspot.com
Nyholm@discussions.microsoft.com Mats Nyholm
I got the same problem and wonder if you have solved the issue, I tried with
Axapta export and Import to new database but this is a time consuming
procedure with large database.
What was your solution??