Go Premium for a chance to win a PS4. Enter to Win

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

Changing the data type of a column

Experts,

I am updating a column datatype integer to varchar(3) with the following script

ALTER TABLE mythirdTable
            ALTER COLUMN [Column2] [varchar] (3) NOT NULL


after changiging the column type,  i am inserting values in to my table and still getting the error related to the data type of the column I have changed. Can you please help why this is happening or if I am doing anything wrong?

INSERT INTO mythirdTable
(Column1,Column2,Column3,Column4,Column5)
VALUES
('3Jh45','2G8' ,'Test',10,4)


Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '2G8' to a column of data type int.

Thanks
0
Tpaul_10
Asked:
Tpaul_10
3 Solutions
 
SweatCoderCommented:
Try this instead:

ALTER TABLE mythirdTable
          ALTER COLUMN [Column2] varchar(3) NOT NULL
0
 
LandyJCommented:
The command should be;

ALTER TABLE mythirdTable
          ALTER COLUMN [Column2] varchar(3) NOT NULL
0
 
SweatCoderCommented:
better yet:

ALTER TABLE mythirdTable
          ALTER COLUMN [Column2] char(3) NOT NULL

for small, normally-fixed number of chars, use "char" instead of "varchar" for optimization purposes.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Tpaul_10Author Commented:
Thanks for helping me out guys and I am trying to change data type for another column to the same table. But getting the follwing error

Server: Msg 5074, Level 16, State 1, Line 11
The object 'DF_myThirdTable_Column5' is dependent on column 'Column5'.
Server: Msg 4922, Level 16, State 1, Line 11
ALTER TABLE ALTER COLUMN column5 failed because one or more objects access this column.

I ran to sp_help to see what's this and here are the details

Constraint_type : DEFAULT on column Column5
Constraint_name : DF_myThirdTable_Column5
delete_action : n/a
update_action : n/a
status_enables : n/a
status_for_replication : n/a
Constraint_keys : (0)


so can i use the following script (wich is incomplete)

==================================================================
ALTER TABLE myThirdtable
DROP CONSTRAINT DF_myThirdTable_Column5
==================================================================ALTER TABLE FactorOccupancyTerritoryGroup
            ALTER COLUMN [column5] varchar (3) NOT NULL

================================================================

I am not sure how to add it back once I drop the constraint which has a defaulue as "0"

ALTER TABLE myThirdtable WITH NOCHECK ADD             
      CONSTRAINT [DF_myThirdTable_Column5]
      (
            [Column5]

      ) WITH  FILLFACTOR = 90  ON ..???

can you please tell me or correct my code to add it back the default constraint?

Thanks again for all your wonderful help

0
 
SweatCoderCommented:
If you ever want to know how to script an existing object, go into Enterprise Manager, right-click on the DB, and choose the "script" option - then you can choose in a granular way exactly which objects you want to script.
0
 
imran_fastCommented:
ALTER TABLE myThirdtable
DROP CONSTRAINT DF_myThirdTable_Column5

FactorOccupancyTerritoryGroup
          ALTER COLUMN [column5] varchar (3) NOT NULL

ALTER TABLE dbo.myThirdtable ADD CONSTRAINT
      DF_myThirdTable_Column5 DEFAULT 0 FOR Column5
GO
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now