Fixing RecId Sequences

[Luegisdorf]

Hi there

In AX 4.0 we have the situtation, that the nextval systemsequences for
several tables are too low.
However, we don't know how this situation has been raised, but we want to
fix it now.

We're working with MS-SQL and therefore I thought, a SQL-Script would be
fine. Unfortunately I'm not very familiar in T-SQL, so I tried to do
something like this; but that was not really fine:

update systemsequences set nextval = (exec('select max (RECID) from ' +
(select [NAME] from sqldictionary where sqldictionary.fieldid = 0 and
sqldictionary.tabid = systemsequences.tabid top 1))) + 1 where
systemsequences.Name = 'SEQNO'

Well; may be you know how the right expression?

Thanky you in advance and
best regards
Patrick

[Luegisdorf]

Hi there

have now elaborated a working SQL-Script:

--CODE BEGIN

/*
This script check and corrects RecID sequences in MS Dynamics AX 4.0
(probably later versions too ...)
It's recommended to backup your database before run!

Author: Opus Solution AG (Switzerland), pk
Date: August 2008
*/

print '** BEGIN **'
declare @tabid int, @nextval bigint, @lastUsed bigint, @sql NVARCHAR(4000),
@msgName varchar(250), @tablename varchar(250)
DECLARE loopTrough CURSOR FOR
select TABID, NEXTVAL from systemsequences where systemsequences.Name =
'SEQNO' and
systemsequences.dataareaid = 'dat' and
systemsequences.TabId > 0

open loopTrough

FETCH next FROM loopTrough INTO @TABID, @nextval
WHILE @@FETCH_STATUS = 0
BEGIN
exec sp_GetMaxRecID @TABID, @nextval

set @tablename = isnull((select [SQLNAME] from sqldictionary where
sqldictionary.fieldid = 0 and sqldictionary.TableId = @tabid), '')

if @tablename <> ''
set @msgName = @tablename + '(No. ' + convert(varchar(20), @tabid) + ')'
else
set @msgName = '(No. ' + convert(varchar(20), @tabid) + ')'

print 'Analyze ' + @msgName + ' ...'

IF @tablename <> '' and
EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME=@tablename)
begin

set @sql = N'SELECT @ret = MAX(RECID) FROM ' + @tablename
EXEC sp_executesql @sql, N'@ret bigint OUTPUT', @lastUsed OUTPUT

if (@nextVal < @lastUsed)
begin
print '> have to be corrected (next RecId: ' + convert(varchar(250),
@nextVal) + ' < max RecID: ' + convert(varchar(250), @lastUsed) + ')'
update systemsequences set nextval = (@lastUsed + 1) where
systemsequences.Name = 'SEQNO' and
systemsequences.tabId = @tabid and
systemsequences.dataareaid = 'dat'
print '> corrected; next RecId: ' + convert(varchar(250), @lastUsed + 1)
end
end
else
begin
print @msgName + ' not in SQLDictionary or Table not in DataBase'
end

FETCH next FROM loopTrough INTO @TABID, @nextval
END
close loopTrough
DEALLOCATE loopTrough
print '** Finish **'

--CODE END

May be, you can use it too.

Best regards
Patrick

[Luegisdorf]

Well, there was a little bug: here the corrected version:

-- CODEBEGIN
/*
This script check and corrects RecID sequences in MS Dynamics AX 4.0
(probably later versions too ...)
It's recommended to backup your database before run!

Author: Opus Solution AG (Switzerland), pk
Date: August 2008
*/

print '** BEGIN **'
declare @tabid int, @nextval bigint, @lastUsed bigint, @sql NVARCHAR(4000),
@msgName varchar(250), @tablename varchar(250)
DECLARE loopTrough CURSOR FOR
select TABID, NEXTVAL from systemsequences where systemsequences.Name =
'SEQNO' and
systemsequences.dataareaid = 'dat' and
systemsequences.TabId > 0

open loopTrough

FETCH next FROM loopTrough INTO @TABID, @nextval
WHILE @@FETCH_STATUS = 0
BEGIN

[Richard]

thanks for a very helpful script!! It helped us solve the problems we where
struggling hard with.

Best Regards / Richard