Our support engineers have assembled the top recommended solutions for you.
Microsoft Dynamics AX 2012CRM Connector in Microsoft Dynamics AX 2012Financials Management in Microsoft Dynamics AX 2012Upgrading to Microsoft Dynamics AX 2012
Microsoft Dynamics AX 2009
Application Object Server (AOS)
Enterprise Portal and Role Centers
Inventory Costing in Microsoft Dynamics AX 2009
Invoice Settlements/Discounts/Reversals
SSRS and SSAS Integration
Workflow
Hi
Today we ran into a very strange issue in Ax 2009 when looking up records like this:
select Table1where Table1.createdDateTime == Table2.createdDateTimeA record that had the exact same CreatedDateTime was never returned. After analyzing the table I noticed that out of 400.000 records, 145 records looked like this in the CreatedDateTime field:2012-04-12 11:48:49.003
Since there were 3 milliseconds added to the value the query did not return the records. It took a while to understand this, since milliseconds are not presented in the table browser. They can only be seen in the query window of the SQL Server Management Studio.
After analyzing the table in the database I did find the reason, but not a solution for this.
The default value for CreatedDateTime field looks like this in the database:dateadd(millisecond, -datepart(millisecond,getutcdate()),getutcdate())This is how AX creates the fields.
What this default value is supposed to do is to return current date but strip the milliseconds.The problem with this is that getutcdate() gets called twice. That means that there is a slight possibility for the method to return two different values, especially if there is a high load on the database. Also, SQL Server has a precision of 3 milliseconds, so milliseconds will be rounded to 3.
So, what happened in the 145 records of my table is this:2012-05-30 11:20:30.567 - .564 = 2012-05-30 11:20:30.003
That is the reason. Now - does anyone have a solution?
/Jonas
I've somehow similar issue, I've a record in AX with utcDateTime value 2012-03-30 12:20:30 shown on the form, but when I'm running SSRS report with 2012-03-29 the record with utcDateTime value "2012-03-30 12:20:30" is shown in the result !!!
Any solutions/recommendation?
Mahmoud
That might be related to the timezone functionality of Ax. Dates are stored as UTC and timezone is applied when displaying on the screen.
Check msdn.microsoft.com/.../cc589382(v=ax.50).aspx
I have no experience when it comes to SSRS, though.
Hi Jonas,
We too found hundreds of records with exactly .003 milliseconds in some tables and thousands in few tables
This also causes missing those records in filtering with createdatetime/modifieddatetime fields with table browser & grids in AX
Using “convert(datetime, convert(varchar, getutcdate(), 120))” instead of “dateadd(millisecond, -datepart(millisecond,getutcdate()),getutcdate())” may solve the issue
This might need to be fixed by Microsoft
"Nice" to see that I am not the only one. Currently I have no more support tickets with Microsoft, so I haven't had the possibility to report this - since you never know if they will charge you or not...
I have sent this information to a person within the Microsoft support team by mail, but I have not heard anything from him yet. So if you have the possibility to report this, please do.
Since these two fields “createddatetime, modifieddatetime with associated default value/formula” are created at low-level execution between AX and SQL-Server during AOT-Tables Sync/Compilation Process
This issue cannot be resolved by customizatin in AX
Until it fixed by Microsoft, I suggest temporary workaround for this issue is to use direct SQL update before executing reports/queries in AX
update tablename
set createddatetime = convert(datetime, convert(varchar, createddatetime, 120)) where DATEPART(ms, createddatetime)>0
set modifieddatetime = convert(datetime, convert(varchar, modifieddatetime, 120)) where DATEPART(ms, modifieddatetime)>0
I recommend taking full database backup before executing sql-update and using separate sql-update statements to update createddatetime and createddatetime fields for quick/performance on large tables