Alter Table Row Size Error

I am trying to alter a table and getting the following error.
Server: Msg 1701, Level 16, State 2, Line 1
Creation of table 'CustomerMaster' failed because the row size would be
8508, including internal overhead. This exceeds the maximum allowable table
row size, 8060.
I can create a new table with the same fields, but not alter an existing one.
I have calculated the maximum row size as only 6004 after the changes. Here
is what I used to calculate.
69 columns
39 char fields (5816 total characters)
9 tinyint fields (size = 9 * 1 = 9)
4 int fields (size = 4 * 4 = 16)
5 datetime fields (size = 5 * 8 = 40)
12 decimal 19,5 fields (size = 12 * 9 = 108)
Num_Cols = 69
Fixed_Data_Size = 5989
Num_Var_Cols = 0
Max_Var_Size = 0
Null_Bitmap = 2 + ((69 + 7 ) / 8 = 11.5
Row_Size = 5981 + 0 + 11 + 4 = 6004
> 39 char fields (5816 total characters)
What are the definitions of these columns? Are they
CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?

> 9 tinyint fields (size = 9 * 1 = 9)
> 4 int fields (size = 4 * 4 = 16)
> 5 datetime fields (size = 5 * 8 = 40)
> 12 decimal 19,5 fields (size = 12 * 9 = 108)
Are any of these NULLable?
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> Are any of these NULLable?
>
>
39 CHAR fields
field length - field count
3 - 2
5 - 3
10 - 4
15 - 1
20 - 11
25 - 2
30 - 11
45 - 2
50 - 1
2000 - 1
3000 - 1
total characters = 5816
all fields are NULLABLE but one CHAR field (in my calculations I considered
all fields as nullable)
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> Are any of these NULLable?
>
>
I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
calculate my row size.
|||Are you certain they're all CHAR and none are NCHAR or NVARCHAR? Can you
post the results of:
EXEC sp_help 'tablename';
?
Why would you have a CHAR(2000) or CHAR(3000)? Is every row always going to
be 3000 characters?
"Matt Soukup" <MattSoukup@.discussions.microsoft.com> wrote in message
news:F6ADF63E-E3FD-4D8E-88C7-8D1B1C4F2D22@.microsoft.com...
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
> 39 CHAR fields
> field length - field count
> 3 - 2
> 5 - 3
> 10 - 4
> 15 - 1
> 20 - 11
> 25 - 2
> 30 - 11
> 45 - 2
> 50 - 1
> 2000 - 1
> 3000 - 1
> total characters = 5816
> all fields are NULLABLE but one CHAR field (in my calculations I
> considered
> all fields as nullable)
|||> I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
> calculate my row size.
Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
actually NCHAR(3000), and used the former in your calculations, it doesn't
really matter how accurate the calculation is, the source input invalidates
it.
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
> actually NCHAR(3000), and used the former in your calculations, it doesn't
> really matter how accurate the calculation is, the source input invalidates
> it.
>
>
all character fields are CHAR(####)
Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
Here is a list of the fields:
Customer_Numberchar(20)
CUSTNMBRchar(45)
VENDORIDchar(45)
Attn_Namechar(30)
Contact_Namechar(30)
ContactPhonechar(20)
BusinessNamechar(30)
AddressLine1char(30)
AddressLine2char(30)
Citychar(25)
Statechar(3)
ZipCodechar(10)
Phonechar(20)
Faxchar(20)
ICC_Numberchar(20)
FedID_Numberchar(15)
Contractedtinyint
Start_Datedatetime
InsAgentNamechar(30)
InsAgentAddress1char(30)
InsAgentAddress2char(30)
InsAgentCitychar(25)
InsAgentStatechar(3)
InsAgentZipCodechar(10)
InsAgentPhonechar(20)
InsAgentFaxchar(20)
InsAgentContactchar(30)
CargoInsNamechar(28)
CargoInsAmountdecimal(19, 5)
CargoInsExpirationDatedatetime
AutoLibInsNamechar(30)
AutoLibInsAmountdecimal(19, 5)
AutoLibInsExpirationDatedatetime
Agent_YNtinyint
Broker_YNtinyint
Carrier_YNtinyint
Shipper_YNtinyint
Consignee_YNtinyint
BillTo_YNtinyint
CreatedDatedatetime
CreatedUserIDchar(20)
Billing_Ratedecimal(19, 5)
Billing_Rate_Codechar(5)
Ratingchar(5)
Colorchar(20)
Flaggedtinyint
FlagDatedatetime
FlagUserIDchar(20)
FlagReasonchar(50)
Activetinyint
Noteschar(3000)
PayCodechar(5)
PayRateFlatdecimal(19, 5)
PayRatePercentdecimal(19, 5)
PayRateLoadeddecimal(19, 5)
PayRateUnloadeddecimal(19, 5)
DefaultSalespersonchar(20)
Directionschar(2000)
FSTypechar(10)
FSRateAmountdecimal(19, 5)
Weightdecimal(19, 5)
AgentPayAccountint
CarrierPayAccountint
DropPayAmountdecimal(19, 5)
PickupPayAmountdecimal(19, 5)
ISTypechar(10)
ISRateAmountdecimal(19, 5)
Latitudeint
Longitudeint
|||Sorry, but there is still information missing. I don't want to see your
compiled list of fields. Can you please copy and paste the result of:
EXEC sp_help 'CustomerMaster';
? Otherwise, I have no further input on this issue. The list of columns
you provided below can't possibly be 8508 bytes unless (a) the column you're
trying to add is a lot longer than you're letting on, (b) some of those data
types are not correct, or (c) you've left out some columns.

> all character fields are CHAR(####)
> Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
> Here is a list of the fields:
> Customer_Number char(20)
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Sorry, but there is still information missing. I don't want to see your
> compiled list of fields. Can you please copy and paste the result of:
> EXEC sp_help 'CustomerMaster';
> ? Otherwise, I have no further input on this issue. The list of columns
> you provided below can't possibly be 8508 bytes unless (a) the column you're
> trying to add is a lot longer than you're letting on, (b) some of those data
> types are not correct, or (c) you've left out some columns.
>
>
>
>
Just forget it. SQL is being stupid.
This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
I can create this table from scratch with the columns/sizes I listed above.
The table is created with no problem. I have to up the size of one of my CHAR
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.
|||> Just forget it. SQL is being stupid.
I am willing to bet that it is not. But I can't prove it unless you post an
honest result from EXEC sp_help.

> This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
Is it possible this column is involved in a foreign key relationship, and
you are trying to implement the change through the GUI?
A