Replacing Merge Objects

I have a merge publication that I need to change some views and get the new
views to the subscribers. At first, i tried the following steps and it
failed when trying to synch from subscriber:
exec sp_dropmergearticle ...
ALTER VIEW ....
exec sp_addmergearticle ...
re-created snapshot
Do I only need the ALTER VIEW and then re-create snapshot? I want to keep
the VIEW in the articles. Thanks.
David
Or, do I need to drop and re-create the subscriber snapshot. We are using
SQL 2000 and have laptops as anonymous subscribers. I don't want to lose
the data changes that have been made on the laptops. I prefer to have them
synch first, then recreate their snapshot. However, this is very difficult
as laptops synch at different times. Can anyone give me advice or point me
to a "how-to" article? I'm sure our situation is no different than a lot of
other Merge replications with disconnected subscribers. I am very new to
SQL Server replication so I want to do it right the first time. Thanks.
David
"David" <dlchase@.lifetimeinc.com> wrote in message
news:eCgHjbUGGHA.3984@.TK2MSFTNGP14.phx.gbl...
>I have a merge publication that I need to change some views and get the new
>views to the subscribers. At first, i tried the following steps and it
>failed when trying to synch from subscriber:
> exec sp_dropmergearticle ...
> ALTER VIEW ....
> exec sp_addmergearticle ...
> re-created snapshot
> Do I only need the ALTER VIEW and then re-create snapshot? I want to keep
> the VIEW in the articles. Thanks.
> David
>
|||David,
have you given up trying to isolate the views into a different publication?
This will be much better than reinitializing to send down view changes,
which is what you'll be obliged to do this way. Add the views to a new
publication and have all your subscribers subscribe to this new publication.
Typically this'll be a snapshot one for the sake of clarity. Each time the
views change, you generate a new snapshot and synchronize. Disable the
snapshot job in this case because typically you won't synchronize often, and
it'll be manually controlled. Pls let me know if any of this is unclear.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||No, I haven't given up on that. I plan to do that in near future. But
I still don't understand why I can't synch now when I change a view.
The laptop synch fails with:
The schema script
'\\LIFEDEVTEST\E$\Snapshots\unc\LIFEDEVTEST_MCFIDa ta_MCFIDataPubAll\2006
0114140533\vw_BillingDetail_1737.sch' could not be propagated to the
subscriber.
Cannot drop the view 'dbo.vw_BillingDetail' because it is being used for
replication.
What I did on the publisher was 1. alter view 2. sp_addmergearticle 3.
run new snapshot.
Then, when I tried to synch from a laptop I got the error above.
Wouldn't I get this even with a separate publication? Or would I have to
know that a new snapshot exists at the subscriber (laptop) before
synching.
As you can tell, the light still has not gone on in my brain about this.
I want it to be simple so user of laptop doesn't have to do special
things. What am I missing? Thanks. I really appreciate your help. I
did a lot of replication with Access in the past and this is very
different.
David
*** Sent via Developersdex http://www.codecomments.com ***
|||David,
it'll be fine if you don't drop the view from the publication. If it is left
there, you just alter the view on the publisher then reinitialize. This
process will call a system proc (sp_MSunmarkreplinfo) behind the scenes that
will remove the replication flag and then allow the article to be dropped
during the replication process. The way you're doing it means the
replication engine 'thinks' this is the first time it's seen the view so the
flag is not reset on the subscriber and you get the same error you'd get if
you tried manually to alter the view on the subscriber.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'll give that a try. Thanks.
David
*** Sent via Developersdex http://www.codecomments.com ***
|||When you refer to "reinitialize" do you mean just try the synch again with
laptop subscriber? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23hAUlrhGGHA.2684@.TK2MSFTNGP11.phx.gbl...
> David,
> it'll be fine if you don't drop the view from the publication. If it is
> left there, you just alter the view on the publisher then reinitialize.
> This process will call a system proc (sp_MSunmarkreplinfo) behind the
> scenes that will remove the replication flag and then allow the article to
> be dropped during the replication process. The way you're doing it means
> the replication engine 'thinks' this is the first time it's seen the view
> so the flag is not reset on the subscriber and you get the same error
> you'd get if you tried manually to alter the view on the subscriber.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
to reinitialize, you right-click on the publication and select to
'reinitialize all subscriptions'. This means that you'll have to create a
new snapshot then synchronize.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Wait. What about the laptops that are out in the field that have not yet
updated the Publisher with their changes yet? If I create a new snapshot,
won't that kill their updates?
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OoecZ5qGGHA.1628@.TK2MSFTNGP12.phx.gbl...
> David,
> to reinitialize, you right-click on the publication and select to
> 'reinitialize all subscriptions'. This means that you'll have to create a
> new snapshot then synchronize.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||AFAIR, they'll have the option of uploading their changes before receiving
the snapshot.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)