Hello,

I am trying to alter a table , in this task i need to alter some columns in order to set the default value to -1 or 1 , I tried the following methodology and it never worked , please help me to get the syntax right.

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TestEnvironment]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
begin

ALTER TABLE [TestEnvironment] alter column [Name] varchar(100) not null
ALTER TABLE [TestEnvironment] alter column [Other] varchar(500) not null
ALTER TABLE [TestEnvironment] alter column [SerMinNumProcs]
smallint not null DEFAULT 1
ALTER TABLE [TestEnvironment] alter column [SerRAMID] SET DEFAULT -1 int
not null
ALTER TABLE [TestEnvironment] alter column [SerHDID] int not
null DEFAULT -1
ALTER TABLE [TestEnvironment] alter column [CliMinNumProcs] smallint not null DEFAULT 1

ALTER TABLE [TestEnvironment] alter column [CliRAMID] int
not null DEFAULT -1
ALTER TABLE [TestEnvironment] alter column [CliHDID] int
not null DEFAULT -1
end
GO

thanking you

regards
Sasidhar

thanks,
Sasidhar

__________________
thanks,
Sasidhar

if any of your columns already have a defaults constraints you must first drop the constraints before altering the column.

Jaime E. Maccou

Thank you very much I was able to solve this issue , I have done the following...

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestEnvironment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin

    ALTER TABLE [TestEnvironment] alter column [Name] varchar(100) not null
    ALTER TABLE [TestEnvironment] alter column [Other] varchar(500) not null
    ALTER TABLE [TestEnvironment] ADD CONSTRAINT DF_TENV_SerMinNumProcs DEFAULT 1 FOR SerMinNumProcs
    ALTER TABLE [TestEnvironment] ADD CONSTRAINT DF_TENV_SerRAMID DEFAULT -1 FOR SerRAMID
    ALTER TABLE [TestEnvironment] ADD CONSTRAINT DF_TENV_SerHDID DEFAULT -1 FOR SerHDID
    ALTER TABLE [TestEnvironment] ADD CONSTRAINT DF_TENV_CliMinNumProcs DEFAULT 1 FOR CliMinNumProcs
    ALTER TABLE [TestEnvironment] ADD CONSTRAINT DF_TENV_CliRAMID DEFAULT -1 FOR CliRAMID
    ALTER TABLE [TestEnvironment] ADD CONSTRAINT DF_TENV_CliHDID DEFAULT -1 FOR CliHDID

 end
GO

regards
Sasidhar

thanks,
Sasidhar