Dynamics GP – Select Cheques routine looping because of duplicate key error in DEX_LOCK

A quick one but hopefully it will help someone else in the future.

We had an issue raised last week with a client running the “Select Cheques” routine in payables and although it appeared to be running normally it was taking much longer than usual and never seemingly completing.

As usual my first port of call is to run a SQL Profile Trace of the process (should probably be using extended events by now) to monitor exactly what’s happening.

When I setup an SQL trace I generally start with RPC:Completed and SQL:BatchCompleted as per below:

This image has an empty alt attribute; its file name is image-17.png

After running this trace I could see that the process seems to be showing a loop where’s its SELECTing data and them attempting to INSERT into the DEX_LOCK table over and over again for the same document:

To investigate further I stopped the trace and added “Errors and Warning” and restarted the trace again

The trace now shows the issue. The system is producing a duplicate key error when trying to INSERT into the DEX_LOCK table.

Because of this it seems to loop back and then try the INSERT again. The error isn’t being reported to the end user so they just think the process is running but taking longer than usual.

As expected when i query the DEX_LOCK table the row_id (42468782 – yes this client posts LOTS and LOTS of payables data) relates to the DEX_ROW_ID in the PM20000 for the document mentioned in the SELECT in the trace:

To fix the issue I deleted the offending row in the DEX_LOCK table as per below and the “Select Cheques” process continued as normal. (I didn’t even have to stop the “Select Cheques” process, I just deleted the row and it went through fine)

I hope this helps anyone else facing a similar issue.

Thanks for reading!

Dynamics GP – Error Message Invalid length parameter passed to LEFT or SUBSTRING function in Edit Payables Check Window

I encountered this error when a client was trying to select a vendor in the “Edit Payables Cheque Batch” window. A screen shot of the error is below

The error occurred when trying to select a vendor from the list in the left hand pane of the window.

To troubleshoot this I created a SQL trace to capture all the SQL statements and also any error messages when the error occurred. This highlighted the exact error and offending SQL statement as per below:

Next I copied the offending SQL statement to SQL management studio and ran it in a query window. (I do have to tweak it a little and I also checked it wasn’t going to do any updates or inserts that could cause data issues). This gave me the same error being returned by Dynamics GP

Now it was a question of finding all the LEFT or SUBSTRING statements, commenting them out, and re-running the query until it worked. After some trial and error I found when commenting out the highlighted portion of the SQL statement below it executed without error

Therefore I now know the issue is with the A.KEYSOURC field. At this point I suspected it was blank and therefore the LEN calculation is producing a 0. The alias A has been given to the PM20100 table so I queried this table for the vendor in question and as suspected there is a blank KEYSOURC for this vendor.

I therefore deleted this blank record using a simple SQL script

DELETE PM20100 WHERE VENDORID='<InsertVendorid>' AND KEYSOURC=''

And now I can successfully select the vendor in the “Edit Payables Cheque Batch” window.

I hope this helps explain the issue and also how it can be fixed if anyone else encounters this in the future.

Thanks for reading!

Dynamics GP – The remittance reprint is including a remittance for a different vendor

A client reported that when reprinting a remittance via the “Recreate Check Stub” option it was correctly reprinting the remittance however another remittance for a totally different vendor was also printing at the same time?

The option being used for the remittance reprint is below

To troubleshoot I looked at the main table used on the “Reprint Check Remittance” layout in report writer.

As you can see from the image above this is the “PM Remittance Temp” table which on investigation is the PM80950 table.

When I queried this table in SQL I found a single row and the Vendor ID matched that of the remittance that was being tagged onto the reprint.

I therefore removed this row from the table by running a DELETE statement in SQL and now when I reprint the remittance it prints correctly.

I hope this helps someone else in the future if they come across the same issue.

Thanks for reading