August 3, 2006 at 10:31 am
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
August 3, 2006 at 10:48 am
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.
August 3, 2006 at 11:04 am
Good point...but removing it still gives me the error.
August 3, 2006 at 2:34 pm
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
August 4, 2006 at 8:01 am
Oh ok, so just change it to CONVERT(TEXT, WRnotes), right?
August 4, 2006 at 4:48 pm
At least it will work.
But is it what you need - it's for you to decide.
_____________
Code for TallyGenerator
August 4, 2006 at 8:31 pm
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
Change is inevitable... Change for the better is not.
August 4, 2006 at 10:08 pm
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
August 5, 2006 at 8:21 am
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
Change is inevitable... Change for the better is not.
August 5, 2006 at 5:10 pm
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
August 5, 2006 at 5:27 pm
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?
August 5, 2006 at 7:15 pm
Thanks for the tip, Serqiy... if I ever let my guys put text in the database, that'll help a lot.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 8, 2006 at 6:27 am
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!
August 8, 2006 at 6:36 am
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.
August 8, 2006 at 2:22 pm
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