Database log error on AX 2009 with SQL Server 2008
On a database that is upgraded from AX 4.0 to AX 2009 running on SQL 2008 you may get following error while using database log:
The target table 'SYSDATABASELOG' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.. The SQL statement was:
INSERT INTO SYSDATABASELOG (DATA,USERNAME,DESCRIPTION,LOGTYPE,TABLE_,LOGRECID,CREATEDBY,CREATEDTRANSACTIONID, DATAAREAID,RECVERSION,RECID) OUTPUT INSERTED.CREATEDDATETIME VALUES (?,?,?,?,?,?,?,?,?,?,?)
To solve this issue you need to drop the onSYSDATABASELOGinsert trigger by executing this statement in SQL Server Management Studio:
USE <ax_database_name>;
GO
DROP TRIGGER [onSYSDATABASELOGinsert];
GO
You may also need to modify the default constraint for CreatedDateTime field to replace the default value ‘1900-01-01 00:00:00.000’ coming from AX 4.0 with ‘dateadd(millisecond, -datepart(millisecond,getutcdate()),getutcdate())’. To do this you can execute following code:
ALTER TABLE dbo.SYSDATABASELOG
DROP CONSTRAINT <constraint_name>
GO
ALTER TABLE dbo.SYSDATABASELOG ADD CONSTRAINT
<constraint_name> DEFAULT dateadd(millisecond, -datepart(millisecond,getutcdate()),getutcdate()) FOR CREATEDDATETIME
GO
Martin F