Altering linked tables

I have a table (in Access) that is linked to the SQL server, and I
need to add a column to it. I wrote the following:

ALTER TABLE Census
ADD COLUMN 'ActiveFacility' BIT;

and I get a syntax error that I do not know how to solve. The column
needs to contain yes/no data for each record.

Any help for a struggling newbie?
Thanks,

ChristineChristine (cbrandel@.rehabmanagement.com) writes:
> I have a table (in Access) that is linked to the SQL server, and I
> need to add a column to it. I wrote the following:
> ALTER TABLE Census
> ADD COLUMN 'ActiveFacility' BIT;
> and I get a syntax error that I do not know how to solve. The column
> needs to contain yes/no data for each record.
> Any help for a struggling newbie?

It is always wise to include the error message you get. In this case it
was very easy for me to reproduce the error, but sometimes this is
may be impossible with access to the database.

The best place to find out the syntax for a command, is to look it up
in Books Online, which comes with SQL Server. In the book T-SQL Syntax
Reference you find all T-SQL commands described with syntax diagrams.
Sure, the diagram for ALTER TABLE may seem unwieldy, since you can
alter a table in many ways. On the flip side, there are also examples
for the most common uses of the command.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

alter table set default value for money type column

I run the sql like the following
Alter table ItemStone add ISPurPrice money default 0
then when I select the itemStone table, I find the field ISPurPrice is still
Null, not 0, why?
I'm using SQL Server ver 8.0 (2000)
Thx!!
Kei,
Use the WITH VALUES option in your statement, or (probably better)
declare your new column as NOT NULL. Here are the choices:
Alter table ItemStone add ISPurPrice money NOT NULL default 0
Alter table ItemStone add ISPurPrice money default 0 WITH VALUES
From Books Online, topic ALTER TABLE:
WITH VALUES
Specifies that the value given in DEFAULT constant_expression is stored
in a new column added to existing rows. WITH VALUES can be specified
only when DEFAULT is specified in an ADD column clause. If the added
column allows null values and WITH VALUES is specified, the default
value is stored in the new column added to existing rows. If WITH VALUES
is not specified for columns that allow nulls, the value NULL is stored
in the new column in existing rows. If the new column does not allow
nulls, the default value is stored in new rows regardless of whether
WITH VALUES is specified.
Steve Kass
Drew University
kei wrote:

>I run the sql like the following
>Alter table ItemStone add ISPurPrice money default 0
>then when I select the itemStone table, I find the field ISPurPrice is still
>Null, not 0, why?
>I'm using SQL Server ver 8.0 (2000)
>Thx!!
>

Alter Column datatype with Default constraint

I need to alter the datatype of a column from smallint to decimal (14,2) but the column was originally created with the following:

alter my_table
add col_1 smallint Not Null
constraint df_my_table__col_1 default 0
go

I want to keep the default constraint, but i get errors when I try to do the following to alter the datatype:

alter table my_table
alter column col_1 decimal(14,2) Not Null
go

Do I need to drop the constraint before I alter the column and then rebuild the constraint? An example would be helpful.

Thxyes thats right,

the constraint has a dependency on the column and hence the data type of the column.

If you change the data type then you change the column and then this affects the constraint which SQL Server will not allow.

drop the constriant, then do what you need to do to the column

Cheers