October 2, 2007 at 6:57 am
I took the following syntax from BOL
ALTER TABLE MyCustomers
ALTER COLUMN CompanyName
SET DEFAULT 'A. Datum Corporation'
And when I put my information
ALTER TABLE bvc_Affiliate
ALTER COLUMN [DisplayName]
Set DEFAULT NULL
I get the following error
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'Set'.
Does anyone know why this would happen?
October 2, 2007 at 7:08 am
Pam,
Can you provide the link to BOL? I am interested to know where you found the syntax.
However, to answer your question, you can use the following syntax:
ALTER TABLE bvc_Affiliate
ADD CONSTRAINT DF_myDefaultConstraint DEFAULT('This is a test') FOR [DisplayName]
Please check that there is no default bound to the column prior to running the DDL.
Regards,
Wameng Vang
MCTS
October 2, 2007 at 7:12 am
Here is the link
ms-help://MS.SQLCC.v9/MS.SQLMobile.v3.en/SSMProg3/html/5719d3e4-14db-4073-bed7-d08f39416a39.htm.
One more question, What if I want to change a column to allow nulls that currently does not? There are no foreign keys against the column.
October 2, 2007 at 8:21 am
You want to alter the column to allow nulls, not a default of null. Run these one at a time. The first insert of null should error out.
create table MyTest
( myid int not null
, mychar varchar(20) not null
)
go
insert MyTest select 1, 'A'
insert MyTest select 2, 'B'
insert MyTest select 3, 'C'
insert MyTest select 4, 'D'
go
select * from MyTest
go
insert MyTest select null, 'E'
go
alter table MyTest
alter column myid int null
go
insert MyTest select null, 'E'
go
select * from MyTest
go
drop table MyTest
October 2, 2007 at 8:55 am
Pam,
I could not reach the SQL Server Mobile documentation. However, I was able to locate the example syntax that you've provided. It is for SQL Server CE Edition. See:
http://msdn2.microsoft.com/en-us/library/aa237859(SQL.80).aspx
Base on the error, which you have provided, I do not think you have SQL Server CE Edition. Please keep in mind to search for documentation with the correct version of SQL Server. This will be helpful for you in future use.
Regards,
Wameng Vang
MCTS
October 2, 2007 at 9:09 am
ALTER TABLE bvc_Affiliate
ALTER COLUMN [DisplayName] NULL -- allow nulls
ALTER TABLE bvc_Affiliate
ALTER COLUMN [DisplayName] NOT NULL -- do not allow them
* Noel
October 2, 2007 at 9:12 am
Thanks All
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply