Row is too big - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Row is too big

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-02-20 : 08:53:19

I have a form that searches for results in a 3 table join statement. It worked fine for weeks and stopped working over the weekend. Actually, it works for some keywords and doesn't work for others. When it doesn't work, it gives this error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot sort a row of size 8224, which is greater than the allowable maximum of 8094

The database data that was uploaded last week wasn't any bigger than data from previous weeks, so I don't know what it could be.

Thanks...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-02-20 : 09:13:36

the combined zize in bytes of all your columns in one of the tables is more than 8094 byte.
either reduce the varchar columns in size or put change them to a text/ntext datatype.

Go with the flow & have fun! Else fight the flow

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-02-20 : 09:45:58

i've had problems with long text ads going over the 8000 varchar limit. we've had to cut them down to get into the database, but once we get them in, why is it not pulling them up in a search.

i guess that means we need to cut them down even further?

i'd like to switch to datatype text, but when i do that it won't display in the browser. any idea how to fix that?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-02-20 : 10:08:10

why wouldn't it display in a browser??
how are you displaying it?

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2006-02-20 : 10:11:16

"why wouldn't it display in a browser"

Old (very old!) version of ADO perhaps - used to have problems with large-ish string columns, particularly if they were not the last column in the SELECT statement.

Local variable not DIMensioned big enough and truncating the column?

But otherwise should just display "as is" the data from SQL server.

Query Analyser will cut it short if the OPTIONS are so configured - the default is to only displays 128 characters IIRC, but that won't effect application-display, obviously!

Kristen

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-02-20 : 10:38:10

when i use a text or ntext datatype, i can see the data through query analyzer just fine. however, when i display the data on the website, there is nothing.

i've tried to get around it with no success

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-02-20 : 10:52:12

show us some code.

Go with the flow & have fun! Else fight the flow

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-02-20 : 10:56:44

This is the asp tag in the body...
<%= RsAuctAdResults("AdText") %>

From this result set...

Set RsAuctAdResults = Server.CreateObject("ADODB.Recordset")
RsAuctAdResults.ActiveConnection = MM_AW_Connect_STRING
RsAuctAdResults.Source = "SELECT * FROM tblEvents, tblClient, tblAuctionAd WHERE tblEvents.ClientID = tblClient.ClientID AND tblEvents.OrderID = tblAuctionAD.OrderID AND tblEvents.OrderID = " & OrderID & " "
RsAuctAdResults.CursorType = 0
RsAuctAdResults.CursorLocation = 2
RsAuctAdResults.LockType = 1
RsAuctAdResults.Open()

If the datatype in the database is set to nvarchar, it displays the text nicely, but if you change it to text or ntext, there is nothing. no error message, it simply won't read it.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-02-20 : 11:10:27

what MDAC version do you have?
try just using the rs.Open(sql) without setting any properties.

change your SQL syntax into joins. it's more readable.

Go with the flow & have fun! Else fight the flow

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-02-20 : 11:20:17

I'm not sure what MDAC version I have or where to look actually.

Here is a better way of showing my sql statement..

"SELECT * FROM tblEvents LEFT JOIN tblClient ON tblEvents.ClientID = tblClient.ClientID LEFT JOIN tblAuctionAd ON tblEvents.OrderID = tblAuctionAd.OrderID WHERE EventDate > getdate()"

Getting text to display with certain datatypes is an issues i've been having for a while. i switched to varchar and just try to stay under the 8000 limit.

as far as my problem of not getting the result set for the entire row being too big, is there a way i can cut down one of the fields? Or can i tell it to only take the first 7000 characters of a field?

i know that isn't a great way to go about it but it would be a temporary fix to get it working until i come up with a better system.

thanks

Kristen
Test

22859 Posts

Posted - 2006-02-20 : 11:30:53

"can i tell it to only take the first 7000 characters of a field?"

SELECT LEFT(7000, MyTextColumn)
or
SELECT CONVERT(nvarchar(7000), MyTextColumn)

"I'm not sure what MDAC version I have or where to look actually."

This may provide some of the relevant info:


sub fnVersionADO()

Response.Write("<hr><h1>ADO Data Source Driver</h1>")
Response.Write("<table border=1>")
on error resume next
With dbConn		' dbConn is a "global" for the Database Connection
	Response.Write("<tr><td>Version</td><td>" & .Version & "</td></tr>")
	Response.Write("<tr><td>Timeout</td><td>" & .CommandTimeout & "</td></tr>")
	Response.Write("<tr><td>Connection String</td><td>" & .ConnectionString & "</td></tr>")
	Response.Write("<tr><td>Connection Timeout</td><td>" & .ConnectionTimeout & "</td></tr>")
	Response.Write("<tr><td>Cursor Location</td><td>" & .CursorLocation & "</td></tr>")
	Response.Write("<tr><td>Default Database</td><td>" & .DefaultDatabase & "</td></tr>")
	Response.Write("<tr><td>Isolation Level</td><td>" & .IsolationLevel & "</td></tr>")
	Response.Write("<tr><td>Mode</td><td>" & .Mode & "</td></tr>")
	Response.Write("<tr><td>Provider</td><td>" & .Provider & "</td></tr>")
	Response.Write("<tr><td>State</td><td>" & .State & "</td></tr>")
End With
on error goto 0
Response.Write("</table>")
Response.Write fnADOCommandPropertyDump(dbConn)
Response.Write("<H3>ADO Errors</H3>")
Response.Write("Errors:" & dbConn.Errors.count & "<br>")
If dbConn.Errors.count >= 1 Then
	Response.Write("<table border=1>")
	Response.Write("<tr><TH>Name</TH><TH>Value</TH></tr>")
	For Each objProperty In dbConn.Errors
		Response.Write("<tr><td>" & objProperty.name & "</td><td>" & objProperty.Value & "</td></tr>")
	Next
	Response.Write("</table><hr>")
End If
end sub
' -----------------------

Function fnADOCommandPropertyDump(objCmd)
Dim objProperty
Dim strContent

strContent = "<TABLE><tr><th>Name</th><th>Value</th></tr>"
For Each objProperty In objCmd.Properties
	strContent = strContent & "<tr><td>" & objProperty.name & "</td><td>" & objProperty.Value & "</td></tr>"
Next
strContent = strContent & "</table>"
fnADOCommandPropertyDump = strContent
End Function

Kristen

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-02-20 : 12:53:14

I'll try that out, thanks

oahu9872
Posting Yak Master

112 Posts

Posted - 2006-02-20 : 13:15:05

After messing with a few things, what worked the best was adding an extra column to my table and eliminating one of the table joins. Thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources