How to change a field's type?

How to change a field's type?

• RRAALLVV

  Hi,
  
  
  Whew! Why didn't I think about that tsk....
  Thank you very much! You really made my day...
  
  Here's the error message that I keep on getting in SQLServerCE:
  
  FAILED: ALTER TABLE CustomerAddresses
  ALTER COLUMN IDNew int
  Error: 0x80040e14 DB_E_ERRORSINCOMMAND
  Native Error:  (25501)
  Description: There was an error parsing the query. [Token line number,Token line offset,,Token in error,,]
  Interface defining error: IID_ICommand
  Param. 0: 2
  Param. 1: 7
  Param. 2: 0
  Param. 3: IDNew
  Param. 4:
  Param. 5:
  
  
  
  Cheers,
  
  
  Paul June A. Domag
  
  

• thebrainstormman

  Hi,
  
  I tried placing a NOT NULL statement and still it doesn't work...
  
  ALTER TABLE myTable ALTER COLUMN myCol int NOT NULL
  
  What is wrong with my statement
  BTW, the myCol field is numeric (I don't know if it matters)
  
  ALSO, is there another alternative in renaming the field Coz Im trying to achieve this in SQLServerCE and there is no sp_rename function in SQLServerCE...
  
  
  
  
  cheers,
  
  
  
  Paul June A. Domag
  
  

• Mark Benvenuto

  The syntax you have listed is correct, although you may need to specify NULL/NOT NULL:
  
  Alter Table dbo.SomeTable
      Alter Column SomeField Varchar(101) NULL
  
  For the rename funtion, use sp_rename you can find the syntax in books online...
  
  Edit:  If you are getting an error, please post the details of the error so we can dig a little deeper...
  
  
  

• Fábio Lima

  Hi Paul,
  
  I think that Identity may be the issue.  I suppose for an easy answer you could use the same method to change the column type, i.e., create a new column, copy the values and delete the orginal column.
  
  To restore the numbering just do it when you declare the column:
  
  Alter Table SomeTable
      With Check Add
         MyColumn INT IDENTITY (x, y) NOT NULL
  
  Where x is the SEED value (i.e., what the next number will start at) and y is the INCREMENT (usually 1 to increment by one)
  
  So if you where to declare the column as IDENTITY (1500, 1) the next record inserted would automatically have a value of 1500, etc.
  
  Let me know if that makes sense.
  
  Thanks!
  Tyler
  
  

• dpopcmh

  Hi,
  
  
  In addition to that, how could I modify a field to make it an IDENTITY column
  I tried:
        ALTER TABLE mytable ALTER COLUMN IDNew IDENTITY
  
  It generates an error saying that my field could not be made identity column after it was made...
  
  BTW, the column that im trying to rename is an IDENTITY column, so by using the solution that you have provided, I need to restore the Autonumbering function on my field...
  
  
  
  
  cheers,
  
  
  Paul June A. Domag
  
  

• VisualStudio Rocks!

  With a column set to Identity you cannot insert values unless your basically turn off the Identity feature, which is done as Eisa states. 
  
  If this doesn't work in SQL CE then we'll need to find a SQL CE Expert to answer this question.
  
  Have you tried turning off the IDENTITY_INSERT   Also, make sure you insert GO statements after each line.
  
  
  
  

• Puzzled

  Hi Paul
  I think you cannot update an Identity column unless you set the identity insert on
  
  ALTER TABLE t ADD myNewCol int IDENTITY(1,1)
  go
  SET IDENTITY_INSERT t ON
  UPDATE t SET myNewCol = ID ' Error Here
  SET IDENTITY_INSERT t OFF
  ALTER TABLE t DROP COLUMN ID
  GO
  
  
  

• chorweng

  Hey Paul,
  
  I'm not familiar with SQL Server CE, so not sure if the T-SQL is any different.
  
  In the absence of sp_rename, the following should work
  
  -- Create the renamed column
  Alter Table myTable with Check Add newColname varchar(100) not null
  Go
  -- copy the data to the new column
  Update myTable set newColumn = oldColumn
  Go
  -- delete the old column
  Alter Table myTable Drop Column oldColumn
  Go
  
  For the Alter statement that isn't working, can you please post the error message that you receive
  
  Thanks,
  Tyler
  
  

• daras

  Hi,
  
  
  Im currently using SQLServerCE and I doubt that this function/variable exists... Im trying to do this purely in SQL Statement...
  
  
  Any Other Bright Ideas
  
  
  
  
  cheers,
  
  
  Paul June A. Domag
  
  

• Carty

  Hi,
  
  
  Sorry, but it didn't work. It seems that Identity columns couldn't be updated...
  I used your steps:
  
           ALTER TABLE t ADD myNewCol int IDENTITY(1,1)
           UPDATE t SET myNewCol = ID ' Error Here
           ALTER TABLE t DROP COLUMN ID
  
  BTW, my ID field (w/c is the identity column) is not entirely sequential. What I mean is, there have been some deletions and the numbering is somewhat jumbled. eg 1,2,3,8,11,13,21,22,25...
  
  In this case I can't even use the new Identity Column that I would create coz the newly created identity would be sequential and thus would break my relationship with other tables...
  
  Any other bright ideas
  
  
  
  cheers,
  
  
  
  Paul June A. Domag
  
  

• vivekuniq

  Hi,
  
  
  Sad to say, it didn't work. I searched the BOL of SQLServerCE and the IDENTITY_INSERT switch isn't available. Guess, I'll have to find other means to solve this problem...
  
  Thanks a lot Tyler Free and Eisa for your ideas and comments...
  
  
  
  
  cheers,
  
  
  Paul June A. Domag
  
  
• How to change a field's type?