syntax for ALTER TABLE ALTER COLUMN DEFAULT 
Author Message
 syntax for ALTER TABLE ALTER COLUMN DEFAULT
I am trying to change the default value on a column in a table using
sql query analyzer on SQL Server 2000.

This is what I am using:

Alter table dbo.QuoteRawMatCost alter column Increments numeric(18, 0)
NULL DEFAULT 1
GO

It returns:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DEFAULT'.

Thank You!!
Scott



Sat, 28 Jun 2003 23:38:02 GMT
 syntax for ALTER TABLE ALTER COLUMN DEFAULT

I'm not sure if ALTER TABLE will let you do this. But what you can do is,
execute sp_help on the table, get the default constraint's name, drop that
constraint with ALTER TABLE...DROP CONSTRAINT command. Then create a new
default using CREATE DEFAULT and bind it to the column using sp_bindefault
--
HTH,
Vyas
Check out my page for SQL Server, ASP, VB resources,Replication FAQ and
MORE:
http://vyaskn.tripod.com/index.htm
Review of SQL Server 2000's "Federated database servers" feature:
http://vyaskn.tripod.com/federated.htm
Interesting code samples (include SQL stored procedures, VB programs):
http://vyaskn.tripod.com/code.htm


Quote:
> I am trying to change the default value on a column in a table using
> sql query analyzer on SQL Server 2000.

> This is what I am using:

> Alter table dbo.QuoteRawMatCost alter column Increments numeric(18, 0)
> NULL DEFAULT 1
> GO

> It returns:

> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'DEFAULT'.

> Thank You!!
> Scott



Sun, 29 Jun 2003 00:24:27 GMT
 syntax for ALTER TABLE ALTER COLUMN DEFAULT
I for got to post this with the original post, but it does work
without the DEFAULT.  See below:

Alter table dbo.QuoteRawMatCost alter column Increments numeric(18, 0)
NULL
GO

Quote:

>I am trying to change the default value on a column in a table using
>sql query analyzer on SQL Server 2000.

>This is what I am using:

>Alter table dbo.QuoteRawMatCost alter column Increments numeric(18, 0)
>NULL DEFAULT 1
>GO

>It returns:

>Server: Msg 156, Level 15, State 1, Line 1
>Incorrect syntax near the keyword 'DEFAULT'.

>Thank You!!
>Scott



Sun, 29 Jun 2003 00:35:08 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Alter table alter column syntax

2. Alter Table Alter Column Syntax

3. Alter Table - Alter Column - Set Default ?'s

4. ALTER TABLE ALTER COLUMN to set Default

5. Syntax for using ALTER...ALTER COLUMN...

6. TSQL : ALTER COLUMN to NOT NULL with DEFAULT syntax error

7. Help with Alter Table Alter Column

8. Alter table alter column

9. multiple ALTER COLUMNs in one ALTER TABLE statement?

10. alter table alter column

11. Alter table alter column

12. ALTER TABLE/ALTER COLUMN problem with MS Access 2K


 
Powered by phpBB® Forum Software