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


Comments (1)

  1. Michael Troelsen says:

    This a problem for all datetime fields which are converted from DAX 4.0 to DAX 2009.

    This small script will show, if you have the correct default for datetime fields.

    select tablename = t.name, columnname = c.name, conname = con.name, condefinition = con.definition

    from sys.columns c join sys.tables t

    on c.object_id = t.object_id

    join sys.default_constraints con

    on c.object_id = con.parent_object_id

    and  c.column_id = con.parent_column_id

    where c.system_type_id = 61

    order by t.name

    go

    One of the issues is, that createdDateTime is updated even though the AOT property is set.

    It is possible to create a SQL script which will alter the contraint on a specific database.

Skip to main content