adding articles

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
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 .
|||Paul Ibison wrote:
> 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
|||Hi Michael, I posted an unofficial workaround in the following posting:
[url]http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_frm/thread/ad9ad3d18f501332/447e9417f655bb1c?lnk=gst&q=Raymond+Mak&rnum=28#447 e9417f655bb1c[/url]
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
"michael.swinarski@.cox.com" <mswinarski@.gmail.com> wrote in message
news:1162309836.937305.115900@.k70g2000cwa.googlegr oups.com...
> Paul Ibison wrote:
>
> 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
>
|||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?
Raymond Mak [MSFT] wrote:[vbcol=seagreen]
> Hi Michael, I posted an unofficial workaround in the following posting:
> [url]http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_frm/thread/ad9ad3d18f501332/447e9417f655bb1c?lnk=gst&q=Raymond+Mak&rnum=28#447 e9417f655bb1c[/url]
> 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
> "michael.swinarski@.cox.com" <mswinarski@.gmail.com> wrote in message
> news:1162309836.937305.115900@.k70g2000cwa.googlegr oups.com...
|||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
"michael.swinarski@.cox.com" <mswinarski@.gmail.com> wrote in message
news:1162316002.881015.11340@.m7g2000cwm.googlegrou ps.com...
> 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?
>
>
> Raymond Mak [MSFT] wrote:
>
|||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
Raymond Mak [MSFT] wrote:[vbcol=seagreen]
> 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
> "michael.swinarski@.cox.com" <mswinarski@.gmail.com> wrote in message
> news:1162316002.881015.11340@.m7g2000cwm.googlegrou ps.com...
|||By setting the immediate_sync property to 0, the snapshot agent should only
generate files for articles with uninitialized subscriptions.
-Raymond
"michael.swinarski@.cox.com" <mswinarski@.gmail.com> wrote in message
news:1162327020.094451.305790@.k70g2000cwa.googlegr oups.com...
> 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
>
>
> Raymond Mak [MSFT] wrote:
>