Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    23

    Unanswered: Schema changes and transactional replication

    Hi

    If a column is added to a table which is a published article in a transactional replication, is it sufficient to do the following?

    - drop subscribers
    - amend the publication by dropping the affected table, then re-adding it
    - re-pushing to subscribers

    Or do I have to drop the entire publication and recreate it?

    TIA

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Schema changes and transactional replication

    You can simple use sp_repladdcolumn.

  3. #3
    Join Date
    Feb 2004
    Posts
    23

    Re: Schema changes and transactional replication

    Thank you - but if someone else has already added a column through Enterprise Manager, for example? Is my method above OK?

  4. #4
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Schema changes and transactional replication

    Yes, but why not drop the column and re-add it with sp_repladdcolumn. You won't have to reinitialize the subscirption which could take a long time depending on how large your database is and the subscription has to be shut down for refresh.

  5. #5
    Join Date
    Feb 2004
    Posts
    23

    Re: Schema changes and transactional replication

    Thank you.

    Follow-up question: in my transactional replication strategy I schedule snapshots every night. If a column, say, is added without my knowledge (!) (e.g. by external product support), as the snapshots re-generate the table schemas, would the change be picked up automatically in this way?

    TIA

  6. #6
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Schema changes and transactional replication

    No.

  7. #7
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69

    Re: Schema changes and transactional replication

    Originally posted by askrabbit
    Thank you.

    Follow-up question: in my transactional replication strategy I schedule snapshots every night. If a column, say, is added without my knowledge (!) (e.g. by external product support), as the snapshots re-generate the table schemas, would the change be picked up automatically in this way?

    TIA
    Hi,
    I donot think any one can add a column using Enterprise manager or other friend-end program. SQL Server would not allow you to add a column if a table is pubished as an article.

    You have to use system stored procedure sp_repladdcolumn.
    And it will be replicated with data to all subscribers when agent runs next time.

  8. #8
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96

    Re: Schema changes and transactional replication

    There is another way to do this without using sp_repladdcolumn. Go to properties in your publisher, then go to Filter columns, you have a choice to add/drop a columns. Once you did that, you will be able to let the replication to do the for you. You do not need to drop subscriber,publisher or distributor. What you need to do is to reinitialize the snapshot and let the snapshot copy the data and new table structure over it.

    Originally posted by smasanam
    Hi,
    I donot think any one can add a column using Enterprise manager or other friend-end program. SQL Server would not allow you to add a column if a table is pubished as an article.

    You have to use system stored procedure sp_repladdcolumn.
    And it will be replicated with data to all subscribers when agent runs next time.

  9. #9
    Join Date
    Feb 2004
    Posts
    23

    Re: Schema changes and transactional replication

    Thanks to everyone who replied - very helpful suggestions!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •