Invalid length parameter passed to the LEFT or SUBSTRING function
Adrian Cooke asked 7 years ago
Hi. I am importing a fairly simple csv file into GP to create a Shipment Receipt using the Purchase Order Processing > Receivings node. I get the error in the title. I have tried all manner of things. The error message doesn’t refer to an individual field so I’m struggling to know why the error may occur
Thanks
Thanks
Answers
Patrick Roth Staff answered 7 years ago
Adrian,
That error is a SQL error and occurs when, well, you pass in an invalid length param to a LEFT or SUBSTRING function.
IN this case, “invalid length” means “negative”. 0 is acceptable as well as too large a value.
for example, this code will product the same error:
declare @s as varchar(5)
set @s = ‘abcde’
print LEFT(@s,-1)
Now if I set -1 to 0 it prints nothing. If I set -1 to 25 (5x bigger than the original string), I still get back the original strings.
So the question is – where is that coming from?
The first thing I’d look at is to see if you have any SQL tasks that you created on the map – to see if they use either function. then see what you are passing for the length In the function.
if there isn’t an issue that you see (ie you don’t have SQL tasks or you do but don’t have these functions), then the next steps would be to do a full SQL trace on the system.
Look to see where the error occurs and with the statement generated you’d have to figure out what you can do about it.
Patrick
eOne
That error is a SQL error and occurs when, well, you pass in an invalid length param to a LEFT or SUBSTRING function.
IN this case, “invalid length” means “negative”. 0 is acceptable as well as too large a value.
for example, this code will product the same error:
declare @s as varchar(5)
set @s = ‘abcde’
print LEFT(@s,-1)
Now if I set -1 to 0 it prints nothing. If I set -1 to 25 (5x bigger than the original string), I still get back the original strings.
So the question is – where is that coming from?
The first thing I’d look at is to see if you have any SQL tasks that you created on the map – to see if they use either function. then see what you are passing for the length In the function.
if there isn’t an issue that you see (ie you don’t have SQL tasks or you do but don’t have these functions), then the next steps would be to do a full SQL trace on the system.
Look to see where the error occurs and with the statement generated you’d have to figure out what you can do about it.
Patrick
eOne