Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KingSexy182
Starting Member
7 Posts |
 Posted - 2003-10-27 : 05:59:26
|
| 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. |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-10-27 : 08:27:18
|
| http://vyaskn.tripod.com/repl_ans.htm#schemaHow to change the schema of a published table?In SQL Server 7.0, if you have to change the schema of a replicated table, first you have to unsubscribe to all the publications, which are based on this table. Then, delete all the publications. Do the schema change. Republish the table and resubscribe. You can do NOSYNC, because the data is already there at the subscriber.You can script the replication in 7.0, so that you don’t have to recreate the publications and subscriptions manually, every time you change the schema. In Enterprise Manager, go to ‘Tools/Replication/Generate Replication Scripts…’ to generate replication scripts. Note: Pull subscriptions cannot be scripted and can’t be traced in Profiler for security reasons.The next release of SQL Server is going to support Schema Replication, where you don’t need to do all the above steps if you want to change the schema of a replicated table. |
 |
|
|
KingSexy182
Starting Member
7 Posts |
Posted - 2003-10-27 : 21:55:33
|
| thanks for the advice Stoad, but apparently i do not have the luxurious option of dropping the subscription, then either auto sync/manual sync to synchronise the subscription.thanks. |
|
|
|
|
|
|
|
|
Changing the DATATYPE of a PUBLISHED COLUMN