Saturday, March 16, 2019

Troubleshooting a Dynamics GP SQL Posting Error

By Steve Endow

I recently had a call with my friend Windi Epperson to troubleshoot an odd SQL error that was occurring when posting a Dynamics GP batch.



Here is a video discussing the error and our journey to identify the cause and resolve the issue.





When posting a GL batch in Dynamics GP, the customer was getting this error:


The partner shared that the customer had a similar error several months ago when they uninstalled an ISV module.  As part of that uninstall, SQL scripts were run that replaced the modified ISV posting stored procedures with the original Dynamics GP stored procedures.


With that in mind, we searched the text of all stored procedures to see which ones used the @I_cCorrespondingUnit parameter.


We reviewed the text of those 10 stored procedures, but they each had hundreds of lines, and unfortunately we were unable to figure out which one might be causing the error.

We then used SQL Server Profiler to trace the error.  After that didn't provide any additional clues, we tried a different Profiler template that had been provided by Microsoft Dynamics support when troubleshooting the previous posting error.

That Profiler template included two options that were key to helping us troubleshoot this error.


First, it included the Errors and Warnings events, allowing us to see exactly when the error occurred.

Second, it included the Object Name column, allowing us to see which stored procedure was causing the error.

With that trace running, we captured the additional critical details.


Notice that the errors are shown in red in the first column.  Immediately before the error, we see that the proc glpPostToHistoryYear is calling glpUpdateHistoryTransaction.

And interestingly, we see that the call to glpUpdateHistoryTransaction does not include a parameter for @I_cCorrespondingUnit.  Hmmmm....

So I checked the TWO company in my GP 2016 install, and found that the glpPostToHistoryYear procedure does include the @I_cCorrespondingUnit parameter when calling glpUpdateHistoryTransaction.


So with that, we discovered that the glpPostToHistoryYear stored procedure that was run in the customer environment as part of the ISV uninstall apparently caused this incorrect stored procedure to be created.

I scripted out my glpPostToHistoryYear procedure from TWO, we ran it on the customer's database, and that resolved the posting error.

This is one of the very rare times that I've been able to successfully troubleshoot a Dynamics GP posting error that involved a SQL stored procedure.  Normally it is much more difficult.

But fortunately, we were able to solve the problem and enjoy our Friday evening.


Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics GP, Dynamics 365 Business Central, SQL Server, .NET, Microsoft Flow, and PowerApps.

You can also find him on Twitter and YouTube







No comments:

Post a Comment

All comments must be reviewed and approved before being published. Your comment will not appear immediately.

C# and SQL and Dynamics GP: Sending a List to a Stored Procedure

By Steve Endow This is a discussion of how to send a list of values to a SQL stored procedure parameter. Specifically, how can a .NET ap...