Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


inherited an app, Error started the next day


inherited an app, Error started the next day

Author
Message
chadmack
chadmack
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 16
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
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2286 Visits: 7830
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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • sqlnaive
    sqlnaive
    Hall of Fame
    Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

    Group: General Forum Members
    Points: 3697 Visits: 2774
    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
    ChrisM@Work
    SSCrazy Eights
    SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

    Group: General Forum Members
    Points: 9027 Visits: 19036
    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
    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
    Exploring Recursive CTEs by Example Dwain Camps
    chadmack
    chadmack
    Forum Newbie
    Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

    Group: General Forum Members
    Points: 3 Visits: 16
    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
    Evil Kraig F
    SSCertifiable
    SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

    Group: General Forum Members
    Points: 5715 Visits: 7660
    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 | Forum Netiquette
    For index/tuning help, follow these directions. |Tally Tables

    Twitter: @AnyWayDBA
    chadmack
    chadmack
    Forum Newbie
    Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

    Group: General Forum Members
    Points: 3 Visits: 16
    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
    ChrisM@Work
    SSCrazy Eights
    SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

    Group: General Forum Members
    Points: 9027 Visits: 19036
    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
    Exploring Recursive CTEs by Example Dwain Camps
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search