Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

To add a defualt value to an existing column.

SQL Server 2005

Please rectify syntax error in this command.
alter table budget_det alter column line_item set default 0

Open in new window

0
Mehram
Asked:
Mehram
  • 6
  • 4
  • 2
  • +1
3 Solutions
 
pcelbaCommented:
alter table budget_det ADD default 0 FOR  line_item
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this will do:
alter table budget_det add constraint DF_line_item default 0 for line_item 

Open in new window

0
 
RiteshShahCommented:
alter table Mytable add constraint DF_X default (1) for MyColumn
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MehramAuthor Commented:
Hi experts

your codes are working.

I was confused by reading help which says like this



ALTER TABLE table_name
{ [ ALTER COLUMN column_name
   {DROP DEFAULT
   | SET DEFAULT constant_expression
   | IDENTITY [ ( seed , increment )


0
 
pcelbaCommented:
My help does not say it :-). What SQL version do you use?
0
 
MehramAuthor Commented:
Hi pcelba

This is what is written in the about my opened help

Microsoft SQL Server 2005 Books Online
© 2006 Microsoft Corporation.
All Rights Reserved.


and the url
ms-help://MS.SQLCC.v9/MS.SQLMobile.v3.en/SSMProg3/html/5719d3e4-14db-4073-bed7-d08f39416a39.htm
0
 
MehramAuthor Commented:
How to correct my sql server help behaviour

select @@version
=Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2, v.2825) 

Open in new window

0
 
MehramAuthor Commented:

select @@version
=Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07
   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition 
on Windows NT 5.2 (Build 3790: Service Pack 2, v.2825) 

Open in new window

0
 
pcelbaCommented:
You are right but this help is for SQL Server Compact edition which has slightly different syntax from the SQL Server 2005.
0
 
MehramAuthor Commented:
Hi pcelba

How to use / install the correct help ( the syntax for which my sql server is working)
0
 
pcelbaCommented:
I would say you can either filter the help contents or you can decide to search on-line help on the internet and you don't need to install it.

Whereas my help is for SQL 2008 I've found your syntax on http://msdn.microsoft.com/en-us/library/ms174123.aspx using the Search option in my local help file.
0
 
RiteshShahCommented:
Mehram, don't worry your help file is ok. don't need to do anything as of now. however, I can see you have SP2 which you can update to SP3 few bugs in SP2 has been fixed along with some security enhancement.
0
 
MehramAuthor Commented:
Hi experts,

courtesy to your help,

I managed to understand my existing help files.

I should see help for transact sql and not mobile edition.



[ CONSTRAINT constraint_name ] 
{ 
    { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
                (column [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        [ WITH ( <index_option>[ , ...n ] ) ]
        [ ON { partition_scheme_name ( partition_column_name ... )
          | filegroup | "default" } ] 
    | FOREIGN KEY 
                ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
    | DEFAULT constant_expression FOR column [ WITH VALUES ] 
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now