Errors With ALTER TABLE/ALTER COLUMN 
Author Message
 Errors With ALTER TABLE/ALTER COLUMN

Hi,

I had this database, which was denormalized. It is now normalized, however I
have come to changing the column data types, where applicable they are int
and tinyint, i also set an identity column (called code) as a primary key,
when doing so i get the following error msg:

Warning: The table 'tab_people' has been created but its maximum row size
(9361) 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.

my sql (no pun) for this is:

/* change datatype */
alter table tab_people
alter column reference tinyint

/* apply primary key */
alter table tab_people
add primary key (code)

does anyone have any suggestions as to what the problem is here, and is it a
fatal problem?

thanks for your time,
Alex Wilson




Sat, 13 Mar 2004 21:07:36 GMT
 Errors With ALTER TABLE/ALTER COLUMN

As the error message indicates, the maximum row size is 8060 and your table
definition exceeds that limit. You might want to have a look at the column
definitions again and see if you really need those lengthy columns. Your
alternative is to replace columns (with widths such as: varchar(7000)) with
text columns if appropriate.
--
HTH,
Vyas
SQL Server FAQ, articles, code samples,

http://vyaskn.tripod.com/


Quote:
> Hi,

> I had this database, which was denormalized. It is now normalized, however
I
> have come to changing the column data types, where applicable they are int
> and tinyint, i also set an identity column (called code) as a primary key,
> when doing so i get the following error msg:

> Warning: The table 'tab_people' has been created but its maximum row size
> (9361) 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.

> my sql (no pun) for this is:

> /* change datatype */
> alter table tab_people
> alter column reference tinyint

> /* apply primary key */
> alter table tab_people
> add primary key (code)

> does anyone have any suggestions as to what the problem is here, and is it
a
> fatal problem?

> thanks for your time,
> Alex Wilson





Sat, 13 Mar 2004 21:27:18 GMT
 Errors With ALTER TABLE/ALTER COLUMN
This is a warning message, not a fatal error.  Apparently, there are some
variable length columns in the table and the sum of the maximum lengths
exceed 8060 bytes.  You will not have any problems unless you attempt to
insert/update a row exceeding 8060, in which case the statement will fail.

Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------


Quote:
> Hi,

> I had this database, which was denormalized. It is now normalized, however
I
> have come to changing the column data types, where applicable they are int
> and tinyint, i also set an identity column (called code) as a primary key,
> when doing so i get the following error msg:

> Warning: The table 'tab_people' has been created but its maximum row size
> (9361) 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.

> my sql (no pun) for this is:

> /* change datatype */
> alter table tab_people
> alter column reference tinyint

> /* apply primary key */
> alter table tab_people
> add primary key (code)

> does anyone have any suggestions as to what the problem is here, and is it
a
> fatal problem?

> thanks for your time,
> Alex Wilson





Sat, 13 Mar 2004 21:25:50 GMT
 Errors With ALTER TABLE/ALTER COLUMN
thanks guys for that !
BIG BIG HELP :)

all the best,
Alex

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 13 Mar 2004 22:19:56 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Alter table alter column

2. Alter table alter column text

3. Alter table Alter column problem.

4. ALTER TABLE ALTER COLUMN to set Default

5. multiple ALTER COLUMNs in one ALTER TABLE statement?

6. Alter Table - Alter Column - Set Default ?'s

7. Alter table alter column

8. Help with Alter Table Alter Column

9. Alter table alter column syntax

10. Alter Table Alter Column Syntax

11. ALTER TABLE ALTER COLUMN

12. alter table alter column


 
Powered by phpBB® Forum Software