Cannot SET DEFAULT in "alter column" clause

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

SQL Server 2000 Forums

Transact-SQL (2000)

Cannot SET DEFAULT in "alter column" clause

Author

Topic

KarlKaiser
Starting Member

2 Posts

Posted - 2006-05-16 : 16:39:14

Well here's one of those excruciatingly simple obstacles:

In SQL Server 2005 (Mgmt Studio): according to BOL, the syntax to set a default value for an existing column is:

ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'

However, when I Check:

alter table CommissionPayment alter column Amount Set Default 0

I get the error message:

"Incorrect syntax near the keyword 'Set'."

No other combinations of this syntax work.

Help! What am I missing?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-16 : 16:54:55

Here's how I do it in 2000:

ALTER TABLE dbo.MyCustomers ADD CONSTRAINT ConstraintName DEFAULT 'A. Datum Corporation' FOR CompanyName

Tara Kizer
aka tduggan

KarlKaiser
Starting Member

2 Posts

Posted - 2006-05-16 : 17:00:52

Well thanks! That worked. Too bad the BOL is wrong on this one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources