Database log error on AX 2009 with SQL Server 2008

  • Article

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