Dynamics NAV Data Export and SQL timestamp field

In one project we have created to a customer required us to do some heavy integrations to external logistics partners. This almost always requires some kind of marking records with some fields that include information to track whether the record has been sent to partner or not after last change. There is couple of ways to reach this. My normal approach is to keep data synchronized between partners has been to use a boolean field "Changed after last transfer", and possibly one or more date/time fields to keep track when the last transfer has been done. When "important" fields are changed, boolean gets set, and then we know the record has to be resent to the partner. Some developers use date field "Last Changed Date" and keep track in a setup table when the last transfer occurred, and then just filter out the records that have not been changed after last transfer. There is also some other ways to achieve the same, but I think these are the most used patterns.

This pattern is called timestamping, and it is quite a standard way to handle synchronizations.

SQL also keeps internally timestamp of changed records, but that field has earlier not been visible to NAV. In NAV 2016 Microsoft added SQL timestamp handling property to NAV. Microsoft engineer Lars Lohndorf-Larsen blogged about this in April 2016: https://blogs.msdn.microsoft.com/nav/2016/04/06/new-timestamp-fields-in-microsoft-dynamics-nav-2016/

I have been since using this successfully, and it is working nicely when you have to synchronize records if any change to any field in the record has been occurred. If you need to synchronize only if important fields have been updated, you still have to build the functionality described earlier.

Anyways, to the subject! Last October, I had to update customers test database with live data for some integration testing. I started to export the data as I have done like hundreds of times, but alas! The export crashed the client and I could not understand what is going on. This happened in NAV 2016 CU08 build 46045.

Additionally I got multiple event viewer logs telling that something has gone terribly wrong (below only the interesting part):

Log Name:      Application
Source:        MicrosoftDynamicsNAVClient
Date:          23.10.2016 15:54:22
Event ID:      0
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      xxx.xxxx.innofactor.com
Type: System.InvalidCastException
Message: Unable to cast object of type 'System.DateTime' to type 'System.String'.
     at Microsoft.Dynamics.Nav.Client.WinClient.ExceptionHandler.<>c__DisplayClass6.<DoRethrowWithCatchException>b__4()
     at Microsoft.Dynamics.Nav.Client.WinClient.NavClientErrorHandler.ExecuteAndCatchExceptions(Func`1 execute)
     at Microsoft.Dynamics.Nav.Client.WinClient.ExceptionHandler.DoExecute(Func`1 execute)
     at Microsoft.Dynamics.Nav.Client.WinClient.ExceptionHandler.DoRethrowWithCatchException(Exception exception, Boolean designMode)
Source: Microsoft.Dynamics.Nav.Client.WinClient
HResult: -2147467262
Type: System.InvalidCastException
Message: Unable to cast object of type 'System.DateTime' to type 'System.String'.
     at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
     at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
     at Microsoft.Dynamics.Framework.UI.WinForms.WinFormsDispatcher.Invoke(Action method)
     at Microsoft.Dynamics.Framework.UI.LogicalDispatcher.ExecuteInFrameInternal(LogicalDispatcherFrame frame, Action executeOnFrame)
     at Microsoft.Dynamics.Framework.UI.LogicalDispatcher.ExecuteInFrame(LogicalDispatcherFrame frame, Action executeOnFrame)
     at Microsoft.Dynamics.Framework.UI.LogicalForm.ShowDialogAndEnsureModality(LogicalForm ownerForm)
     at Microsoft.Dynamics.Nav.Client.Actions.NavOpenTaskPageAction.ShowForm(LogicalForm childForm, LogicalForm parentForm, UISession uiSession, FormState formState)
     at Microsoft.Dynamics.Nav.Client.Actions.NavOpenTaskPageAction.InvokeCore(LogicalControl logicalControl, Object state, UISession uiSession)
     at Microsoft.Dynamics.Framework.UI.LogicalAction.InvokeCoreWithErrorHandling(LogicalControl logicalControl, Object state, UISession uiSession)
     at Microsoft.Dynamics.Framework.UI.LogicalAction.DoInvoke(LogicalControl logicalControl, UISession uiSession)
     at Microsoft.Dynamics.Framework.UI.UX2006.NavigationService.NavigateToNode(LogicalControl logicalControl, NavigationNode node)
     at Microsoft.Dynamics.Framework.UI.UX2006.WinForms.PageSearchBar.WndProc(Message& m)
     at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
Source: System.Data
HResult: -2147467262

What is this, "Unable to cast object of type 'System.DateTime' to type 'System.String'" ???

I searched the net (obviously), but found no information about the problem. I then started to narrow down the possible causes and finally ended up updating the client to CU15 (yes, January... this was a tricky one and I could not concentrate on it, since SQL backup always works...) to see if the fixes done in CU12 and CU15 have any effect. Turned out that no, CU15 did the same.

Now I searched a bit more, and with help of the previously mentioned Lars Lohndorf-Larsen (thank you!) I managed to find out that the problem was, surprise surprise, in the BigInteger SQL timestamp I added for integrations to use.

After deleting the column, export works again like a charm. So, now I am waiting MS to fix this issue in later releases.

Hope someone finds this information useful,



**EDIT 2017-02-07: added better description about timestamping pattern**

Comment List