I'm installing my first AX 2012 (CU1) @ a customer site and stumbled on problem processing the OLAP cubes.
I've followed the error to it's source and appearantly it's a view that won't work ( Data convertion error from nvarchar to string)
In the view PRODCALCTRANSEXPANDED, there is fixed date (N'24/10/2011') that should be converted to DateTime.
First of all, because I'm on a European server with DD/MM/YYYY settings, it writes the date the same in T-SQL. But in T-SQL, it's always MM/DD/YYYY. This is a bug I presume. Setting my server to US dateformat,synschronising the view and the error is gone.
But then I tried to understand the logic of this view, the date is updated every time you do a synchronise of that view. Meaning if you don't synchronise very often and Prodstatus is not 5 or 7, you production-order is not delayed ( DELAYED = 0 ). Why doesn't they use GETDATE() or am I missing something here?
The same for view PRODROUTETRANSEXPANDED, same bug and some illogical use of a fixed date.
This is the piece of code that holds that fixed date in the first view.
CAST((CASE WHEN (((T2.PRODSTATUS) != (5)) AND ((T2.PRODSTATUS) != (7))) AND (T2.DLVDATE < N'24/10/2011') THEN 1 WHEN ((T2.PRODSTATUS = 5) OR (T2.PRODSTATUS = 7)) AND (T2.DLVDATE < T2.FINISHEDDATE) THEN 1 WHEN ((T2.PRODSTATUS = 5) OR (T2.PRODSTATUS = 7)) AND (T2.DLVDATE >= T2.FINISHEDDATE) THEN 0 ELSE NULL END) AS INT) AS DELAYED
T2 is PRODTABLE in this piece of T-SQL.
Both issues are fixed in AX2012 R2, namely in class method ProdTableExpandedColumn::delayed(), which is used by view ProdCalcTransExpanded. The code which resulted in hardcoding current date with date format derived from local machine:
was replaced with proper:
As the result, the view (after synchronization) contains:
CAST((CASE WHEN (((T2.PRODSTATUS) != (5)) AND ((T2.PRODSTATUS) != (7))) AND (T2.DLVDATE < GETUTCDATE())
Hope that helps.
Not sure why they hard coded a date in there, but maybe you can wrap that part with some additional SQL just to make sure the type remains a datetime (assuming DLVDATE is same type as TRANSDATE).
Depending on what version of SQL you are running, perhaps you can get inspired from here:
Or here: msdn.microsoft.com/.../ms187928.aspx (look at the first community comment at the bottom).
Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no
Did you get a permanent solution to this issue.
We found this "synchronisation" date is also still used in the view created in the AX2012 R2 version.