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 Mgr reporting error

Win 2k & SQL 2k
I have trans & merge replication between several remote
sites and my main office. On a couple of the Replication
Managers, an error is being reported on a publication.
When I expand the dist agent & the publication folder to
the actual publication, there is no error. I have
restarted the remote server and still the error exists,
even after I refresh the Replication Manager in EM.
Any one have an idea?
Larry,
when I have seen these rogue errors reported before, it was because of
incorrect data in tempdb so restarting the sql server service on the
distributor removed both the erroneous table and the red icon. hilary
mentions that running sp_MSload_replication_status normally clears this
error as well.
HTH,
Paul Ibison
|||I have restarted the server where the publications exist
and they are their own distributors. I ran
sp_MSload_replication_status on both the publisher and
the subscriber and the errors were not cleared. Any
ideas?
|||Larry,
can you try restarting the sql server service on the server which has
replication manager.
If this still doesn't fix it, try using profiler after you refresh the
replication manager and see if you can track it down.
HTH,
Paul Ibison

Repeate Outer Group in Matrix

Hi,

I am trying to make this report and I am using matrix in it. Currently it merge my outer group value. Is there any ways that I can use Matrix and have my outer group values REPEATED and not merged. Any help will be appreciated.

Thanks,

-Rohit

Rohit, please take a look at my suggestion in your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1138711&SiteID=1

-- Robert

repairing replicated tables

Hi all,
I have a sql2005 merge replication running nightly between SQl 2005 standard
and SQLexpress. (The subscription is on the SQLexpress) last night it began
failing. I ran a DBCC checkDB and found one table has 3 inconsistancies in
it and the lowest level of repair is repair with loss. This box is on its
way out and i need to band-aide it until the new one arrives and is setup.
My question is, do I need to drop the replication before putting the DB in
single user mode? Is there a way to repair the table without running DBCC
checktable with the repair option?
TIA,
Joe
Is the problem related to some indexes and do you get a RID ID error?
If so this is related to some indexes (on system tables IIRC) and you
can drop them and recreate them.
Run CHECKDB again note the object ID which is experiencing the error
and evaluate whether it is an index or not. Script out the index, drop
it and recreate it. There will be no data loss associated with this.
If it is table related you will have to bcp the data out noting where
failure occurs and then work around that/those rows using the firstrow
and lastrow options. You can use DBCC Page to look at the problem
pages if you need to be so granular in your data retrieval.
On Jan 15, 2:16 pm, jaylou <jay...@.discussions.microsoft.com> wrote:
> Hi all,
> I have a sql2005 merge replication running nightly between SQl 2005 standard
> and SQLexpress. (The subscription is on the SQLexpress) last night it began
> failing. I ran a DBCC checkDB and found one table has 3 inconsistancies in
> it and the lowest level of repair is repair with loss. This box is on its
> way out and i need to band-aide it until the new one arrives and is setup.
> My question is, do I need to drop the replication before putting the DB in
> single user mode? Is there a way to repair the table without running DBCC
> checktable with the repair option?
> TIA,
> Joe
|||Thank you, it was an index issue and droping and recreating the Indexes did
the trick.
Thanks again.
"Hilary Cotter" wrote:

> Is the problem related to some indexes and do you get a RID ID error?
> If so this is related to some indexes (on system tables IIRC) and you
> can drop them and recreate them.
> Run CHECKDB again note the object ID which is experiencing the error
> and evaluate whether it is an index or not. Script out the index, drop
> it and recreate it. There will be no data loss associated with this.
> If it is table related you will have to bcp the data out noting where
> failure occurs and then work around that/those rows using the firstrow
> and lastrow options. You can use DBCC Page to look at the problem
> pages if you need to be so granular in your data retrieval.
> On Jan 15, 2:16 pm, jaylou <jay...@.discussions.microsoft.com> wrote:
>