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
Paul Ibison - 31 Oct 2006 15:57 GMT
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 .
michael.swinarski@cox.com - 31 Oct 2006 16:50 GMT
> 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 .
Correct. I don't however see a work around posted here. I'm hoping
that someone might be able to clue me in to what I can do. I would
hate to think that MS would not have a way of adding the article to a
publication without having to do a total re-init.
-ms
Raymond Mak [MSFT] - 31 Oct 2006 18:23 GMT
Hi Michael, I posted an unofficial workaround in the following posting:
http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_frm
/thread/ad9ad3d18f501332/447e9417f655bb1c?lnk=gst&q=Raymond+Mak&rnum=28#447e9417
f655bb1c
Other more official workarounds including changing the sync_method from
'concurrent' to either 'database snapshot' (enterprise edition only) and
'native' (which locks table during snapshot generation). Change the
sync_method will force a reinitialization of all your subscriptions at this
point.
-Raymond
>> Saw this on Vyas's blog some time ago in SQL Server 2000
>> (http://vyaskn.tripod.com/sqlblog/).
[quoted text clipped - 9 lines]
>
> -ms
michael.swinarski@cox.com - 31 Oct 2006 18:33 GMT
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?
> Hi Michael, I posted an unofficial workaround in the following posting:
>
[quoted text clipped - 21 lines]
> >
> > -ms
Raymond Mak [MSFT] - 31 Oct 2006 19:29 GMT
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
> 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
[quoted text clipped - 26 lines]
>> >
>> > -ms
michael.swinarski@cox.com - 31 Oct 2006 21:37 GMT
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
> 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
[quoted text clipped - 32 lines]
> >> >
> >> > -ms
Raymond Mak [MSFT] - 31 Oct 2006 21:42 GMT
By setting the immediate_sync property to 0, the snapshot agent should only
generate files for articles with uninitialized subscriptions.
-Raymond
> Can you then generate a snapshot for the individual article? When the
> article is added, how does the subscriber recieve it for the first
[quoted text clipped - 44 lines]
>> >> >
>> >> > -ms