Share via

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

Comments

  • Anonymous
    February 23, 2010
    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.