How to change a column Size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to change a column Size

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2012-07-23 : 08:54:58

Hi

In a table called Order I have a column called Notes that currently shows

Notes (ntext, null)

If I wanted to change this to 200 characters what would the script be?

I tried ALTER TABLE Order MODIFY Notes varchar(200)

But I can't get it to work.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-23 : 09:11:02

[code]ALTER TABLE Order ~~MODIFY~~ ALTER COLUMN Notes varchar(200)[/code]

KH
[spoiler]Time is always against us[/spoiler]

deanglen
Yak Posting Veteran

65 Posts

Posted - 2012-07-23 : 10:09:15

Hi

Tried that but I think there are some columns where there are already more than 200 characters.

Msg 8152, Level 16, State 10, Line 1
String or binary data would be truncated.
The statement has been terminated.

So I need to alter those records that are 200 or more I guess?

I tried

SELECT *
FROM Order
WHERE LEN(Notes) >200;

But get this

Msg 8116, Level 16, State 1, Line 1
Argument data type ntext is invalid for argument 1 of len function.

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 10:12:28

use datalength


SELECT *
FROM Order
WHERE DATALENGTH(Notes) >200

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-05-03 : 06:12:19

Brilliant! thanks worked fine

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-03 : 06:18:43

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources