Переделываем RecId в таблицах
Для исправления проблемы Произошла ошибка при получении нового значения RecId для таблицы.
Нюанс использования:
Использовал скрипт Upd_RecID.sql.txt:
P.S. Ax 2009 Sp1 RU5
Цитата:
Не удается распределить номера номерной серии для TableId:%1, CurrentNextVal:%2, NewNextVal:%3
Произошла ошибка при получении нового значения RecId для таблицы.
Произошла ошибка при получении нового значения RecId для таблицы.
- AOS надо выключить.
- Не забыть сделать Full BackUP БД.
- Можно запускать скрипт
Использовал скрипт Upd_RecID.sql.txt:
PHP код:
USE DynamicsAx2009;
DECLARE @recid bigint
DECLARE @recIdCustTable bigint
DECLARE @recIdVendTable bigint
DECLARE @recIdCustBankAccount bigint
DECLARE @recIdVendBankAccount bigint
DECLARE @DataAreaID nvarchar(4)
DECLARE @AccountNUM nvarchar(20)
DECLARE @ACCOUNTID nvarchar(10)
DECLARE @CUSTACCOUNT nvarchar(20)
DECLARE @VENDACCOUNT nvarchar(20)
SET @recIdCustTable = 7349605052;
SET @recIdCustBankAccount = 7173596050;
SET @recIdVendTable = 8709438849;
SET @recIdVendBankAccount = 6691633308;
DELETE FROM CUSTTABLE
WHERE DATAAREAID in ('BR', 'CB','L01','L02', 'DAT')
DELETE FROM VendTable
WHERE DATAAREAID in ('BR', 'CB','L01','L02', 'DAT')
DELETE FROM CustBankAccount
WHERE DATAAREAID in ('BR', 'CB','L01','L02', 'DAT')
DELETE FROM VendBankAccount
WHERE DATAAREAID in ('BR', 'CB','L01','L02', 'DAT')
-------------------------------CustTable Start----------------------------------
DECLARE CustTable_Cursor CURSOR FOR
SELECT ACCOUNTNUM, DATAAREAID, RecID FROM dbo.CustTable
WHERE DataAreaID in ('j01','BSRF')
ORDER BY DataAreaID;
OPEN CustTable_Cursor;
FETCH NEXT FROM CustTable_Cursor INTO @AccountNUM, @DataAreaID, @recid;
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@recid > @recIdCustTable OR @recid <0)
BEGIN
SET @recIdCustTable = @recIdCustTable +1;
update CustTable
SET RECID = @recIdCustTable
WHERE DataAreaID = @DataAreaID AND AccountNUM = @AccountNUM;
/*
select RecID as [newRecId],@recid as [oldRecid], @AccountNUM, @DataAreaID
from Custtable
WHERE DataAreaID = @DataAreaID AND AccountNUM = @AccountNUM;
*/
END
FETCH NEXT FROM CustTable_Cursor INTO @AccountNUM, @DataAreaID, @recid;
END;
CLOSE CustTable_Cursor;
DEALLOCATE CustTable_Cursor;
UPDATE [SYSTEMSEQUENCES]
SET NEXTVAL = @recIdCustTable + 1
Where ID = -1 AND TABID = 77 --CUSTTABLE
-------------------------------CustTable End----------------------------------
-------------------------------VendTable Start----------------------------------
DECLARE VendTable_Cursor CURSOR FOR
SELECT ACCOUNTNUM, DATAAREAID, RecID FROM dbo.VendTable
WHERE DataAreaID in ('j01','BSRF')
ORDER BY DataAreaID;
OPEN VendTable_Cursor;
FETCH NEXT FROM VendTable_Cursor INTO @AccountNUM, @DataAreaID, @recid;
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@recid > @recIdVendTable OR @recid <0)
BEGIN
SET @recIdVendTable = @recIdVendTable +1;
update VendTable
SET RECID = @recIdVendTable
WHERE DataAreaID = @DataAreaID AND AccountNUM = @AccountNUM;
/*
select RecID as [newRecId],@recid as [oldRecid], @AccountNUM, @DataAreaID
from VendTable
WHERE DataAreaID = @DataAreaID AND AccountNUM = @AccountNUM;
*/
END
FETCH NEXT FROM VendTable_Cursor INTO @AccountNUM, @DataAreaID, @recid;
END;
CLOSE VendTable_Cursor;
DEALLOCATE VendTable_Cursor;
UPDATE [SYSTEMSEQUENCES]
SET NEXTVAL = @recIdVendTable + 1
Where ID = -1 AND TABID = 505 --VENDTABLE
-------------------------------VendTable End----------------------------------
-------------------------------CustBankAccount Start----------------------------------
DECLARE CustBankAccount_Cursor CURSOR FOR
SELECT DATAAREAID, CUSTACCOUNT, ACCOUNTID, RecID FROM dbo.CustBankAccount
WHERE DataAreaID in ('j01','BSRF')
ORDER BY DataAreaID;
OPEN CustBankAccount_Cursor;
FETCH NEXT FROM CustBankAccount_Cursor INTO @DATAAREAID, @CUSTACCOUNT, @ACCOUNTID, @recid;
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@recid > @recIdCustBankAccount OR @recid <0)
BEGIN
SET @recIdCustBankAccount = ( CAST(@recIdCustBankAccount +1 AS bigint) );
update CustBankAccount
SET RECID = @recIdCustBankAccount
WHERE DataAreaID = @DataAreaID AND CUSTACCOUNT=@CUSTACCOUNT AND @ACCOUNTID = @ACCOUNTID;
END
FETCH NEXT FROM CustBankAccount_Cursor INTO @DATAAREAID, @CUSTACCOUNT, @ACCOUNTID, @recid;
END;
CLOSE CustBankAccount_Cursor;
DEALLOCATE CustBankAccount_Cursor;
UPDATE [SYSTEMSEQUENCES]
SET NEXTVAL = @recIdCustBankAccount + 1
Where ID = -1 AND TABID = 50 --CUSTBANKACCOUNT
-------------------------------CustBankAccount End----------------------------------
-------------------------------VendBankAccount Start----------------------------------
DECLARE VendBankAccount_Cursor CURSOR FOR
SELECT DATAAREAID, VENDACCOUNT, ACCOUNTID, RecID FROM dbo.VendBankAccount
WHERE DataAreaID in ('j01','BSRF')
ORDER BY DataAreaID;
OPEN VendBankAccount_Cursor;
FETCH NEXT FROM VendBankAccount_Cursor INTO @DATAAREAID, @VENDACCOUNT, @ACCOUNTID, @recid;
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@recid > @recIdVendBankAccount OR @recid <0)
BEGIN
SET @recIdVendBankAccount = ( CAST(@recIdVendBankAccount +1 AS bigint) );
update VendBankAccount
SET RECID = @recIdVendBankAccount
WHERE DataAreaID = @DataAreaID AND VENDACCOUNT=@VENDACCOUNT AND @ACCOUNTID = @ACCOUNTID;
END
FETCH NEXT FROM VendBankAccount_Cursor INTO @DATAAREAID, @VENDACCOUNT, @ACCOUNTID, @recid;
END;
CLOSE VendBankAccount_Cursor;
DEALLOCATE VendBankAccount_Cursor;
UPDATE [SYSTEMSEQUENCES]
SET NEXTVAL = @recIdVendBankAccount + 1
Where ID = -1 AND TABID = 489 --VENDBANKACCOUNT
-------------------------------VendBankAccount End----------------------------------
SELECT *
FROM [SYSTEMSEQUENCES]
where ID = -1 AND TABID in (77, 505, 50, 489)
order by tabid, NEXTVAL desc
select MAX(recid), 'CUSTBANKACCOUNT- 50 ' from CUSTBANKACCOUNT -- 50
select MAX(recid), 'custtable- 77' from custtable -- 77
select MAX(recid), 'VENDBANKACCOUNT- 489' from VENDBANKACCOUNT -- 489
select MAX(recid), 'vendtable- 505' from vendtable -- 505
Всего комментариев 0
