Changing the DATATYPE of a PUBLISHED COLUMN

[KingSexy182]

Hi guys,

Is there any way or method to CHANGE the DATATYPE of a column in a published table being used for transactional replication (MSSQL 2000), WITHOUT DROPPING THE SUBSCRIPTION ????

Im stuck in this mess and do have the option to drop the subscription, alter the table, create the subscription and rerun the snapshot or to recreate it by Manual Synchronisation either.

Can anyone help? Has anyone been across this dilemma before and have troubleshooted the problem? If yes, help is much appreciated.


MY PROBLEM:
~~~~~~~~~~~~~
'MyTable' is currently being published and has subscriptions to it. The PRIMARY KEY column 'id' has an Identity property as well. 'id' is of datatype smallint, however because of bad planning, i now need to change that datatype to an integer to support a larger range WITHOUT DROPPING SUBSCRIPTIONS.
I CANT DROP THE COLUMN EITHER AS IT IS BEING THE PRIMARY KEY COLUMN.

IS THERE ANY OTHER WAY I CAN DO TO ARCHIEVE MY GOAL? THANKYOU.

[rmiao]

How about drop the column from article, change data type then add it back to article? You can drop/add column with sp_articlecolumn.

[MAK]

do it on the test environment. if there are foreign key tables then it becomes hectic

[KingSexy182]

thanks guys,

but i cant drop the column and add it again, because the datatype type i want change is the PRIMARY KEY COLUMN.

doesnt look to good for me..... = (