sql server replication: adding articles -- sql server replication

adding articles

michael.swinarski NO[at]SPAM cox.com
10/31/2006 5:57:55 AM

sql server replication:

I am using trasactional push replication on 2005.

So, first I do this:

EXEC sp_addarticle
@publication = N'azDSS', -- Change value with the name of the
publication we wish to add to
@article = N'tbPhxSrvrpf', -- Change value with the name of the
article we are adding
@source_owner = N'ICOMS', -- Change value with the correct schema
name
@source_object = N'tbPhxSrvrpf', -- Change value to the name of the
table
@destination_table = N'tbPhxSrvrpf', -- Change value to the name of
the table
@type = N'logbased',
@creation_script = N'',
@description = null,
@pre_creation_cmd = N'truncate',
@schema_option = 0x000000000807509F,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL [sp_MSins_tbPhxSrvrpf]', -- Update the section
between the {} (and removed the {})
@del_cmd = N'CALL [sp_MSdel_tbPhxSrvrpf]', -- Update the section
between the {} (and removed the {})
@upd_cmd = N'SCALL [sp_MSupd_tbPhxSrvrpf]', -- Update the section
between the {} (and removed the {})
@filter = null,
@sync_object = null
GO

Then I do this:

exec sp_addsubscription
@publication = N'azDSS', -- Change value with the name of the
publication
@article = N'tbPhxSrvrpf', -- Change value with the name of the
article
@subscriber = N'CARZ0DB13\ARZSQL13', -- Change value with the name
of the subscribing server
@destination_db = N'azDSS', -- Change value with the name of the
subscribing db
@sync_type = N'automatic',
@update_mode = N'read only'
GO

and what I get is:

Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line
533
Specify all articles when subscribing to a publication using concurrent
snapshot processing.

Is there a work around for this. I need to be able to add an article
to an existing publication without snapshoting the entire thing

Re: adding articles

michael.swinarski NO[at]SPAM cox.com
10/31/2006 9:33:22 AM

This publication is over 200 GB. I would rather not have to
re-snapshot the entire publication just to add a table (wich we will be
doing more offtien then most). Can this be done?

[quoted text, click to view]

Re: adding articles

Raymond Mak [MSFT]
10/31/2006 10:29:04 AM

I am guessing that you don't want the snapshot agent to regenerate snapshot
data for all articles in your publication. If this is the case, please make
sure that the immediate_sync property in syspublications is set to 0 (see
sp_changepublication).

-Raymond
[quoted text, click to view]

Re: adding articles

michael.swinarski NO[at]SPAM cox.com
10/31/2006 12:37:00 PM

Can you then generate a snapshot for the individual article? When the
article is added, how does the subscriber recieve it for the first
time?
-ms

[quoted text, click to view]

Re: adding articles

Raymond Mak [MSFT]
10/31/2006 12:42:48 PM

By setting the immediate_sync property to 0, the snapshot agent should only
generate files for articles with uninitialized subscriptions.

-Raymond

[quoted text, click to view]

Re: adding articles

Paul Ibison
10/31/2006 2:57:46 PM

Saw this on Vyas's blog some time ago in SQL Server 2000
(http://vyaskn.tripod.com/sqlblog/).
As far as i know, you'll have to use a workaround eg have a different
publication publish the table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.