Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 12, 2001

Transaction Replication - Altering a Published Table Using SQL 2000 - Page 2

By Andy Warren

In this example I'm adding a column called country.


Once you add a column, it's automatically selected as part of the article. When you close the publication properties the change will be sent to each subscriber the next time the log reader & distribution agent run.


That's all there is to it. A big step up from SQL 7 and if you use do these changes often, probably worth the upgrade right there! You've probably noticed that there is also a 'Drop Selected Column' button. Let's look at what happens when you click it:

That's right, even though you're working on a publication, if you use this button it will actually drop the column from both the publisher and all the subscribers. Useful, but use with care!

Another thing you can do from Filter Columns is to remove a column from the article. You just can't do this easily in SQL 7, but with SQL 2000 you just clear the checkbox - well, almost. It does most of the work for you, but unfortunately requires you to force a snapshot to occur. Until the snapshot is done, no transactions will be distributed to subscribers of that publication.

That's all there is to it. SQL 2000 greatly reduces the time needed to perform one of the more common tasks of adding a column to a published article. Maybe in a future release we'll see enhancements that will support modifying existing columns without having to do a snapshot.

If you've got questions or comments about any of my articles posted on Swynk, please email me. 

Back to Page 1

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM