Adding table/article w/o starting snapshot!

Before running the snapshot agent, run
sp_refreshsubscriptions 'publicationname'
Cheers,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks for ur response, but I still get the same error.
"A snapshot was not generated b/c no subscription needed intialization".
Regards
Naveed.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1aa701c4aa03$e43f6440$a501280a@.phx.gbl...
> Before running the snapshot agent, run
> sp_refreshsubscriptions 'publicationname'
> Cheers,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Naveed,
was the initial subscription a noinit one?
It looks like it was, in which case you'll have to
manually add the scripts
(http://support.microsoft.com/default.aspx?scid=kb;EN-
US;299903), and DTS the table. Alternatively you can
publish the table in a separate publication.
Rgds,
Paul Ibison[vbcol=seagreen]
|||Hello Paul;
I think if I reintialize the subscription then snapshot will start
publishing all articles instead of 1 article.
and I dont want that.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:3fb201c4aa11$91875d30$a601280a@.phx.gbl...
> Naveed,
> was the initial subscription a noinit one?
> It looks like it was, in which case you'll have to
> manually add the scripts
> (http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;299903), and DTS the table. Alternatively you can
> publish the table in a separate publication.
> Rgds,
> Paul Ibison
>
|||Naveed,
to recap - from your other posts you mention that running the snapshot agent
doesn't do anything. After adding a new article and refreshing the
subscription, still the snapshot agent doesn't do anything. My assumption is
that the initial setup was a noinit one, in which case no snapshot of a new
article is produced. The new article is a part of the publication and (I'm
not suggesting you try this) if you update a record in this article on the
publisher there should be an error in the distribution agent which complains
about the absence of an update stored procedure onthe subscriber. Now you
have a choice -
(a) set up these stored procedures by hand using
sp_scriptpublicationcustomprocs and transfer the table by hand. Be sure to
remove the identity attribute if there is one.
(b) reinitialize.
(c) add the new article to a separate publication. This can cause integrity
problems if the article is related to other articles in the first
publication.
I have suggested (a), which won't result in a new snapshot of all articles
being produced.
HTH,
Paul Ibison (MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hello Paul,
I'm using Transactional Replication and it's Push subscription.
Can u plz give me the complete steps to add a single table
w/o starting snapshot for all tables.
And another question,
BOL says sp_refreshsubscription for Pull and
sp_reinitsubscription is for Push Subscription.
But I saw newgroup an it says BOL is wrong sp_refreshsubscription works for
both.
What is the difference b/w sp_refreshsubscription and
sp_reinitsubscription?
Thanks in advance.
Naveed.
|||Naveed,
I don't have a complete script to hand, but this is the basic pattern I have
followed:
On the publisher:
EXEC sp_addarticle
@.publication = N'NorthwindOIncludeDRINonCLustered',
@.article = N'CategoriesArticle',
@.source_owner = N'dbo',
@.source_object = N'Categories',
@.destination_table = N'Categories',
@.type = N'logbased',
@.creation_script = null, @.description = null,
@.pre_creation_cmd = N'drop',
@.schema_option = 0x0000000000000073,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'CALL sp_MSins_Categories',
@.del_cmd = N'CALL sp_MSdel_Categories',
@.upd_cmd = N'MCALL sp_MSupd_Categories'
GO
exec sp_addsubscription
@.publication = N'NorthwindOIncludeDRINonCLustered',
@.article = N'CategoriesArticle',
@.subscriber = N'HOME-WIN2K',
@.destination_db = N'Pubs',
@.sync_type = N'none',
@.update_mode = N'read only'
GO
sp_refreshsubscriptions
run sp_scriptpublicationcustomprocs to generate the necessary procs and
apply them to the subscriber.
(BTW, I have also found that sp_refreshsubscription works for push and
pull).
HTH,
Paul Ibison

Adding new index to existing table in Merge Replication

I see from other posts that sp_addscriptexec is recommended for
updating/creating indexes. It isn't clear to me if I can use Enterprise
Manager to go into the table on the publisher and add additional indexes on
a table and then have the added indexes automatically populate to the
subscribers.
Additionally, BOL says that an index can contain upto 16 fields but they
recommend upto three. Does that mean I should create many indexes with only
upto three fields in each index? Help or references on this matter would
be greatly appreciated.
WB
The index can be added at the publisher as per usual. It won't get
automatically propagated to the subscribers and that is what
sp_addscriptexec is for. If I just have one or two subscribers then I tend
to create the index by hand, but if you have lots, then it is a way or being
sure they all receive the changes, especially if they are pull
subscriptions.
As for the size, this seems a general question to do with indexes. Have a
look for clustered, nonclustered and covering in BOL. Indexes are sorthed on
the first column, then for duplicates of the first column they are sorted on
the second and so on, so 2 indexes with 3 columns is not the same as one
index with 6 columns. An index with 6 columns will be 'wide' and therefore
slow, but on the other hand it might have been created as a covering index
for a particluar long-running query. This is a really big subject and I'm
sure there are loads of resources on it apart from BOL.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding data from disconnected subscriber

Abdul,
what type of replication are you using?
Rgds,
Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thank you Paul, I'm using merge replication
"Paul Ibison" wrote:

> Abdul,
> what type of replication are you using?
> Rgds,
> Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Abdul,
in your first post you mention "Then I recreate the subscription but I find
that the record does not replicate on the other subscriber and publisher. ".
Merge replication is not normally continuous, rather the merge agent is run
on a schedule. You could have a schedule of once a minute and enter records
on the subscriber between synchronizations. To simulate a broken connection,
you could disconnect the network card, or set one of the databases to
read-only, then synchronize. Dropping the subscription as you have done is
something else entirely and is a simulation of a subscriber exceeding the
maximum retention period. In this case you could run sp_removedbreplication,
resubscribe as a noinit subscription and do a dummy update. However, I think
that this isn't what you really require.
Rgds,
Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)