Hi all:
Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
index 'uc1MSmerge_tombstone'.
The statement has been terminated.
that is the error i got when i try to delet a record from table which has
merge replication on.
i had a check that this error actually is from the trigger generated by
replication..
so any helps will be appreciated.
Thanks
Nick
"nick" schrieb:
> Hi all:
> Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
> index 'uc1MSmerge_tombstone'.
> The statement has been terminated.
> that is the error i got when i try to delet a record from table which has
> merge replication on.
> i had a check that this error actually is from the trigger generated by
> replication..
> so any helps will be appreciated.
> Thanks
> Nick
Run the merge agent and try again ...
Showing posts with label statement. Show all posts
Showing posts with label statement. Show all posts
Friday, March 30, 2012
Friday, March 9, 2012
replication of views that reference other views
hi,
in a merge replication I use a view (V1) that references another view (V2)
in its select statement.
when running the merge agent to initialize the subscriber I get this error:
The schema script '\\SSS-SERVER\ReplData\unc\SSS-
SERVER_E5K_714_FS_E5K_714_FS_Replikat\200404072145
58\V1_667.sch' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
Unable to replicate a view or function because the referenced objects or
columns are not present on the Subscriber.
(Source: SSS1 (Agent); Error number: 0)
Invalid object name 'dbo.V2'.
(Source: SSS1 (Data source); Error number: 208)
I think the error occurs, because V2 is not yet present on the subscriber
when the agent tries to create V1. (when i check the views created on the
subscriber after the agent fails, V2 is missing)
so how can I tell the agent the order of creating objects?
or is there any other solution?
thanks for your help
thomas
Thomas,
your dependencies are out of sync. Run sp_refreshview on the 2 views then
they'll be replicates in the correct order.
Regards,
Paul Ibison
|||thanks paul,
i tried sp_refreshview on all my views, but i still have the same problem.
thomas
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OAYVRyjHEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Thomas,
> your dependencies are out of sync. Run sp_refreshview on the 2 views then
> they'll be replicates in the correct order.
> Regards,
> Paul Ibison
>
|||use a presnapshot script that precreates the views on the subscriber.
"Thomas Schnauer" <thomas@.schoenauer.at> wrote in message
news:4076c17c$0$30734$91cee783@.newsreader02.highwa y.telekom.at...
> thanks paul,
> i tried sp_refreshview on all my views, but i still have the same problem.
> thomas
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:OAYVRyjHEHA.1944@.TK2MSFTNGP11.phx.gbl...
then
>
|||Thomas,
Although my reply may not apply to you, I am posting some documentation that
may help others.
Contrary to table articles, order of views in publication is seen to be
controlled differently. Maybe this behaviour has been fixed in a later
service pack or hotfix that I am not aware of, so sorry if that is the case.
Unless it is too late or at all feasible, try this workaround:
1. Create a publication for the views.
2. Add one view and generate a snapshot (this step is key to successful
implementation of this workaround).
3. Continue step 2 until all the views have been added. The order in which
you add your views will determine the order in which the snapshot will apply
them to the subscribers.
4. Apply the snapshot. This should work fine now.
Now, the tricky part here is that if you have to change your views and
dependencies change, you might be in trouble again. For example, View2
depends on View1. You add View1, gen snapshot, add View2, gen snapshot and
finally apply snapshot. Later you create View3 and change View2 to include
reference to View3. If you now add View3 to the publication and reinitialise
your subscriptions, the snapshot will try to apply View1, View2 and View3 in
that order instead of the order View1, View3, View2 as required. Obviously,
the agent will fail with the same message you have posted because View3 does
not exist at the subscriber yet and View2 is being applied.
So, the bottom line is: do not use publications with views as articles if
you can have the above scenario. But if your design is not going to change
at all (very unlikely?), then go ahead with the above workaround.
Alternatively, use pre-snapshot scripts, as also suggested by Hilary. Of
course, the scripts should contain the views in the proper order.
Hope the above helps,
Raj Moloye
|||Thomas,
the order of running it is crucial - run sp_refreshview on the simple view
then run it on the view that depends on it. This dependency should show up
using sp_depends. Provided the dependency is recognised, then the order of
application in the snapshot should be correct. I have run this successfully
before. However, if it doesn't work then Hilary's advice (postscripts) or
sp_addscriptexec will do the same thing.
Regards,
Paul Ibison
|||views can also be replicated to all subscribers using sp_addscriptexec
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eMlshkkHEHA.3576@.TK2MSFTNGP10.phx.gbl...
> use a presnapshot script that precreates the views on the subscriber.
> "Thomas Schnauer" <thomas@.schoenauer.at> wrote in message
> news:4076c17c$0$30734$91cee783@.newsreader02.highwa y.telekom.at...
problem.
> then
>
|||Paul,
you were right. I re-ordered my script so that the simple views were
refreshed first - now replication works fine.
thanks to all of you for your support
Thomas
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OH$2dhlHEHA.3840@.TK2MSFTNGP11.phx.gbl...
> Thomas,
> the order of running it is crucial - run sp_refreshview on the simple view
> then run it on the view that depends on it. This dependency should show up
> using sp_depends. Provided the dependency is recognised, then the order of
> application in the snapshot should be correct. I have run this
successfully
> before. However, if it doesn't work then Hilary's advice (postscripts) or
> sp_addscriptexec will do the same thing.
> Regards,
> Paul Ibison
>
in a merge replication I use a view (V1) that references another view (V2)
in its select statement.
when running the merge agent to initialize the subscriber I get this error:
The schema script '\\SSS-SERVER\ReplData\unc\SSS-
SERVER_E5K_714_FS_E5K_714_FS_Replikat\200404072145
58\V1_667.sch' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
Unable to replicate a view or function because the referenced objects or
columns are not present on the Subscriber.
(Source: SSS1 (Agent); Error number: 0)
Invalid object name 'dbo.V2'.
(Source: SSS1 (Data source); Error number: 208)
I think the error occurs, because V2 is not yet present on the subscriber
when the agent tries to create V1. (when i check the views created on the
subscriber after the agent fails, V2 is missing)
so how can I tell the agent the order of creating objects?
or is there any other solution?
thanks for your help
thomas
Thomas,
your dependencies are out of sync. Run sp_refreshview on the 2 views then
they'll be replicates in the correct order.
Regards,
Paul Ibison
|||thanks paul,
i tried sp_refreshview on all my views, but i still have the same problem.
thomas
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OAYVRyjHEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Thomas,
> your dependencies are out of sync. Run sp_refreshview on the 2 views then
> they'll be replicates in the correct order.
> Regards,
> Paul Ibison
>
|||use a presnapshot script that precreates the views on the subscriber.
"Thomas Schnauer" <thomas@.schoenauer.at> wrote in message
news:4076c17c$0$30734$91cee783@.newsreader02.highwa y.telekom.at...
> thanks paul,
> i tried sp_refreshview on all my views, but i still have the same problem.
> thomas
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:OAYVRyjHEHA.1944@.TK2MSFTNGP11.phx.gbl...
then
>
|||Thomas,
Although my reply may not apply to you, I am posting some documentation that
may help others.
Contrary to table articles, order of views in publication is seen to be
controlled differently. Maybe this behaviour has been fixed in a later
service pack or hotfix that I am not aware of, so sorry if that is the case.
Unless it is too late or at all feasible, try this workaround:
1. Create a publication for the views.
2. Add one view and generate a snapshot (this step is key to successful
implementation of this workaround).
3. Continue step 2 until all the views have been added. The order in which
you add your views will determine the order in which the snapshot will apply
them to the subscribers.
4. Apply the snapshot. This should work fine now.
Now, the tricky part here is that if you have to change your views and
dependencies change, you might be in trouble again. For example, View2
depends on View1. You add View1, gen snapshot, add View2, gen snapshot and
finally apply snapshot. Later you create View3 and change View2 to include
reference to View3. If you now add View3 to the publication and reinitialise
your subscriptions, the snapshot will try to apply View1, View2 and View3 in
that order instead of the order View1, View3, View2 as required. Obviously,
the agent will fail with the same message you have posted because View3 does
not exist at the subscriber yet and View2 is being applied.
So, the bottom line is: do not use publications with views as articles if
you can have the above scenario. But if your design is not going to change
at all (very unlikely?), then go ahead with the above workaround.
Alternatively, use pre-snapshot scripts, as also suggested by Hilary. Of
course, the scripts should contain the views in the proper order.
Hope the above helps,
Raj Moloye
|||Thomas,
the order of running it is crucial - run sp_refreshview on the simple view
then run it on the view that depends on it. This dependency should show up
using sp_depends. Provided the dependency is recognised, then the order of
application in the snapshot should be correct. I have run this successfully
before. However, if it doesn't work then Hilary's advice (postscripts) or
sp_addscriptexec will do the same thing.
Regards,
Paul Ibison
|||views can also be replicated to all subscribers using sp_addscriptexec
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eMlshkkHEHA.3576@.TK2MSFTNGP10.phx.gbl...
> use a presnapshot script that precreates the views on the subscriber.
> "Thomas Schnauer" <thomas@.schoenauer.at> wrote in message
> news:4076c17c$0$30734$91cee783@.newsreader02.highwa y.telekom.at...
problem.
> then
>
|||Paul,
you were right. I re-ordered my script so that the simple views were
refreshed first - now replication works fine.
thanks to all of you for your support
Thomas
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OH$2dhlHEHA.3840@.TK2MSFTNGP11.phx.gbl...
> Thomas,
> the order of running it is crucial - run sp_refreshview on the simple view
> then run it on the view that depends on it. This dependency should show up
> using sp_depends. Provided the dependency is recognised, then the order of
> application in the snapshot should be correct. I have run this
successfully
> before. However, if it doesn't work then Hilary's advice (postscripts) or
> sp_addscriptexec will do the same thing.
> Regards,
> Paul Ibison
>
Subscribe to:
Posts (Atom)