Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: Error in query; "Invalid length parameter passed to the substring function"

    Hi
    i got errro mess "Invalid length parameter passed to the substring function" from this below. Anyone how can give me a hint what cause this, and how i can solve it? if i remove whats whitin thoose [] it works, i dont use [] in the code
    colums:
    VLF_InfectionDestination is nvarchar 254

    SELECT TOP 10 tb_AVVirusLog.VLF_VirusName, COUNT(tb_AVVirusLog.VLF_VirusName) AS number
    FROM tb_AVVirusLog INNER JOIN
    __CustomerMachines002 ON tb_AVVirusLog.CLF_ComputerName = __CustomerMachines002.FalseName
    WHERE (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
    (__CustomerMachines002.folder_id = @folderId) [OR
    (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
    (tb_AVVirusLog.VLF_InfectionDestination LIKE N'%@%')]
    GROUP BY tb_AVVirusLog.VLF_VirusName
    HAVING (NOT (tb_AVVirusLog.VLF_VirusName LIKE N'cookie'))
    ORDER BY COUNT(tb_AVVirusLog.VLF_VirusName) DESC
    Last edited by mrpcguy; 11-28-05 at 17:16.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Generally, you get this error when you pass in a bad parameter, like a negative startpoint, or a length parameter that takes you out of bounds of the string. Maybe instead of convert (varchar), you should try convert(varchar(10)). I don't see substring in there.

  3. #3
    Join Date
    Mar 2004
    Posts
    162
    naa cant get it to work... that damn error pop up no matter what i do...

  4. #4
    Join Date
    Mar 2004
    Posts
    162
    is i reverse the order and put in a AND insteed it works... like this
    [AND
    (CONVERT (tb_AVVirusLog.VLF_InfectionDestination LIKE N'%@%')] AND (varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday)]

    how come o got that error the other way?

    //Mr

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Read the sticky at the top of this forum..Post us the DDL and some sample data and what the expected results are suppose to be...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about this?

    Code:
    DECLARE @date1 datetime, @date2 datetime
    SELECT @date1 = CONVERT(datetime, @fyear + @fmonth + @fday)
         , @date2 = CONVERT(datetime, @tyear + @tmonth + @tday)
    
    
    SELECT TOP   10 a.VLF_VirusName
    	   , COUNT(a.VLF_VirusName) AS number
          FROM   tb_AVVirusLog       a
    INNER JOIN __CustomerMachines002 b 
    	ON a.CLF_ComputerName = b.FalseName
         WHERE (     a.CLF_LogGenerationTime > @Date1 AND a.CLF_LogGenerationTime <= @Date2
                 AND b.folder_id = @folderId) 
            OR (     a.CLF_LogGenerationTime > @Date1 AND a.CLF_LogGenerationTime <= @Date2
                 AND a.VLF_InfectionDestination LIKE N'%@%')
      GROUP BY a.VLF_VirusName
        HAVING a.VLF_VirusName NOT LIKE N'cookie'
      ORDER BY COUNT(a.VLF_VirusName) DESC
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2004
    Posts
    162
    thx, i manage this to work, and i got some new ideas how to do things....

    //Mr

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by mrpcguy
    thx, i manage this to work, and i got some new ideas how to do things....

    //Mr

    ...and you're not gonna share it with us....I'll never gety thos 5 minutes back....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •