ALTER TABLE ALTER COLUMN question

Question

ALTER TABLE ALTER COLUMN question

Raymond van Laake

SSCarpal Tunnel

Points: 4212
More actions
August 27, 2010 at 6:20 am

#228374

Hi there,
I need a script to change a TINYINT (not null default 0) TO DECIMAL(6,3).
These are my results:
ALTER TABLE systeem ALTER COLUMN aantal_dagen_vooruit_in_te_plannen DECIMAL(6,3) NOT NULL DEFAULT 0
Incorrect syntax near the keyword 'DEFAULT'
ALTER TABLE systeem ALTER COLUMN aantal_dagen_vooruit_in_te_plannen DECIMAL(6,3) NOT NULL
Msg 5074, Level 16, State 1, Line 1
The object 'DF_systeem_aantal_dagen_vooruit_in_te_plannen1' is dependent on column 'aantal_dagen_vooruit_in_te_plannen'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN aantal_dagen_vooruit_in_te_plannen failed because one or more objects access this column.
Anyone?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

r.vanlaake-1086273 (8/27/2010)
Hi there,
I need a script to change a TINYINT (not null default 0) TO DECIMAL(6,3).
These are my results:
ALTER TABLE systeem ALTER COLUMN aantal_dagen_vooruit_in_te_plannen DECIMAL(6,3) NOT NULL DEFAULT 0
Incorrect syntax near the keyword 'DEFAULT'
ALTER TABLE systeem ALTER COLUMN aantal_dagen_vooruit_in_te_plannen DECIMAL(6,3) NOT NULL
Msg 5074, Level 16, State 1, Line 1
The object 'DF_systeem_aantal_dagen_vooruit_in_te_plannen1' is dependent on column 'aantal_dagen_vooruit_in_te_plannen'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN aantal_dagen_vooruit_in_te_plannen failed because one or more objects access this column.
Anyone?

You need to drop the existing default before changing the column. How does this work?

ALTER TABLE systeem DROP CONSTRAINT [DF_systeem_aantal_dagen_vooruit_in_te_plannen1]

GO

ALTER TABLE systeem ALTER COLUMN aantal_dagen_vooruit_in_te_plannen DECIMAL(6,3) NOT NULL DEFAULT (0)

GO

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Raymond van Laake SSCarpal Tunnel Points: 4212 More actions · Answer 2

Thanks... but it is not 100% working for me! The constraint gets dropped, but when I try to recreate the column I get the message:

[font="Courier New"]ALTER TABLE systeem ALTER COLUMN aantal_dagen_vooruit_in_te_plannen DECIMAL(6,3) NOT NULL DEFAULT 0

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'DEFAULT'.[/font]

When I do it without the DEFAULT 0 it does work....

[font="Courier New"]ALTER TABLE systeem ALTER COLUMN aantal_dagen_vooruit_in_te_plannen DECIMAL(6,3) NOT NULL

Command(s) completed successfully.[/font]

must I add the default seperately?

WayneS SSC Guru Points: 95409 More actions · Answer 3

I think the default needs parenthesis around the value:

DEFAULT (0), not DEFAULT 0

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Raymond van Laake SSCarpal Tunnel Points: 4212 More actions · Answer 4

Thanks again for y'r reply, but I already tried that without luck...

WayneS SSC Guru Points: 95409 More actions · Answer 5

After playing around with this a bit, yes it needs to be done separately. It can be combined on the CREATE TABLE, but not on the ALTER TABLE.

So:

ALTER TABLE systeem DROP CONSTRAINT [DF_systeem_aantal_dagen_vooruit_in_te_plannen1];

GO

ALTER TABLE systeem ALTER COLUMN aantal_dagen_vooruit_in_te_plannen DECIMAL(6,3) NOT NULL ;

GO

ALTER TABLE systeem ADD CONSTRAINT [DF_systeem_aantal_dagen_vooruit_in_te_plannen1] DEFAULT 0 FOR [aantal_dagen_vooruit_in_te_plannen];

GO

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Raymond van Laake SSCarpal Tunnel Points: 4212 More actions · Answer 6

Raymond van Laake

SSCarpal Tunnel

Points: 4212

August 30, 2010 at 4:13 am

#1213648

It's working!

Thanks a 10^6

Ray

jldavis5 SSC Rookie Points: 49 More actions · Answer 7

Actually if the field is to allow nulls it should be (0) but if it is to not allow nulls it should be ((0))

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Not true. You can wrap expressions in as many brackets as you like, makes no difference.

p.s. 3 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass