Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unhappy Unanswered: SQL 7 Hanging with large In() Statement

    For MS SQL 7, an error occurs as shown at bottom.
    Basically a piece of embedded sql is being sent to the db which has a syntax like:
    select distinct WC_REFERENCE_MANAGER.logicalname,
    WC_REFERENCE_MANAGER.referenceid,
    wc_site.doclistid,
    WC_REFERENCE_MANAGER.shared,
    WC_REFERENCE_MANAGER.languageid
    from wc_content inner join wc_content_ref on wc_content.contentid = wc_content_ref.documentid
    inner join wc_reference_manager on wc_content_ref.referenceid = wc_reference_manager.referenceid
    inner join wc_site on wc_reference_manager.referenceid = wc_site.referenceid
    where xmlid = @p1 and contains(xmldata, @p2)
    and wc_reference_manager.languageid = @p3
    and wc_site.siteid = @p4
    and wc_site.sectionid IN (

    Followed by over 15000 numbers

    (dont ask - its a generic product with a generic db design behind it!).

    Any comments apart from rewrite the query (which isnt an option).

    The error is :



    02/27/04 14:30:05 Stack Overflow Dump not possible - Exception c00000fd E at 00499f17
    2004-02-27 14:30:05.93 spid8 Address=499f17 Exception Code = c00000fd
    2004-02-27 14:30:05.93 spid8 eax=394d29f4 ebx=00000000 ecx=394d29f4 edx=0000000d
    2004-02-27 14:30:05.93 spid8 esi=394d29f4 edi=433a499c eip=00499f17 esp=3c263000
    2004-02-27 14:30:05.93 spid8 ebp=3c26300c efl=00010a97
    2004-02-27 14:30:05.93 spid8 cs=1b ss=23 ds=23 es=23 fs=3b gs=0
    2004-02-27 14:30:05.93 spid8 Input Buffer 250651 bytes -
    2004-02-27 14:30:05.93 spid8
    2004-02-27 14:30:05.93 spid8 s p _ p r e p a r e & @ T @ p 1 i n t , @ p 2 n v a r c h a
    2004-02-27 14:30:05.93 spid8 r ( 4 0 0 0 ) , @ p 3 i n t , @ p 4 i n t c s e l e c t
    2004-02-27 14:30:05.93 spid8 d

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    See this part....

    s p _ p r e p a r e & @ T @ p 1 i n t
    , @ p 2 n v a r c h a( 4 0 0 0 ) , @ p 3 i n t , @ p 4 i n t c s e l e c t

    See the varchar(4000)

    That's where your SQL statements gets prepared ...

    It's to big..

    My guess is the code is generated...And are you doing something to select all of those numbers?

    Can you break it up in to (MUCH) smaller sets?
    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.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That would certainly rate a "Bad code! No donut!" for any dba or developer in my shop.

    If you can't fix the code, fix the coder (so they don't make any little ones for your children to care for)!

    -PatP

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nah..it's a third party tool that probably collects request from an interface, generates the code and executes it dynamically...

    That's why s/he can't change it.

    Right?

    Because it doesn't exist
    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.

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    Thanks for your help Guys,

    Looking to see if we can avoid IN( statement altogether. If not (because this statement is returned to us from product), back to the drawing board.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you tell us what the product name is?

    [s]I'd like to shoot the devolper you see....[/s]

    EDIT: Not PC in todays world I guess

    I'd like to give that developer a good talking to....
    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
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    So, whatever is in the IN (...) is what comes back from the product? The error is a new one with the release of anti-worm service pack targeting unchecked buffers (SP3 for 2K and SP4 for 7.0). The server posts "Invalid TDS packet received from client" into its errorlog. Our Java coders are getting a lot of this, because they still don't understand how to invoke a stored procedure other than through c.Execute("exec sp_xxxxx .....") which gets translated by JDBC driver into what you all see at the top here. What can you do? Beat them with a bat (oh yeah, very tempting )?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, but people will talk about you if you use the bat in public.

    -PatP

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Pat Phelan
    Yes, but people will talk about you if you use the bat in public.

    -PatP
    It's a private visit to each cube....
    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.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by Pat Phelan
    Yes, but people will talk about you if you use the bat in public.

    -PatP
    I'll make sure they won't...hehehe

Posting Permissions

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