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)

Replacing a view in merge

We have SQL 2005 and I need to update a view used in a publication. The
views are in a separate pub so it should be pretty easy. My thought is that
I run sp_dropmergearticle, update the view and then run sp_addmergearticle.
Am I correct? Also, will the subscribers get the new publication/snapshot
when synching? Thanks.
David
I'd use sp_addscriptexec. Simply because it avoids the need for a new
snapshot of all the articles.
Rgds,
Paul Ibison
|||But the users have limited rights. Isn't this a problem in this solution?
Or is that requirement only to run the sp on the publication? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:3C9223C0-069E-48A5-9CD6-C0A3B7743276@.microsoft.com...
> I'd use sp_addscriptexec. Simply because it avoids the need for a new
> snapshot of all the articles.
> Rgds,
> Paul Ibison
>
|||Yes - just run it on the publisher and it'll go down to the subscribers on
synchronization.
HTH,
Paul Ibison
|||Thank you.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:7BBAE4B2-76CF-48E5-AE87-36A77D6F4DCE@.microsoft.com...
> Yes - just run it on the publisher and it'll go down to the subscribers on
> synchronization.
> HTH,
> Paul Ibison
>

Replace SQL view on merge

I have a merge publication that I need to update a published view. I have
tried the following but does not work:
sp_droparticle
drop view
create view
I need to know if I can do this process without being in EM. Thanks.
David
IIRC you can use sp_addscriptexec to do this for subscribers deployed via
UNCs.
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:u7HXevuFGHA.3684@.TK2MSFTNGP14.phx.gbl...
>I have a merge publication that I need to update a published view. I have
>tried the following but does not work:
> sp_droparticle
> drop view
> create view
> I need to know if I can do this process without being in EM. Thanks.
> David
>
|||I found documentation for sp_dropmergearticle and sp_addmergearticle. I
tried them as follows in EM and it worked. Can I do this in a single
script file also? Thanks.
exec sp_dropmergearticle ......
drop view ...
create view ...
exec sp_addmergearticle ......
I had to specify @.force_invalidate_snapshot = 1 on the 1st and last
operations above. Then I issued an "exec sp_start_job ...." to run the
snapshot agent.
Does this seem like the correct way to do what I want to do?
David
*** Sent via Developersdex http://www.codecomments.com ***
|||After I tried the script sequence (and it worked on the Publisher) I tried
to synch with a subscriber and got the following error:
The schema script
'\\LIFEDEVTEST\E$\Snapshots\unc\LIFEDEVTEST_MCFIDa ta_MCFIDataPub\20060111144516\vw_BillingDetail_176 7.sch'
could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
Cannot drop the view 'dbo.vw_BillingDetail' because it is being used for
replication.
(Source: LIFETIMEANTEC (Data source); Error number: 3724)
------
Any ideas why this is occurring? Thanks.
David
"David" <daman@.lifetime.com> wrote in message
news:OwkGNLvFGHA.3684@.TK2MSFTNGP14.phx.gbl...
> I found documentation for sp_dropmergearticle and sp_addmergearticle. I
> tried them as follows in EM and it worked. Can I do this in a single
> script file also? Thanks.
> exec sp_dropmergearticle ......
> drop view ...
> create view ...
> exec sp_addmergearticle ......
> I had to specify @.force_invalidate_snapshot = 1 on the 1st and last
> operations above. Then I issued an "exec sp_start_job ...." to run the
> snapshot agent.
> Does this seem like the correct way to do what I want to do?
> David
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||David,
I'd use a separate snapshot publication for these views, as you are
currently reinitializing the whole set of articles inc data when a view
changes which is a bit of an overkill. Also, when you change a view, it's
best to use alter view rather than drop and create - that way you'll be able
to keep the permissions.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I wasn't aware you could have separate publications for the same database.
Would I then remove the views and stored procs from the current publication
and then create a 2nd one with just the views and stored procs? That sounds
really slick.
Since the subscribers are laptops, I assume I would need to create new
publication synchs on them also? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23%23wAvlvFGHA.1760@.TK2MSFTNGP10.phx.gbl...
> David,
> I'd use a separate snapshot publication for these views, as you are
> currently reinitializing the whole set of articles inc data when a view
> changes which is a bit of an overkill. Also, when you change a view, it's
> best to use alter view rather than drop and create - that way you'll be
> able to keep the permissions.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
the setup you describe is exactly how I do it. You'll need separate
subscriptions it's true, but the versatility is worth it. Actually I have a
separate publication for each programming object type - sps, views and udfs.
Another advantage is that a problem in one publication doesn't affect the
others (use independant distribution agents).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||And by "independant distribution agents" do you mean creating separate
distributors? Currently the distributor is on the same server.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23f25l0vFGHA.3936@.TK2MSFTNGP12.phx.gbl...
> David,
> the setup you describe is exactly how I do it. You'll need separate
> subscriptions it's true, but the versatility is worth it. Actually I have
> a separate publication for each programming object type - sps, views and
> udfs. Another advantage is that a problem in one publication doesn't
> affect the others (use independant distribution agents).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
not a different distributor - in fact this is not possible to another
publication from the same publisher. What I mean is the option on the
subscription options tab - to 'Use a distribution agent that is
independant....'. This'll isolate the jobs entirely.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK, but I cannot find this in the Subscription Options tab. I went into
Publisher properties and found the tab but there is no checkbox with that
name on it. Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e4dewY1FGHA.3984@.TK2MSFTNGP14.phx.gbl...
> David,
> not a different distributor - in fact this is not possible to another
> publication from the same publisher. What I mean is the option on the
> subscription options tab - to 'Use a distribution agent that is
> independant....'. This'll isolate the jobs entirely.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

repl_distributor

If I click on the publication and look at the subscriber column the
subscriber name has changed to REPL_DISTRIBUTOR. Why is this and how do i
change it back to the name of the subscriber? Thank you in advance for your
help.
Exactly where are you seeing this? If I right click on my publication and
select properties, I see the Subscriptions and Subscription options tabs,
but nowhere a subscriber column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"golfnut" <golfnut@.discussions.microsoft.com> wrote in message
news:14A13BFA-52B1-45CF-B6E4-FA91F43B4BB2@.microsoft.com...
> If I click on the publication and look at the subscriber column the
> subscriber name has changed to REPL_DISTRIBUTOR. Why is this and how do i
> change it back to the name of the subscriber? Thank you in advance for
your
> help.
>