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)
>