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

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

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 12, 2001

Transaction Replication - Altering a Published Table Using SQL 2000

By Andy Warren

A few weeks ago I published an article about modifying replicated tables with SQL 7. If you haven't read that article, I encourage you to do so before continuing.

With SQL 2000 you can now add a column to a table (and a publication) with very little effort. The only thing to remember is that if you want the new column to be added to the subscribers, you MUST make the change via the 'Filter Columns' tab of the publication properties. SQL still provides no help if you want to modify an existing column. You can drop a column as long as it is not part of the primarykey or part of a filter (thanks to Jeff Cook for pointing this out to me).  If you don't want the new column to be part of any existing publication you can add the column via Enterprise Manager or Query Analyzer.

For the following demo, I created two databases, ReplSource and ReplDestination, both on the same machine running an instance of SQL2K Developer Edition. I then imported the Authors table from Pubs into ReplSource and created a standard transactional publication, using the default options. Here is the original schema:

To use the Filter Columns tab you can either use 'Create & Manage Publications' found on the Tools|Replication menu, or you can right click the publication itself either under Databases or under Replication Monitor.

Click on Filter Columns. You'll see the Add Column to Table button. Clicking that brings up the following dialog. My one complaint here is that instead of the nice editing tools you normally get when making changes through Enterprise Manager, you have to type everything in. If you're not sure of the syntax, make a quick copy of the table schema and use Enterprise Manager to make the change, then script the changes out so you can copy the DDL for the column you're adding. If you make a mistake here, you'll have to apply the same process you would with SQL 7! 


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