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?? DEFAULT is not a column property in T-SQL -- it's actually implemented as a constraint. The correct syntax is: CREATE TABLE #x (id int) ALTER TABLE #x ADD CONSTRAINT DF_id DEFAULT (0) FOR id Where in BOL did you see that syntax? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Karl Kaiser@.discussions.microsoft.com> wrote in message news:a5caf4ff-8884-4383-960f-b4f61aad2da2@.discussions.microsoft.com...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:[b]ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'[/b]However, when I Check:[b]alter table CommissionPayment alter column Amount Set Default 0[/b]I get the error message:[b]"Incorrect syntax near the keyword 'Set'."[/b]No other combinations of this syntax work.Help! What am I missing?|||The syntax you saw was for SQL Server Mobile Edition. It is not applicable for the other SQL Server editions. So the correct way is to add default as a constraint using syntax posted by Adam.
