Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    100

    Unanswered: Please Help: Cannot Sort row of size 8104: any advice.

    I have a database linked too a website that, we've just put the live data into and hay presto the thing has fallen over. The data has gone in fine but When we search on it it coming up with this error:

    Microsoft OLE DB Provider for SQL Server (0x80040E14) Cannot sort a row of size 8104, which is greater than the allowable maximum of 8094.

    Has anyone come across this kinda of problem before and is their any nice work arounds. Thanks Ed

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Please Help: Cannot Sort row of size 8104: any advice.

    Obviously, you managed to get a row size which is 10 more than the maximum. Consider to shorten one of your VARCHAR(x) fields by 10.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Jul 2003
    Posts
    100

    Thanks

    Okay I think I understand the problem now, But I'm still slightly stuck;
    I've got the huge amount of data that I've imported and it gone in fine it just this search which throws up problems.

    So as far as I can see the only two solution I can look at are some way to make the search accept these size of data or come up with some kind of way of identifing just those singler rows which are causing the problem and deal with them indevidually. Is their some kind of SQL statment that would just return the row where data is above a certain size?


    If it any help the SQL look like this:

    SELECT DISTINCT
    terms.TermName, terms.TermAltName, terms.TermShortDesc, linkSectionSub.SectionID, linkSectionSub.SubID, terms.AutoId,
    subSections.SUBName, sections.SCTName
    FROM terms INNER JOIN
    linkSectorSection INNER JOIN
    linkSectionSub ON linkSectorSection.SectionID = linkSectionSub.SectionID INNER JOIN
    linkSubTerm ON linkSectionSub.SubID = linkSubTerm.SubID ON terms.AutoId = linkSubTerm.TermID INNER JOIN
    sections ON linkSectionSub.SectionID = sections.AutoId INNER JOIN
    subSections ON linkSectionSub.SubID = subSections.AutoID
    WHERE (linkSectorSection.SectorID = 1)
    AND (terms.TermName LIKE '%mortgage%')
    OR (linkSectorSection.SectorID = 1)
    AND (terms.TermShortDesc LIKE '%mortgage%')
    ORDER BY terms.TermName

    And the field causing the problems is:
    terms.TermShortDesc

    Thanks Again

  4. #4
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Thanks

    Originally posted by Nixies
    Okay I think I understand the problem now, But I'm still slightly stuck;
    I've got the huge amount of data that I've imported and it gone in fine it just this search which throws up problems.

    So as far as I can see the only two solution I can look at are some way to make the search accept these size of data or come up with some kind of way of identifing just those singler rows which are causing the problem and deal with them indevidually. Is their some kind of SQL statment that would just return the row where data is above a certain size?


    If it any help the SQL look like this:

    SELECT DISTINCT
    terms.TermName, terms.TermAltName, terms.TermShortDesc, linkSectionSub.SectionID, linkSectionSub.SubID, terms.AutoId,
    subSections.SUBName, sections.SCTName
    FROM terms INNER JOIN
    linkSectorSection INNER JOIN
    linkSectionSub ON linkSectorSection.SectionID = linkSectionSub.SectionID INNER JOIN
    linkSubTerm ON linkSectionSub.SubID = linkSubTerm.SubID ON terms.AutoId = linkSubTerm.TermID INNER JOIN
    sections ON linkSectionSub.SectionID = sections.AutoId INNER JOIN
    subSections ON linkSectionSub.SubID = subSections.AutoID
    WHERE (linkSectorSection.SectorID = 1)
    AND (terms.TermName LIKE '%mortgage%')
    OR (linkSectorSection.SectorID = 1)
    AND (terms.TermShortDesc LIKE '%mortgage%')
    ORDER BY terms.TermName

    And the field causing the problems is:
    terms.TermShortDesc

    Thanks Again
    I'm assuming that TermShortDesc is a text field not a varchar.

    You have two possible solutions:

    Fix the problem short term:
    SELECT terms.AutoId, LEN(terms.TermShortDesc) AS CHAR_LEN, terms.TermShortDesc
    FROM TERMS
    WHERE LEN(terms.TermShortDesc) >8093.

    and then edit that data down in length.

    The permanent solution is:
    Before you get to deep into this, ask yourself these questions:
    Do you have to search the entire field? Can you limit data to 8093 characters? In your search do you really have to go beyond the first 500 characters? 1000?

    For a permanent solution if you only need the first 500 characters, then make an in your import that TermShortDescSort field and just take the LEFT(TermShortDesc,500) and insert them into TermShortDescSort on import. If you need to go beyond and have all 24,282, then make 3 fields TermShortDesc1, TermShortDesc2, TermShortDesc3 and on import insert into them as Substr(TermShortDesc,1,8094), Substr(TermShortDesc,8095,8094), Substr(TermShortDesc,16188,8094).

    Just throwing in my 2 centavos.....
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  5. #5
    Join Date
    Jul 2003
    Posts
    100

    Excellent

    This is fantasic, we've found the offending rows and everything is up and working, Thanks for your Help, Ed

Posting Permissions

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