We recently received some cases related to an unexpected behavior with the Microsoft Dynamics NAV Debugger when the Bulk Insert feature is enabled.
The Bulk Insert functionality is duly described in the MSDN Library at http://msdn.microsoft.com/en-us/library/dd355341(v=nav.70).aspx. Here you can see that Microsoft Dynamics NAV automatically buffers inserts in order to send them to Microsoft SQL Server at one time. In this way, performance is improved since the number of server calls is reduced.
So far, so good.
But when you enable debugging in a scenario that falls into a violation of a Primary Key in SQL Server due to several concurrent INSERT calls, you might find that the Microsoft Dynamics NAV Debugger will not stop in the exact INSERT statement but, due to delayed Bulk Insert, in a position right after the INSERT AL Statement sequence.
For example, create a codeunit with the following code:
IF NOT CONFIRM(‘Please enable debugging, and then click OK’) THEN
GLEntry.”Entry No.” = 111;
MESSAGE(‘Insert GL Entry record’);
MESSAGE(‘Try to insert duplicate GL Entry record’);
MESSAGE(‘Do something. Such as FINDLAST’);
ERROR(‘End of scenario’);
Run the codeunit, and then, when prompted, enable debugging against the current session.
Normally, you would expect to have debugger stop exactly in the second INSERT statement due to the violation of Primary Key (duplicate) but in this scenario, the debugger stops in the FINDLAST statement. This behavior is due to Bulk Insert feature that would delay the INSERT and therefore the error message will be catch only after the last INSERT statement.
Microsoft Dynamics NAV 2013 R2 introduced a configuration parameter in the CustomSettings.config file for the Microsoft Dynamics NAV Server service:
Specifies whether to enable the SQL Buffered Insert functionality to buffer rows that are being inserted into a database table.
When this parameter is enabled, up to 5 rows will be buffered in the table queue before they are inserted into the table.
To optimize performance in a production environment, you should set this parameter to TRUE (enabled). In a test environment,
you can set this parameter to FALSE (disabled) to debug SQL insert failures.
<add key=”BufferedInsertEnabled” value=”True” />
With a brief explanation on how to turn on and off this feature. If you simply change this parameter from True to False (in the Microsoft Dynamics NAV Server Administration tool, on the General tab, clear the Enable Buffered Insert field), restart the service, and then run the same codeunit as described above. Now the debugger stops exactly in the violation of Primary Key in the duplicate attempt (INSERT).
The aforementioned behavior also reproduces with Microsoft Dynamics NAV 2009 R2 and Microsoft Dynamics NAV 2013. With these version, you cannot trigger on and off the Bulk Insert feature, but you can apply a workaround if you would like to fall back to the classic INSERT instead of the automatic enablement of the Bulk Insert feature.
The workaround is pretty simple: temporarily violate one of the following constraint for Bulk Insert that would fall back to classic INSERT into SQL Server. The easiest and most feasible one is to simply add a BLOB field in the Table structure (just add the field, you do not need to populate this). Save and Compile the table and magically you will not have the Bulk Insert effect when you need to debug. Of course, we do recommend to perform this action in a staging or test environment.
These postings are provided “AS IS” with no warranties and confer no rights. You assume all risk for your use.
Duilio Tacconi Microsoft Dynamics Italy
Microsoft Customer Service and Support (CSS) EMEA
Special thanks to Nacho Galajares, Antonio Cerrolaza from tipsa.net and Tobias Fenster, Stefan Konrad from infoma.de