Hi there, I'm not very familiar 1

[Faheem786]

Hi there,

I'm not very familiar with the SQL server, but I heard that the SQL server can even store values in a field around GBs. I got a problem while I created the DB with the following warning.



########################################################
The table 'betatest' has been created but its maximum
row size (14380)exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail
if the resulting row length exceeds 8060 bytes.
########################################################


It's quite mysterious that the row size cannot exceed 8060 bytes.

Again I got the error message while I tried to submit the form using the browser.


########################################################
Microsoft OLE DB Provider for SQL Server error '80040e14'

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


I'm in a siuation that I need to allow my clients to enter more than 8060 bytes. Could you experts pls help me to get rid of this problem?

Thanks in advance.
Faheem

 

[Frano]

Yes, SQL Server can store data (image and text) up to 2GB for each element. However, as you have found out it has a row size limit of 8060 bytes.

These statements are not contradictory, because SQL only stores a pointer to image and text fields in a row.

Hope this helps.

Steve Francis

 

[Faheem786]

Franco

Thanks for your answer.

But what could I do if I would like to store more than 8060 bytes in a single row? I have some textarea fields in the form where our client could write a reveiew about our products which needed to allow the client to enter more than 2000 char. ( I have around 8 fileds like this =16K).

Pls find me a solution for this. It's amazing that SQL server is limited with this.

Thanks in advance
Faheem

 

[tlbroadbent]

There should be no mystery about the row size limit. It is well documented. The max row size is set at a page size (8K)less some overhead. This is done for processing efficiency.

As Steve Francis mentioned in his post, you can use TEXT or NTEXT data type to hold more data. So the quick answer is to convert the data type from a varchar (nvarchar) to text or ntext.

Another option is to redesign the table such that each comment or text field is numbered and stored on a separate record in a table. The structure would change as illustrated below.

Current:

ID Text1 Text2 Text3 Text4 Text5 Text6 Text7 Text8

Proposed:

ID No Text

I would choose this option if designing a similar database. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.