none
Added a New Table to the Database but Didn't replicate to the subscriber RRS feed

  • Question

  • Hi, I have transactional replication between two servers. The publisher and distributor resides on the same server. I recently added a table to the publisher database. After I created the table I choose table to be replicated in the check box. But I don't see the table at the subscriber. In one of the job at the publisher I see the message:

    2010-02-03 19:07:49.299 Error executing a batch of commands. Retrying individual commands.
    2010-02-03 19:08:24.267 Agent message code 208. Invalid object name 'dbo.TableName'.
    2010-02-03 19:08:24.283 Category:COMMAND
    Source:  Failed Command
    Number: 
    Message: if @@trancount > 0 rollback tran
    2010-02-03 19:08:24.283 Category:NULL
    Source:  Microsoft SQL Native Client
    Number:  208
    Message: Invalid object name 'dbo.TableName'.
    Any help is appreciated

    Thank you
    Jay

    Wednesday, February 3, 2010 7:11 PM

Answers

  • Ah, you will need to disable immediate sync on your publication. You might want to review this thread.

    http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/22b3cca9-9dfc-4969-81b6-28c9115764b7

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, February 4, 2010 1:43 PM
    Moderator

All replies

  • That is correct you have to add it to the subscription (sp_addsubscription) and the do a sp_refreshsubscriptions.

    Here is an example:

    create database paul
    GO
    create database PaulSub
    GO
    Use Paul
    GO
    create table table1(pk int not null primary key, charcol char(20))
    GO
    sp_replicationdboption 'paul','publish','true'
    GO
    sp_addpublication 'paul',@status='active'
    go
    sp_addpublication_snapshot 'paul'
    GO
    sp_addarticle 'paul','table1','table1'
    GO
    sp_addsubscription 'paul','table1',@@servername,'paulsub'
    GO
    --ensure the snapshot is deployed
    select * from paulsub.dbo.table1
    --creating table2
    create table table2(pk int not null primary key, charcol char(20))
    GO
    sp_addarticle 'paul','table2','table2'
    GO
    sp_refreshsubscriptions 'paul'
    GO
    sp_addsubscription 'paul','table2',@@servername,'paulsub'
    GO
    --ensure the snapshot is deployed
    select * from paulsub.dbo.table2
    --the message I see in the distribution agent is for 1 table - table2.




    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Wednesday, February 3, 2010 7:28 PM
    Moderator
  • Try Following.

     

    EXEC

     

    sp_addarticle

     

     

    @publication = pualPublicationName,

    @article

    = 'YourTableName',

    @source_object

    = 'YourTableName',

    @source_owner

    = 'sourceOwnder',

    @schema_option

    = 0x80030F3,

    @vertical_partition

    = N'true',

    @type

    = N'logbased',

    @force_invalidate_snapshot

    =1

     


    Failure in Life is failure to try...
    Wednesday, February 3, 2010 8:30 PM
  • Hi Hilary, I got this message, When running the statement:

    sp_addsubscription

     

    'Publisher DBNAme','TableName',@@servername(subscriber server),'Subscriber DBNAME'

     


    Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

    Specify all articles when subscribing to a publication using concurrent snapshot processing

    Thanks

    Wednesday, February 3, 2010 8:34 PM
  • Ah, you will need to disable immediate sync on your publication. You might want to review this thread.

    http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/22b3cca9-9dfc-4969-81b6-28c9115764b7

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, February 4, 2010 1:43 PM
    Moderator