Looking to the MSDN help http://msdn2.microsoft.com/en-us/library/ms174123(SQL.100).aspx (Same topic I have in local MSDN Library) I could not understand why following commands did not work:
alter table [Albums] alter column [Comment] set default 'default';
alter table [Albums] alter column [Comment] set not null;
I was getting error message:
Incorrect syntax near the keyword 'set'.
First I found this solution for default:
alter table [Albums] add constraint DF_Albumns_Comment default 'default' for [Comment];
constraint name is optional;
For not null similar solution:
alter table [Albums] add constraint NN_Albumns_Comment check ([Comment] is not null);
produces slightly different result. This new constraint is added over existing table definitions. The definition for column [Comment] is that it still allows nulls.
But then I noticed that online help was in fact for SQL 2008 compact edition, and later I found updated version (February 2008) for SQL 2005
http://msdn2.microsoft.com/en-us/library/ms190273.aspx
Now ther is no option for default here, so solution with new constraint should be used. But for not null constraint I can use this:
alter table [Albums] alter column [Comment] varchar(30) not null;
And no mistery.
Here I will post problems I and my colleagues met and solutions we found.
Tuesday, February 26, 2008
Alter column set default doesn't work in T-SQL 2005
Subscribe to:
Post Comments (Atom)
1 comments:
maybe this script works for u, keep in mind to alter the default values to your needs
DECLARE BY_CURSOR CURSOR FOR SELECT ( SELECT TOP 1 obj.[name] FROM [syscolumns] col JOIN [sysobjects] obj ON obj.[id] = col.[id] WHERE obj.type = 'U' AND col.id = o.id ),o2.name FROM syscolumns c JOIN sysobjects o ON c.id = o.id JOIN sysobjects o2 ON c.cdefault = o2.id WHERE c.NAME = 'CreatedBy_SID'
DECLARE ON_CURSOR CURSOR FOR SELECT ( SELECT TOP 1 obj.[name] FROM [syscolumns] col JOIN [sysobjects] obj ON obj.[id] = col.[id] WHERE obj.type = 'U' AND col.id = o.id ),o2.name FROM syscolumns c JOIN sysobjects o ON c.id = o.id JOIN sysobjects o2 ON c.cdefault = o2.id WHERE c.NAME = 'CreationDate_SID'
DECLARE @CONSTRAINT VARCHAR(4000)
DECLARE @TABLE VARCHAR(4000)
OPEN BY_CURSOR
FETCH NEXT FROM BY_CURSOR INTO @TABLE, @CONSTRAINT
WHILE @@FETCH_STATUS >= 0
BEGIN
EXEC( 'ALTER TABLE ' + @TABLE+ ' DROP CONSTRAINT ' + @CONSTRAINT )
EXEC( 'ALTER TABLE ' + @TABLE+ ' ADD CONSTRAINT ' + @CONSTRAINT + ' DEFAULT ([dbo].[FBAS_UsersID](suser_name())) FOR CreatedBy_SID' )
FETCH NEXT FROM BY_CURSOR INTO @TABLE, @CONSTRAINT
END
CLOSE BY_CURSOR
DEALLOCATE BY_CURSOR
OPEN ON_CURSOR
FETCH NEXT FROM ON_CURSOR INTO @TABLE, @CONSTRAINT
WHILE @@FETCH_STATUS >= 0
BEGIN
EXEC( 'ALTER TABLE ' + @TABLE+ ' DROP CONSTRAINT ' + @CONSTRAINT )
EXEC( 'ALTER TABLE ' + @TABLE+ ' ADD CONSTRAINT ' + @CONSTRAINT + ' DEFAULT ([dbo].[FDate_DateTime_2_ISO](getdate())) FOR CreationDate_SID' )
FETCH NEXT FROM ON_CURSOR INTO @TABLE, @CONSTRAINT
END
CLOSE ON_CURSOR
DEALLOCATE ON_CURSOR
Post a Comment