Row Size is too large?

Question

Row Size is too large?

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 1

Sergiy, I should of said I did try that too. I've also tried using CAST instead of CONVERT. I have examined my query over and over and do not see a reason for receiving 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, 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, WRnotes

The error I get is the same:

[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 can find no instance of where I am comparing or sorting the WRnotes column.

Jeff Moden SSC Guru Points: 1004287 More actions · Answer 2

In your last query posting, you are grouping by WRNotes and you cannot.

--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)

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 3

But if I take WRnotes out of the GROUP BY I get the infamous error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'workrequest.WRnotes' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Why you need GROUP BY at all?

_____________
Code for TallyGenerator

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 5

Sergiy, if I remove the entire GROUP BY, I still get this error:

Column 'requestgenerator.RGnameLast' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

So I need that clause in there to satisfy some requirement.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

The 'some' requirements in your case, is that you have aggregates in your select. (SUM, MAX, MIN etc)

If you have aggregates, then all non-aggregated columns must be GROUPED BY

A workaround to include columns that don't need grouping, but can't be, or won't be in the GROUP BY clause, may be - depending on the data, to use MAX or MIN on that column, if either makes no difference to the result.

If this is a way to go in this case, I can't really tell.

Another workaround may be to split up the query in parts, do the aggregates first, enclose them in a virtual table, and then join out from that to include the nonaggregated columns...

It's late and I have to run, so I can't give any examples at this time, sorry.

/Kenneth

Gift Peddie SSC Guru Points: 73570 More actions · Answer 7

It means that one of the Rows is bigger than a SQL Server page, the text below is from the link below and it includes more restrictions. The reasons and the restrictions are valid based on the relational algebra. Hope this helps.

(There is no limit to the number of items in the ORDER BY clause. However, there is a limit of 8,060 bytes for the row size of intermediate worktables needed for sort operations. This limits the total size of columns specified in an ORDER BY clause.)

http://msdn2.microsoft.com/en-us/library/ms188385.aspx

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

Sergiy SSC Guru Points: 110208 More actions · Answer 8

You have MAX() and MIN() for some columns, and you are definetely not allowed (and probably don't intend) to group you text notes.

I could help you with this query if you build this query right way first.

a) assign aliases to each table you use;

b) use proper "INNER JOIN ... ON ..." syntax;

c) qualify every column name with table alias.

_____________
Code for TallyGenerator