Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

inherited an app, Error started the next day Expand / Collapse
Author
Message
Posted Monday, April 29, 2013 5:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 7:09 PM
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
Post #1447818
Posted Monday, April 29, 2013 5:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:32 PM
Points: 1,796, Visits: 5,801
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1447819
    Posted Tuesday, April 30, 2013 4:21 AM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Friday, October 10, 2014 7:07 AM
    Points: 3,545, Visits: 2,654
    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.
    Post #1447943
    Posted Tuesday, April 30, 2013 5:54 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 9:54 AM
    Points: 6,813, Visits: 14,028
    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
    Post #1447969
    Posted Tuesday, April 30, 2013 4:38 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, April 30, 2013 7:09 PM
    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.



    Post #1448268
    Posted Tuesday, April 30, 2013 5:12 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: 2 days ago @ 6:08 PM
    Points: 5,401, Visits: 7,514
    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
    Post #1448273
    Posted Tuesday, April 30, 2013 5:22 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, April 30, 2013 7:09 PM
    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.
    Post #1448274
    Posted Wednesday, May 1, 2013 1:07 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 9:54 AM
    Points: 6,813, Visits: 14,028
    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
    Post #1448325
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse