an article about altering columns in a table but want to verify the correct
sequence of events to add or change articles after Publisher is created.
For a new table:
CREATE TABLE command
sp_addarticle
For a revised table, view or stored proc:
sp_droparticle
DROP ...
CREATE ...
sp_addarticle
For removing table, view or stored proc:
sp_droparticle
DROP ...
Also, I'm not real clear on where or when the snapshot needs to be
re-created. Does this have to be done before the new, changed or deleted
articles will get to the subscribers? Thank you.
David
David,
please take a look at this : http://www.replicationanswers.com/AddColumn.asp
When adding a table to a publication, you'll need to run the snapshot agent.
For TR this'll create the new article only in the snapshot. For merge
this'll create the whole snapshot. In either case only the new table will be
synchronised.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK, I read this, but no mention of what to do with a VIEW. There is a
sp_repladdcolumn but I think that only applies to TABLE objects. If that is
true, then do I have to drop the article, etc. as I mentioned? Thanks.
p.s. I only care about MERGE replication.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uswWphhEGHA.336@.TK2MSFTNGP14.phx.gbl...
> David,
> please take a look at this :
> http://www.replicationanswers.com/AddColumn.asp
> When adding a table to a publication, you'll need to run the snapshot
> agent. For TR this'll create the new article only in the snapshot. For
> merge this'll create the whole snapshot. In either case only the new table
> will be synchronised.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I don't replicate any other objects other than tables (and occasionally the
execution of stored procedures).
Normally schema changes on the other objects aren't replicated even when you
do a sp_refreshpublication. For UNC subscribers I use sp_addscriptexec, for
FTP subscriber I use ADO.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%23y4s6NjEGHA.1816@.TK2MSFTNGP11.phx.gbl...
> OK, I read this, but no mention of what to do with a VIEW. There is a
> sp_repladdcolumn but I think that only applies to TABLE objects. If that
> is true, then do I have to drop the article, etc. as I mentioned? Thanks.
> p.s. I only care about MERGE replication.
> David
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:uswWphhEGHA.336@.TK2MSFTNGP14.phx.gbl...
>
|||David,
there isn't really a distinction between tables and views - they're all
articles.
Actually, like Hilary, I don't have views mixed with other articles in a
publication. I sometimes use sp_addscriptexec but usually isolate the views
into another publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||damn you're good
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O0%23EGdqEGHA.1028@.TK2MSFTNGP11.phx.gbl...
> David,
> there isn't really a distinction between tables and views - they're all
> articles.
> Actually, like Hilary, I don't have views mixed with other articles in a
> publication. I sometimes use sp_addscriptexec but usually isolate the
> views into another publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I had a good mentor
|||
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OBsuvYtEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>I had a good mentor
>
|||I am new at replication so I don't understand this. Doesn't the subscriber
(in this case the laptops) need to have the views and stored procs in order
to run the applications that I have with links to the views and using the
stored procedures? Or are they always part of the snapshot (or ?) that
the subscriber gets normally? I will only make changes to the views and
stored procedures at the Publisher, not at the Subscribers. Thank you for
any help on this. Let me know if my assumption was wrong.
David
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:esi%234qlEGHA.2300@.TK2MSFTNGP15.phx.gbl...
>I don't replicate any other objects other than tables (and occasionally the
>execution of stored procedures).
> Normally schema changes on the other objects aren't replicated even when
> you do a sp_refreshpublication. For UNC subscribers I use
> sp_addscriptexec, for FTP subscriber I use ADO.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "David Chase" <dlchase@.lifetimeinc.com> wrote in message
> news:%23y4s6NjEGHA.1816@.TK2MSFTNGP11.phx.gbl...
>
|||I am a little confused by your question. Merge replication will put in place
on the subscriber the procs and views it needs, sync or no sync
subscription.
If your application which will be accessing the subscriber database needs
views, procs, etc you are responsible for putting these in place. You can
use replication, a post snapshot command, sp_addscriptexec (for unc deployed
subscribers only), or another manual method. My least favorite way to do
this is using replication because of dependency issues and having the
publication pick up changes even after you refresh the publication.
So if you replicate a proc's schema, and you reinitialize the publication,
it won't replicate the changes to the proc- at least this was the case last
time I checked.
If I have misread your question please post back.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:OLUBtp6EGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am new at replication so I don't understand this. Doesn't the subscriber
>(in this case the laptops) need to have the views and stored procs in order
>to run the applications that I have with links to the views and using the
>stored procedures? Or are they always part of the snapshot (or ?) that
>the subscriber gets normally? I will only make changes to the views and
>stored procedures at the Publisher, not at the Subscribers. Thank you for
>any help on this. Let me know if my assumption was wrong.
> David
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:esi%234qlEGHA.2300@.TK2MSFTNGP15.phx.gbl...
>
No comments:
Post a Comment