Maximum Rowsize screw up by MSSQL 2000

I've got a table that has too many fields to fit on one page ( So i get that
warning when adding fields ).
Most of those fields were added after the creation of the table.
When I try to edit a record, I get the maxrowsize error.
When I try to add a new record to this table with only one field filled in,
I get the maxrowsize error.
So now I change one field ( from varchar 50 -> varchar 51 ) via Entreprise
Manager.
All changes via Entreprise Manager do a drop and recreate of a table.
Then I can edit the existing records and add new records to it without a
problem.
Is there a difference for MSSQL of you add fields <-> creating the table in
one piece?
Is this a known problem/bug ? How can I avoid this in the future ?
PS : Still awaiting answer from customer on SP-version installed there but
would be at least 3 because it's on Win2K3.
Regards,
Sven Peeters
BelgiumSven
Don't use EM . Perfom such operation in the QA
CREATE TABLE eee
(
col1 VARCHAR(4000),
col2 VARCHAR(4000),
col3 VARCHAR(1000)
)
--Warning: The table 'eee' has been
--created but its maximum row size (9027)
-- 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.
If you deal with large amount of data you might consider using TEXT datatype
instead.
CREATE TABLE eee11
(
col1 TEXT,
col2 TEXT,
col3 VARCHAR(1000)
)
DROP TABLE eee,eee11
"Sven Peeters" <SvenPeeters@.discussions.microsoft.com> wrote in message
news:281B4941-F6EE-4098-A7E1-DCA82C039B36@.microsoft.com...
> I've got a table that has too many fields to fit on one page ( So i get
> that
> warning when adding fields ).
> Most of those fields were added after the creation of the table.
> When I try to edit a record, I get the maxrowsize error.
> When I try to add a new record to this table with only one field filled
> in,
> I get the maxrowsize error.
> So now I change one field ( from varchar 50 -> varchar 51 ) via Entreprise
> Manager.
> All changes via Entreprise Manager do a drop and recreate of a table.
> Then I can edit the existing records and add new records to it without a
> problem.
> Is there a difference for MSSQL of you add fields <-> creating the table
> in
> one piece?
> Is this a known problem/bug ? How can I avoid this in the future ?
> PS : Still awaiting answer from customer on SP-version installed there but
> would be at least 3 because it's on Win2K3.
> Regards,
> Sven Peeters
> Belgium|||That's not the problem, most fields were added by T-SQL command.
Cannot change/add records to this table ( which has data below the maxsize )
.
I change one field ( make it even bigger ) in EM and after that I can
change/add records again.
That's the same table and data, only difference is that the table was
dropped and recreated ( with data ).
"Uri Dimant" wrote:

> Sven
> Don't use EM . Perfom such operation in the QA
>
> CREATE TABLE eee
> (
> col1 VARCHAR(4000),
> col2 VARCHAR(4000),
> col3 VARCHAR(1000)
> )
> --Warning: The table 'eee' has been
> --created but its maximum row size (9027)
> -- 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.
>
> If you deal with large amount of data you might consider using TEXT dataty
pe
> instead.
> CREATE TABLE eee11
> (
> col1 TEXT,
> col2 TEXT,
> col3 VARCHAR(1000)
> )
>
> DROP TABLE eee,eee11
>
> "Sven Peeters" <SvenPeeters@.discussions.microsoft.com> wrote in message
> news:281B4941-F6EE-4098-A7E1-DCA82C039B36@.microsoft.com...
>
>|||PS : SQL Version : 8.00.760 (sp3)
"Sven Peeters" wrote:

> I've got a table that has too many fields to fit on one page ( So i get th
at
> warning when adding fields ).
> Most of those fields were added after the creation of the table.
> When I try to edit a record, I get the maxrowsize error.
> When I try to add a new record to this table with only one field filled in
,
> I get the maxrowsize error.
> So now I change one field ( from varchar 50 -> varchar 51 ) via Entreprise
> Manager.
> All changes via Entreprise Manager do a drop and recreate of a table.
> Then I can edit the existing records and add new records to it without a
> problem.
> Is there a difference for MSSQL of you add fields <-> creating the table i
n
> one piece?
> Is this a known problem/bug ? How can I avoid this in the future ?
> PS : Still awaiting answer from customer on SP-version installed there but
> would be at least 3 because it's on Win2K3.
> Regards,
> Sven Peeters
> Belgium