Alter table containing records

[MattDavies51]

I am trying to alter a table column of datatype Money to datatype varchar. The problem is that the table is populated with records so when I attempt to alter it, I get the 'Disallowed implicit conversion from data type varchar to data type money..' message.

I know I should be using the convert function, but where and how?

Here is my existing code without the convert fucntion:

alter table reference..invoice alter column inp_minfee varchar(50)
go

Assistance will be greatly appreciated....

 

[JamesLean]

When using the ALTER COLUMN statement, the old data type must be implicitly convertible to the new one. This is not the case for money to varchar.

Using TSQL, you would need to create a new column of type varchar, use an update command to copy the data across (using an explicit CONVERT) and then drop the old column.

Alternatively, you can use Enterprise Mgr to change the data type (in table design view), although I'm not sure whether behind the scenes it just does what I described above rather than actually do a straight convert.

--James

 

[MattDavies51]

Thanks for your help James.

Using Enterprise Manager to change the data type does work successfully, however this change needs to be performed on a variety of SQL servers, of which I dont have access to. So, I would prefer to code the change into a script that is then run on all of the servers.

I will work on your 1st suggestion though.

Cheers
Matt

 

[SQLSister]

May I note that it is unusual to need to change a column from Money to varchar. Any calculations you have on this column in your program code will break. So you need to check what may be affected by a change like this.