Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 17th, 2005, 02:07 AM
Authorized User
 
Join Date: Dec 2003
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to sasidhar79
Default Alter Column - Default Value

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
 
Old January 17th, 2005, 10:50 AM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

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

Jaime E. Maccou
 
Old January 18th, 2005, 07:30 AM
Authorized User
 
Join Date: Dec 2003
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to sasidhar79
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
alter add puzzle: Invalid column name JL1024 SQL Server 2000 11 June 11th, 2013 04:59 PM
Alter Column SQLScott SQL Server 2005 5 January 5th, 2007 11:53 AM
Alter a column as identity for table kasanar SQL Server 2000 5 July 6th, 2005 08:44 AM
Alter Default on a Column Gert SQL Server 2000 4 November 28th, 2003 08:58 AM
Alter Table - Add Column liz@trinityholdings.co.za Access 2 July 29th, 2003 03:06 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.