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 |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2008-05-02 : 16:42:44
|
| Guys,We are using MSSQL Server 2000, with the following version:Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) May 13 2005 18:33:17 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1) We have Transactional Replication set up, with 1 Publisher and 1 Subscriber.I recently had to change the data type of some tables from VARCHAR to NVARCHAR, so that the tables can hold foriegn words and phrases. The problem was that those tables are published thorough the Publisher in the Transaction Replication that we have set up Due to Replication, direct change of the column DATA TYPE was not allowed. As such, I had to take the following steps in order to perform this change:1. Delete the Subscriber2. Take table A out as an article3. Change the data type from VARCHAR to NVARCHAR for the column needed4. Add the table back in as an article, with the following properties for the "Snapshot" tab checked: - Delete all data in the existing table - Include declared referential integrity - Convert user-defined data types to base data types5. Add back the subscriber, checking the following in the Push Subscription Wizard: - "No, the Subscriber already has the schema and data"6. Alter sp_MSins_TableA and sp_MSupd_TableA in the Publisher and Subscriber databases, so that the parameter passed is of type NVARCHAR and not VARCHARThis seems to have worked [unless there is some hidden problem].My questions are the following:1. Is my current procedure correct as is?2. Is there a better way of doing this?3. Why did I have to manually change the Replication Stored procedures (e.g. sp_MSins_TableA )? I would assume that this should have been done by the system and it is usually not safe to do these manual changes. Did this happen because I missed something?Thanks a lot! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-02 : 18:20:45
|
| Check it out for answer:http://www.replicationanswers.com/AddColumn.asp |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2008-05-05 : 10:03:08
|
| sodeep,Thank you for the link. I will try it out! |
|
|
|
|
|
|
Changing a data type with Replication involved