inherited an app, Error started the next day

Question

inherited an app, Error started the next day

chadmack

Grasshopper

Points: 17
More actions
April 29, 2013 at 5:23 pm

#293224

Hi,
First post, I've been using SQL for about 3 years, I just changed jobs and inherited a bunch of code.
I have tracked down the problem section of code but haven't worked much with cursors. I could use a hand to solve this bug.
------Error-----
Msg 536, Level 16, State 5, Line 34
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
Msg 536, Level 16, State 5, Line 34
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
.........{INFINITE LOOP}
-----Code Snippet------
PRINT 'CREATE RECORDS BASED ON MOVEITEMS INFORMATION STOCK'
DECLARE @MESSAGE VARCHAR(2000),
@ID INT,
@DATE DATETIME,
@STOCKCODE varchar(50),
@DESCRIPTION varchar(2000),
@userid INT
CREATE TABLE #TEMP (
[ID] INT,
[DATE] DATETIME,
ITEMID INT,
STOCKCODE VARCHAR(50),
[DESCRIPTION] VARCHAR(100),
USERID INT,
QUANTITY FLOAT,
DIRECTION VARCHAR(10),
LOCATIONID INT
)
DECLARE MOVEITEMS_CUR CURSOR
FOR SELECT MOVEITEMSID,MOVEDATE,ITEMID,[DESCRIPTION],USERID FROM MYOB_IMPORT..MOVEITEMS
OPEN MOVEITEMS_CUR
FETCH NEXT FROM MOVEITEMS_CUR
INTO @ID,@DATE,@STOCKCODE,@DESCRIPTION,@USERID
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE (LEN(@DESCRIPTION)) > 5
BEGIN
INSERT INTO #TEMP ([ID],[DATE],ITEMID,STOCKCODE,[DESCRIPTION],USERID,QUANTITY,DIRECTION,LOCATIONID)
SELECT @ID AS [ID],
@DATE AS [DATE],
@STOCKCODE AS ITEMID,
ITEMNUMBER AS STOCKCODE,
REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) - 1),CHAR(10),''),'MOVED ',''),
@userid AS USERID,
CASE WHEN CHARINDEX('INTO',REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) - 1),CHAR(10),''),'MOVED ','')) = 0 THEN CONVERT(FLOAT,REPLACE(LEFT(@DESCRIPTION,CHARINDEX(' ',@DESCRIPTION) - 1),CHAR(10),''))
ELSE - CONVERT(FLOAT,REPLACE(LEFT(@DESCRIPTION,CHARINDEX(' ',@DESCRIPTION) - 1),CHAR(10),''))
END AS QUANTITY,
DIRECTION = CASE WHEN CHARINDEX('INTO',REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) - 1),CHAR(10),''),'MOVED ','')) = 0 THEN 'TO'
ELSE 'FROM' END,
L.LOCATIONID
FROM MYOB_IMPORT..ITEMS I
LEFT JOIN MYOB_IMPORT..LOCATIONS L ON LEFT(REVERSE(LEFT(REVERSE(REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) ),CHAR(10),''),'MOVED ','')),CHARINDEX(' ',REVERSE(REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) ),CHAR(10),''),'MOVED ',''))) - 1)),LEN(LEFT(REVERSE(REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) ),CHAR(10),''),'MOVED ','')),CHARINDEX(' ',REVERSE(REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) ),CHAR(10),''),'MOVED ',''))) - 1)) - 1) = L.LOCATIONIDENTIFICATION
WHERE @STOCKCODE = ITEMID
IF (LEN(@DESCRIPTION) - LEN(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION)))) > 0
BEGIN
SELECT @DESCRIPTION = RIGHT(@DESCRIPTION,LEN(@DESCRIPTION) - LEN(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION))))
END
ELSE
BEGIN
BREAK
END
END
FETCH NEXT FROM MOVEITEMS_CUR
INTO @ID,@DATE,@STOCKCODE,@DESCRIPTION,@USERID
END
CLOSE MOVEITEMS_CUR
DEALLOCATE MOVEITEMS_CUR
GO
any assistance appreciated
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

The code is too messy to look at : use the IFCode shortcuts on the left when you post code, specifically the code/sql one...

Anyway, I suggest make the description column in your temp table 2000 long - see if it stops the error. If it does, then at least you know it is the description column that is causing the problem. If it still errors, try another "char" based column, see if that works...etc...etc...

Edit: after reading sqlnaive's reply, I realised I had misread and they are right, this is not a truncation error! Doh!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

sqlnaive SSCoach Points: 17437 More actions · Answer 2

What i think is some of the CHARINDEX is either returning a NULL or a 0. check it individually one at a time. If not so, even I woul dbe interested to see what's happening here.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

I'd check for NULL decsription in the source table:

DECLARE MOVEITEMS_CUR CURSOR FOR

SELECT MOVEITEMSID, MOVEDATE, ITEMID, [DESCRIPTION], USERID

FROM MYOB_IMPORT..MOVEITEMS

WHERE [DESCRIPTION] IS NOT NULL

The code is horrible! I gave up after playing for a few minutes:

DECLARE @MESSAGE VARCHAR(2000),

@ID INT,

@DATE DATETIME,

@STOCKCODE varchar(50),

@DESCRIPTION varchar(2000),

