Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Question Unanswered: Cannot create a row of size 8069 which is greater than the allowable maximum of 8060.

    Hi all,

    This has been vexing me, perhaps your collective wisdom could shed some light on it for me. I am using SQL 2005 on Windows Server 2003 R2 Ent x64 SP 2 with Sage CRM 6.0e, when a user typed in a client search of a lastname beginning with "L", it returned an error. On checking the logs, I have the below:

    Cannot create a row of size 8069 which is greater than the allowable maximum of 8060.

    This error is raised from the below:

    select TOP 11 * from vSearchListOpportunity WHERE pers_lastname LIKE N'l%' ESCAPE '|' ORDER BY oppo_casenumber, Oppo_OpportunityId

    This gives:

    Msg 511, Level 16, State 1, Line 1
    Cannot create a row of size 8069 which is greater than the allowable maximum of 8060.


    If I run the below with no ORDER BY clause:

    select TOP 11 * from vSearchListOpportunity WHERE pers_lastname LIKE N'l%' ESCAPE '|'

    It works..., no error. Surely the above unordered version brings back the same row lengths as the ordered version?

    Or am I missing something? Thanks for your help in advance...
    Last edited by magicalarmchair; 09-20-10 at 12:21.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Have you applied any SQL Server service packs?

  3. #3
    Join Date
    Sep 2010
    Posts
    3
    Its SP3ed and fully patched...

  4. #4
    Join Date
    Sep 2010
    Posts
    3
    Well, I've gotten over the problem (although it doesn't adequately explain away why the order by changed the length of the rows) by changing a field (its quite a legacy database and the field is no longer displayed on the forms of CRM) from nchar(50) to nvarchar(MAX) (which I believe removes it from the 8000 limit).

    It now works perfectly.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sorting causes worktables to be created in tempdb. Do the columns you are sorting on come up to close to the limit? Also, I find using nvarchar is usually not warranted, unless you are storing international data. Switching from nvarchar(50) to varchar(50) may also prevent the problem.

Posting Permissions

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