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!

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s