CODEWITHSUNDEEP
sql-serversql-server-2008
Pradeep
Pradeep

Reputation: 1252

How to Alter Column from nvarchar(max) to nvarchar(50)

I have an existing table in SQL SERVER 2008 with one of its column as NVARCHAR(MAX) and it only has values of less than 10 characters in it. This table is in production and has data in it.

I have got a requirement wherein I have to Alter this column from NVARCHAR(MAX) to NVARCHAR(50). The SQL Server gives some Truncation error while doing this operation, even though the data in that column is less than 10 characters.

This is my script:

ALTER TABLE [dbo].[Table] ALTER COLUMN [Column1] NVARCHAR ( 50 ) NOT NULL

Upvotes: 5

Views: 13914

Answers (2)

Cetin Basoz
Cetin Basoz

Reputation: 23827

If you are sure that you wouldn't lose data, then:

Update myTable set myNVMaxCol = left(coalesce(myNVMaxCol,''),50);
Alter table myTable alter column myNVMaxCol nvarchar(50) not null;

Upvotes: 3

user6503890
user6503890

Reputation:

First Check Your table data with this query:

SELECT DATALENGTH(Column_Name) AS FIELDSIZE, Column_Name 
FROM Table_Name

If everything is fine, you may have checked the Prevent Saving Changes option. Follow these steps to check:

Tools > Designers Uncheck Prevent saving changes that require table re-creation

Upvotes: 5

Related Questions