473,320 Members | 1,987 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Alter column datatype and default value in a table

i have a table which has 3 columns, one of three is set to default value 0. Now i have to change data type of that particular column and its default value using sql query. am working with sql server 2005. am not getting any prob when i exec ALTER TABLE TABLE_NAME ADD COLUMN [COLUMN_NAME] DATATYPE SIZE CONSTRAINT [CONSTRAINT_NAME] VALUE. but getting probs when exec
ALTER TABLE TABLE_NAME ALTER COLUMN_NAME DATATYPE SIZE [CONSTRAINT NAME] VALUE. Help me, thanx in advance
Sep 25 '07 #1
2 23385
azimmer
200 Expert 100+
i have a table which has 3 columns, one of three is set to default value 0. Now i have to change data type of that particular column and its default value using sql query. am working with sql server 2005. am not getting any prob when i exec ALTER TABLE TABLE_NAME ADD COLUMN [COLUMN_NAME] DATATYPE SIZE CONSTRAINT [CONSTRAINT_NAME] VALUE. but getting probs when exec
ALTER TABLE TABLE_NAME ALTER COLUMN_NAME DATATYPE SIZE [CONSTRAINT NAME] VALUE. Help me, thanx in advance
Are you sure your columns meet all the criteria described in the ALTER COLUMN notes at http://msdn2.microsoft.com/en-us/library/ms190273.aspx ?
Also not that if you modify the type and/or constraints of a column the data already in the column must meet the new settings.
Sep 25 '07 #2
alter table [tableName] alter column [columnName] [DataType]
Jul 22 '09 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Ken1 | last post by:
I am going to drop a primary key from one column and create a new column to be used as primary key in an existing database. The old column was a date column which someone earlier though was a good...
2
by: Richard Hollenbeck | last post by:
I made a table this way: mysql>create table tblCourses (courseCode char(30) primary key, courseDescription char(50)); Now I realize that I forgot to enforce that both fields will not be null....
3
by: M Simpson | last post by:
/* for the google index */ ALTER TABLE DEFAULT COLUMN DEFAULT VALUE I've worked out several stored procedures for altering the default column values in a table. They were compiled from books...
1
by: Bruce | last post by:
Hi, I want to change the datatype of an existing column from char to varbinary. When I run the "Alter Table" statement, I get the following error message - Disallowed implicit conversion...
2
by: me | last post by:
I would like to add an Identity to an existing column in a table using a stored procedure then add records to the table and then remove the identity after the records have been added or something...
4
by: Brian Brane | last post by:
I have properties that wrap DataRow columns as in: public int aNumber { get{ return m_DataRow; } set{ m_DataRow = value; } } If the column happens to contain DBNull, I get a cast exception...
3
by: Kimi | last post by:
Hi, I'm trying to change a column on a DB2 table so that it has a default value but I cannot get it to work. Does anyone have any experience in this area. I'm trying with this statement: ...
0
by: spatik | last post by:
Hi, I have a tableevent] with a columnevent_description] as ntext and default value ''. Now, I want to change the column type to nvarchar(max) using this script, alter table alter column ...
1
by: pulavarthipraveen | last post by:
When I tried to change the datatype of the column through "Alter table tablename Alter column columnname Newdatatype" query it's is throw the error below: "The index 'IndexName' is dependent on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.