Alter column problem

Silly me I thought this would be a simple thing to do. I want to set defaults for some fields using a stored procedure. My testing SP looks like: ------------SP Code------------------------ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE SPTemp    AS BEGIN ALTER TABLE [dbo].[Contact_tbl] ALTER COLUMN FirstName DEFAULT 'dddddd' END GO ---------------------------------------------- I have tried veriations with and without [] around field name, with and without SET These are the errors I am getting: Msg 156, Level 15, State 1, Procedure SPTemp, Line 8 Incorrect syntax near the keyword 'DEFAULT'. Using SET: ALTER TABLE [dbo].[Contact_tbl] ALTER COLUMN [FirstName] SET DEFAULT 'dddddd' Msg 156, Level 15, State 1, Procedure SPTemp, Line 8 Incorrect syntax near the keyword 'SET'. Example from http://technet.microsoft.com/en-us/library/ms174123.aspx ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation' Thanks for any help Rodney

Aren't the [brackets] just for when names have spaces or are keywords? Anyway, the syntax looks okay against that example... maybe try something like this: ALTER TABLE Contact_tbl MODIFY FirstName VARCHAR(50) DEFAULT 'dddddd' ? ----- Excess quoted text cut - see Original Post for more -----

That's my understanding of [] also, but I don't think they hurt (in most cases) if used when not needed And I was tring any veriation I could think of. Nope it don't like Modify either. ALTER TABLE [dbo].[Contact_tbl] MODIFY [FirstName] SET DEFAULT 'dddddd' GETS: Msg 102, Level 15, State 1, Procedure SPTemp, Line 8 Incorrect syntax near the keyword 'FirstName'. ALTER TABLE [dbo].[Contact_tbl] MODIFY FirstName SET DEFAULT 'dddddd' GETS: Msg 102, Level 15, State 1, Procedure SPTemp, Line 8 Incorrect syntax near the keyword 'MODIFY'. So there the [] make a difference, just not sure what or why. Thanks for the suggestion. Rodney Aren't the [brackets] just for when names have spaces or are keywords? Anyway, the syntax looks okay against that example... maybe try something like this: ALTER TABLE Contact_tbl MODIFY FirstName VARCHAR(50) DEFAULT 'dddddd' ?

Try this: ALTER TABLE  [dbo].[Contact_tbl] ALTER FirstName SET DEFAULT 'dddddd'; Hope this helps, Maya "Bruce, Rodney S Mr CTR USA AMC" <rodney.s.bruce1@us.army.mil> wrote: Silly me I thought this would be a simple thing to do. I want to set defaults for some fields using a stored procedure. My testing SP looks like: ------------SP Code------------------------ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE SPTemp AS BEGIN ALTER TABLE [dbo].[Contact_tbl] ALTER COLUMN FirstName DEFAULT 'dddddd' END GO ---------------------------------------------- I have tried veriations with and without [] around field name, with and without SET These are the errors I am getting: Msg 156, Level 15, State 1, Procedure SPTemp, Line 8 Incorrect syntax near the keyword 'DEFAULT'. Using SET: ALTER TABLE [dbo].[Contact_tbl] ALTER COLUMN [FirstName] SET DEFAULT 'dddddd' Msg 156, Level 15, State 1, Procedure SPTemp, Line 8 Incorrect syntax near the keyword 'SET'. Example from http://technet.microsoft.com/en-us/library/ms174123.aspx ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation' Thanks for any help Rodney

Thanks for suggection, but it didn't work either. Removing COLUMN changed the error message to: Msg 170, Level 15, State 1, Procedure SPTemp, Line 8 Incorrect syntax near the keyword 'FirstName '. The ";" doesn't seem to make a difference either way. Thanks Rodney Try this: ALTER TABLE  [dbo].[Contact_tbl] ALTER FirstName SET DEFAULT 'dddddd'; Hope this helps, Maya

ALTER TABLE Contact_tbl add default 'dddddd' for FirstName Tested and it should work if you currently don't have any defaults for this column. Maya "Bruce, Rodney S Mr CTR USA AMC" <rodney.s.bruce1@us.army.mil> wrote:   Thanks for suggection, but it didn't work either. Removing COLUMN changed the error message to: Msg 170, Level 15, State 1, Procedure SPTemp, Line 8 Incorrect syntax near the keyword 'FirstName '. The ";" doesn't seem to make a difference either way. Thanks Rodney Try this: ALTER TABLE  [dbo].[Contact_tbl] ALTER FirstName SET DEFAULT 'dddddd'; Hope this helps, Maya

Yes,  That worked Were did you find the syntex? I always seem to find: ALTER [COLUMN] column_name SET default_expr alters the default value for the column. As examples Thanks Rodney ALTER TABLE Contact_tbl add default 'dddddd' for FirstName Tested and it should work if you currently don't have any defaults for this column. Maya "Bruce, Rodney S Mr CTR USA AMC" <rodney.s.bruce1@us.army.mil> wrote:   Thanks for suggection, but it didn't work either. Removing COLUMN changed the error message to: Msg 170, Level 15, State 1, Procedure SPTemp, Line 8 Incorrect syntax near the keyword 'FirstName '. The ";" doesn't seem to make a difference either way. Thanks Rodney Try this: ALTER TABLE  [dbo].[Contact_tbl] ALTER FirstName SET DEFAULT 'dddddd'; Hope this helps, Maya

Here is url: http://msdn2.microsoft.com/en-US/library/aa237859(sql.80).aspx Maya "Bruce, Rodney S Mr CTR USA AMC" <rodney.s.bruce1@us.army.mil> wrote: Yes,   That worked Were did you find the syntex? I always seem to find: ALTER [COLUMN] column_name SET default_expr alters the default value for the column. As examples Thanks Rodney ALTER TABLE Contact_tbl add default 'dddddd' for FirstName Tested and it should work if you currently don't have any defaults for this column. Maya "Bruce, Rodney S Mr CTR USA AMC"  wrote:   Thanks for suggection, but it didn't work either. Removing COLUMN changed the error message to: Msg 170, Level 15, State 1, Procedure SPTemp, Line 8 Incorrect syntax near the keyword 'FirstName '. The ";" doesn't seem to make a difference either way. Thanks Rodney Try this: ALTER TABLE  [dbo].[Contact_tbl] ALTER FirstName SET DEFAULT 'dddddd'; Hope this helps, Maya

Thanks for the help Here is url: http://msdn2.microsoft.com/en-US/library/aa237859(sql.80).aspx Maya

Sorry, wrong url.  Here is a correct one: http://www.akadia.com/services/sqlsrv_table_auditing.html Maya "Bruce, Rodney S Mr CTR USA AMC" <rodney.s.bruce1@us.army.mil> wrote: Yes,   That worked Were did you find the syntex? I always seem to find: ALTER [COLUMN] column_name SET default_expr alters the default value for the column. As examples Thanks Rodney ALTER TABLE Contact_tbl add default 'dddddd' for FirstName Tested and it should work if you currently don't have any defaults for this column. Maya "Bruce, Rodney S Mr CTR USA AMC"  wrote:   Thanks for suggection, but it didn't work either. Removing COLUMN changed the error message to: Msg 170, Level 15, State 1, Procedure SPTemp, Line 8 Incorrect syntax near the keyword 'FirstName '. The ";" doesn't seem to make a difference either way. Thanks Rodney Try this: ALTER TABLE  [dbo].[Contact_tbl] ALTER FirstName SET DEFAULT 'dddddd'; Hope this helps, Maya