@userid INT

CREATE TABLE #TEMP (

[ID] INT,

[DATE] DATETIME,

ITEMID INT,

STOCKCODE VARCHAR(50),

[DESCRIPTION] VARCHAR(100),

USERID INT,

QUANTITY FLOAT,

DIRECTION VARCHAR(10),

LOCATIONID INT

)

DECLARE MOVEITEMS_CUR CURSOR FOR

SELECT MOVEITEMSID, MOVEDATE, ITEMID, [DESCRIPTION], USERID

FROM MYOB_IMPORT..MOVEITEMS

WHERE [DESCRIPTION] IS NOT NULL

OPEN MOVEITEMS_CUR

FETCH NEXT FROM MOVEITEMS_CUR

INTO @ID, @DATE, @STOCKCODE, @DESCRIPTION, @userid

WHILE @@FETCH_STATUS = 0

BEGIN

WHILE (LEN(@DESCRIPTION)) > 5

BEGIN

INSERT INTO #TEMP ([ID],[DATE],ITEMID,STOCKCODE,[DESCRIPTION],USERID,QUANTITY,DIRECTION,LOCATIONID)

SELECT

[ID] = @ID,

[DATE] = @DATE,

ITEMID = @STOCKCODE,

STOCKCODE = ITEMNUMBER,

[DESCRIPTION] = x.NewDescription,

USERID = @userid,

QUANTITY = CASE

WHEN CHARINDEX('INTO',x.NewDescription) = 0

THEN x.QtyMoved

ELSE 0 - x.QtyMoved

END,

DIRECTION = CASE

WHEN CHARINDEX('INTO',x.NewDescription) = 0

THEN 'TO'

ELSE 'FROM' END,

L.LOCATIONID

FROM MYOB_IMPORT..ITEMS I

CROSS APPLY (

SELECT

DescFirstLine = LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION)),

DescFirstWord = LEFT(@DESCRIPTION,CHARINDEX(' ',@DESCRIPTION)-1)

) b

CROSS APPLY (

SELECT

NewDescription = REPLACE(REPLACE(LEFT(@DESCRIPTION,CHARINDEX(CHAR(13),@DESCRIPTION) - 1),CHAR(10),''),'MOVED ',''),

QtyMoved = CONVERT(FLOAT,REPLACE(DescFirstWord,CHAR(10),''))

) x

CROSS APPLY (

SELECT esrever = REVERSE(REPLACE(REPLACE(DescFirstLine,CHAR(10),''),'MOVED ',''))

) y

LEFT JOIN MYOB_IMPORT..LOCATIONS L

ON LEFT(REVERSE(LEFT(y.esrever,CHARINDEX(' ',y.esrever) - 1)),

LEN(LEFT(y.esrever,CHARINDEX(' ',y.esrever) - 1)) - 1) = L.LOCATIONIDENTIFICATION

WHERE @STOCKCODE = ITEMID

IF (LEN(@DESCRIPTION) - LEN(b.DescFirstLine)) > 0

BEGIN

SELECT @DESCRIPTION = RIGHT(@DESCRIPTION,LEN(@DESCRIPTION) - LEN(b.DescFirstLine))

END

ELSE

BEGIN

BREAK

END

FETCH NEXT FROM MOVEITEMS_CUR

INTO @ID, @DATE, @STOCKCODE, @DESCRIPTION, @userid

END

CLOSE MOVEITEMS_CUR

DEALLOCATE MOVEITEMS_CUR

What would be really interesting to see would be MOVEITEMS.description and matches with LOCATIONS.LOCATIONIDENTIFICATION, because I don't believe for one moment that the expressions should be as complex as they are.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

chadmack Grasshopper Points: 17 More actions · Answer 4

next stop with this is Daily WTF. Just waiting for my account to clear

No nulls in the description column.

Examples of the 2 columns getting joined.

Moveitems

229 moved into 032C 336 moved out of 041A 400 moved into 099B 341 moved out of 106A 74 moved out of 110B 270 moved into 129B 59 moved into 130B 42 moved out of 133A 468 moved out of 134A 33 moved into S000-New 150 moved out of S000Trans 420 mov

Location ID

ZZR02D

I didn't build it, I just inherited it and need it fixed by close of business.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

Not sure we'll be able to help you by close of business but I have a concern. I know substring is the core function and left/right are just wrappers, but the error message is usually explicit as to which one of the three are failing.

There is no substring() in your snippet. I fear we'd need to see the entirety. I'm concerned that it might be buried in a function of some kind.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

chadmack Grasshopper Points: 17 More actions · Answer 6

Thanks to everyone so far, Code is over 9000 lines in its entirety.

This appears to be a data issue. The data in moveitems.description is getting tuncated, this is causing errors when looking for the char(13) line separators.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

chadmack (4/30/2013)
Thanks to everyone so far, Code is over 9000 lines in its entirety.
This appears to be a data issue. The data in moveitems.description is getting tuncated, this is causing errors when looking for the char(13) line separators.

Check the ddl for table MYOB_IMPORT..MOVEITEMS, the [description] string you posted is around 244-258 characters.

That sproc could be easily rewritten to be far simpler, more reliable and set-based, using delimitedsplit2k8 separating on char(13) instead of all the confusing string arithmetic.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden