Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Replication / October 2006

Tip: Looking for answers? Try searching our database.

adding articles

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
michael.swinarski@cox.com - 31 Oct 2006 14:57 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.