Cannot create a record in Database log (sysDataBaseLog)

Encountered this error recently and this had bought the complete environment to a stand still as the implementation was making extensive use of database logs.

We did a full compile of the application but that did not help. Ultimately had to resolve to code debugging. I found this issue to be that of a duplicate RECID being generated for a record insertion into the sysDatabaseLog table

As we know that the seed value for RecID generation for a table is done using SystemSequences table. I hence found the max(RecID) from the SysDatabaseLog table and updated this value (incremented by 1) into the NextVal column of the systemsequences.


PS: Any changes to the systemSequences Table were only visible to the AOS post a restart.

Batch file to recompile in multiple AOS scenario

Batch file

	net stop AOS60$01
	net stop AOS60$02
	net start AOS60$01
	cd C:\Program Files\Microsoft Dynamics AX\60\Server\AOSInstance1\bin
	axbuild xppcompileall /s=01
	net stop AOS60$01
	net start AOS60$01
	@echo "Please log into AOS1 and do a full CIL compile. Before resuming the batch (please do not press a key till the CIL compile is done."
	pause
	net stop AOS60$01
	net stop AOS60$02
	net start AOS60$01
	net start AOS60$02

view raw bat hosted with ❤ by GitHub

Manage a cross server transaction without configuring MSDTC

Usually managing a transaction across two servers would require a distributed transaction controller being setup between the two server.

Below is a scenario where this is managed without a MSTDC configution.

	UserConnection con = new Connection();
	Statement stmt = con.createStatement();
	Statement stmtUpdate = con.createStatement();
	ResultSet r;
	str sql;
	RPSmartParameters parameters;
	RPSmartInvoices rpSmartInvoicesTmp, rpSmartInvoicesInsert;
	Exception sysException;
	str errorText;
	int startLine, currentLine;
	SysInfoLogLevel prevLevel = infolog.infologLevel();
	SqlStatementExecutePermission perm, perm2;
	select firstOnly parameters;
	sql = strfmt('SELECT * FROM %1 where isnull( isAXUpdated, 0 ) = 0', parameters.CustInvoiceTable);
	perm = new SqlStatementExecutePermission(sql);
	perm.assert();
	try //main try block there are 2 try blocks nested below this to ensure all transactions execute consistently
	{
	rpSmartInvoicesTmp.setTmp();
	startLine = Global::infologLine();
	try
	{
	r = stmt.executeQuery(sql);
	while (r.next())
	{
	rpSmartInvoicesTmp.initValue();
	rpSmartInvoicesTmp.InvDetailsID = r.getString(1);
	rpSmartInvoicesTmp.CustCode = r.getString(2);
	rpSmartInvoicesTmp.Currency = r.getString(3);
	rpSmartInvoicesTmp.Amount = r.getReal(4);
	rpSmartInvoicesTmp.InvoiceNo = r.getString(5);
	rpSmartInvoicesTmp.InvoiceDate = r.getDate(6);
	rpSmartInvoicesTmp.Remarks = r.getString(7);
	rpSmartInvoicesTmp.InvoiceStartDate = r.getDate(8);
	rpSmartInvoicesTmp.InvoiceEndDate = r.getDate(9);
	rpSmartInvoicesTmp.DueDate = r.getDate(10);
	rpSmartInvoicesTmp.RevenueCategory = r.getString(11);
	rpSmartInvoicesTmp.Unit = r.getString(12);
	rpSmartInvoicesTmp.Precinct = r.getString(13);
	rpSmartInvoicesTmp.PaymentVoucher = r.getReal(14);
	rpSmartInvoicesTmp.insert();
	}
	}
	catch (exception::Error)
	{
	for (currentLine = startLine + 1; currentLine <= global::infologLine(); currentLine++)
	{
	errorText += infolog.text(currentLine);
	}
	infolog.clear();
	throw Exception::Error;
	}
	CodeAccessPermission::revertAssert();
	//flag the imported records shoudl only happen if there is no error above
	try
	{
	sql = strfmt('update A set A.isAXUpdated = 1, A.IntegrationDate = getdate() FROM %1 A', parameters.CustInvoiceTable);
	perm2 = new SqlStatementExecutePermission(sql);
	perm2.assert();
	stmtUpdate.executeUpdate(sql);
	}
	catch (exception::Error)
	{
	for (currentLine = startLine + 1; currentLine <= global::infologLine(); currentLine++)
	{
	errorText += infolog.text(currentLine);
	}
	infolog.clear();
	throw Exception::Error;
	}
	CodeAccessPermission::revertAssert();
	//move data to live table if the above update was successful
	while select rpSmartInvoicesTmp
	{
	rpSmartInvoicesInsert.data ( rpSmartInvoicesTmp.data() );
	rpSmartInvoicesInsert.insert();
	}
	}
	catch (Exception::Error)
	{
	for (currentLine = startLine + 1; currentLine <= global::infologLine(); currentLine++)
	{
	errorText += infolog.text(currentLine);
	}
	infolog.clear();
	info( errorText );
	throw Exception::Error;

view raw xpo hosted with ❤ by GitHub