Menu
I'm trying to alter the length of the column "Body" in table "Post" like this:
ALTER TABLE Post ALTER COLUMN Body nvarchar(8000) NOT NULL;
The column is defined as nvarchar(4000) and it gives me this error:
Msg 2717, Level 16, State 2, Line 1 The size (8000) given to the parameter 'Body' exceeds the maximum allowed (4000).
524
In generic terms, you use the ALTER TABLE command followed by the table name, then the MODIFY command followed by the column name and new type and size. Here is an example: ALTER TABLE tablename MODIFY columnname VARCHAR(20) ; The maximum width of the column is determined by the number in parentheses.
Msg 2717, Level 16, State 2, Line 1 The size (8000) given to the parameter 'Body' exceeds the maximum allowed (4000). You can't create a NVARCHAR(8000). If you need to go beyond 4,000 characters with the NVARCHAR data type, you need to use NVARCHAR(max).
In this case, you need to use ALTER TABLE statement to increase column size. ALTER TABLE table_name MODIFY column_name varchar(new_length); In the above command, you need to specify table_name whose column you want to modify, column_name of column whose length you want to change, and new_length, new size number.
nvarchar [ ( n | max ) ]n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB).
Use Max instead. If the column were declared as VARCHAR, 8000 would be ok.
ALTER TABLE Post ALTER COLUMN Body nvarchar(max) NOT NULL;
In case, if the column has any constraint key like default, check .. ext, First we have dropped the key constraint from the column than alter the size of the column and alter your constraint to the column. The below steps will help you.
Steps are,
> ALTER TABLE MESSAGE_INBOX_VIEWERS DROP CONSTRAINT DF_CONST_TEXT
> ALTER TABLE MESSAGE_INBOX_VIEWERS ALTER COLUMN TEXT NVARCHAR(MAX)
> ALTER TABLE MESSAGE_INBOX_VIEWERS ADD CONSTRAINT DF_CONST_TEXT DEFAULT('') FOR TEXT
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
