Row Size is too large?
-
I suddenly started receiving this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot sort a row of size 8176, which is greater than the allowable maximum of 8094.
I have never run into this error before. But I did some research on google, and found out what it means. But for the life of me, I can't figure out why my query would fail with this error.
Here is my query:
select RGnameLast+', '+RGnameFirst AS Adjuster,
RGnameLast, COcity AS ClientOffice, IOofficeName, WRworkOfficeId, WRid,
SRnameLast+', '+SRnameFirst AS Subject,
CONVERT(CHAR(8), MIN(WCcompletedDate), 112) AS FilterDate,
WRbillToFileID AS ClaimNumber,
SUM(WCvideoSeconds) as VideoAmount, CONVERT(VARCHAR(12), -1) AS CaseCost,
CAST('' AS TEXT) AS Results, WRinjury, CONVERT(VARCHAR(8000), WRnotes)
AS WRnotes, 0 AS Saved
INTO inet.dbo.jones_temp
FROM workrequest, requestgenerator, clientoffice, subjectrecord, worktask, workcompleted, internaloffice, billmaster
WHERE 1 = 1
AND BMworkrequestid = WRid
AND WRrequestGeneratorID = RGid
AND RGclientOfficeID = COid
AND WRsubjectRecordID = SRid
AND WTlinkItemID = WRid
AND WClinkItemID = WTid
AND WRworkOfficeId = IOid
AND WCcompletedDate BETWEEN '8/1/2006' AND '8/30/2006'
GROUP BY RGnameLast, RGnameFirst, COcity, IOofficeName, WRworkOfficeId, WRid,
SRnameLast, SRnameFirst, WRbillToFileID, WRinjury, CONVERT(VARCHAR(8000),
WRnotes) ORDER BY WCcompletedDate
Is anything sticking out like a sore thumb?
Thanks
-
Why use an ORDER BY with a SELECT INTO ? This does not gurantee anything about the physical ordering of the rows in the destination table.
-
Good point...but removing it still gives me the error.
-
CONVERT(VARCHAR(8000), WRnotes)
creates a column of 8000 size. Plus other columns in this table - and you are over the limit.
Probably you don't need 8000 chars for this column. Or if you need to fit there "as much as possible" consider using text column.
_____________
Code for TallyGenerator -
Oh ok, so just change it to CONVERT(TEXT, WRnotes), right?
-
At least it will work.
But is it what you need - it's for you to decide.
_____________
Code for TallyGenerator -
Good lord, don't use a text column. If you have to, put a VARCHAR(8000) in a parallel table with the same key... you're asking for a world of hurt when it comes to TEXT datatypes.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Why not?
As I can understand this column is just a dump for some texts.
If there is any kind of useful information in it (e.g. key words, some profiles) then it must segregated and stored in another column.
But if you one day will need to search in this column, and you don't use fulltxt search then select the text by portions of 8000 bytes and search in it. Your query performance not to suffer too much. Table scan gonna be involved anyway.
_____________
Code for TallyGenerator -
Because it's just as easy to make a parallel table and still enjoy all of the benefits of the various string functions (if you need them, someday) that you can use on VARCHAR that cannot be used on Text. Also, since it is just a dump for some text (in this case), wouldn't it be better to do like most folks do with pictures, etc, and just have a filename for the text as we've so often preached? Last but not least, you don't have to worry about those bloody text pointers with VARCHAR.
Now, in SQL Server 2005... different story with VARCHAR(MAX)...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If you need it "someday" you can create a view
SELECT ..., CONVERT(varchar(8000), TextColumn) as VarcharColumn
FROM TableWithTextColumn
YOu can use all your favourite benefits without managing 2 tables.
Don't like views - create computed column in the same table for SELECT purposes.
What I like about text - it's stored in separate pages, outside of tables, I prefer to store it even on separate filegroup. So, this rubbish does not slow down performance of normal queries which are used the most.
_____________
Code for TallyGenerator -
Another possibly less conventional solution would be to see if there are just one or two WRNotes entries that cause the whole row to exceed maximum length. If so, can these be trimmed and a check be placed to limit the size of the notes dump?
-
Thanks for the tip, Serqiy... if I ever let my guys put text in the database, that'll help a lot.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks guys...it's just a bunch of text from an online diary. It does not contain any keys or any unique data like that. Just a bunch of text that needs to be displayed on a website.
Thanks again!
-
ahhh...of course I get another error. After changing the varchar(8000) to a text type, I now get this error:
select RGnameLast+', '+RGnameFirst AS Adjuster, RGnameLast, COcity AS ClientOffice, IOofficeName, WRworkOfficeId, WRid, SRnameLast+', '+SRnameFirst AS Subject, CONVERT(CHAR(8), MIN(WCcompletedDate), 112) AS FilterDate, WRbillToFileID AS ClaimNumber, SUM(WCvideoSeconds) as VideoAmount, CONVERT(VARCHAR(12), -1) AS CaseCost, CAST('' AS TEXT) AS Results, WRinjury, 0 AS Saved, CONVERT(TEXT, WRnotes) AS WRnotes INTO inet.dbo.jones_temp FROM workrequest, requestgenerator, clientoffice, subjectrecord, worktask, workcompleted, internaloffice, billmaster WHERE 1 = 1 AND BMworkrequestid = WRid AND WRrequestGeneratorID = RGid AND RGclientOfficeID = COid AND WRsubjectRecordID = SRid AND WTlinkItemID = WRid AND WClinkItemID = WTid AND WRworkOfficeId = IOid AND WCcompletedDate BETWEEN '8/1/2006' AND '8/30/2006' GROUP BY RGnameLast, RGnameFirst, COcity, IOofficeName, WRworkOfficeId, WRid, SRnameLast, SRnameFirst, WRbillToFileID, WRinjury, CONVERT(TEXT, WRnotes)
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
I am not comparing or sorting the text field. When I try taking it out of the GROUP BY clause, I just get another, but different error.
-
GROUP BY ...., WRnotes
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply