If I have applied a database dump to the subscriber for a no-sync database
that is using snapshot replication, do I need to keep applying new
snapshots? The distribution agents seem to be "delivering transactions" but
not as many as when I used transactional repl on the same database (this was
no-sync as well.) I haven't run any snapshots, because it says it is
delivering the transactions; not sure if this is correct.
Please help.
Thanks so much,
Kristy
Kirsty,
I'm confused and am not too sure what type of replication you are wanting to
set up. The title of the post implies snapshot, but dong a nosync
initialization for snapshot doesn't really make sense. Can you provide a
little more info on this and also what the transactions are that are being
delivered (sp_browsereplcmds).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Originally it was suppose to be transaction repl. But there were problems on
the host and the person who reset up the subscriptions accidently chose the
snapshot for the pull subscription. One of them was set to Transactional,
but it's been having major latency issues that I can't figure out. I didn't
know if the now snapshots subscriptions were not delivering everything
(because no further snapshots other than the original Db dump and snapshot -
that wasn't used because we shut down access to the db during db dump and
snapshot - has happened,) so I posted about that (see below for thread
questions.
If you never run the snapshot agent pass the first time then how come the
distribution agent looks like it is delivering transactions?
Thanks,
Kristy[vbcol=seagreen]
It only will if you do a no-sync.
Hilary Cotter[vbcol=seagreen]
So are the transactions being replicated then? Or do I have to keep running
snapshots?
Thanks,
Kristy
So that is why I thought it was no-sync. The 2 subscribing DBs do not get
updated in anyway and do not need to update the publisher. In fact, this
would be very bad. They DO need to have the lowest latency as possible. This
is a large DB (100+GB) that is a OLTP system, with broken down subscriptions
(pull) for the 1 production DB to give the info to the 2 subscribing DBs.
Not sure what you need from sp_browsereplcmds, so let me know. There is a
lot of records in there right now, but "I think" they seem to have started
from last night. Not sure if they are in order though, so I can say for
sure.
Clear as mud, right? Sorry. Let me know if you need any more info.
Thanks,
--Kristy
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eyNOUZ9UGHA.2360@.TK2MSFTNGP09.phx.gbl...
> Kirsty,
> I'm confused and am not too sure what type of replication you are wanting
to
> set up. The title of the post implies snapshot, but dong a nosync
> initialization for snapshot doesn't really make sense. Can you provide a
> little more info on this and also what the transactions are that are being
> delivered (sp_browsereplcmds).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kirsty,
sorry but I don't follow this scenarion. To be honest I think you're getting
the terminology confused as what you're describing doesn't sound like
snapshot replication. Let's get as much systematic info as we can, so pls
can you confirm :
(a) have you set up Transactional Replication with a nosync initialization?
(b) what are the current issues you are encountering?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Okay, here is the info. I have 1 DB that is 100+ GB. It is broken down into
10 publications to try and improve latency issues (as suggested by Hiliary
awhile back ago.) Each publication is subscribed by 2 different servers;
essentially a "hot backup" and a "warm backup", though the later is to be
used for batch printing of reports as well in the near future.
Yes I was getting the terminology confused. Here is why:
When I look at my publisher and the publications in EM, they say they are
transaction replication. HOWEVER, when I run sp_helppullsubscription on both
of the subscriber DBs,
they return a publication_type of 1 (well except for one of them which
returns a 0 and is the one causing me high latency issues.) BOL says 1 =
snapshot and 0 = transactional replication. ADDitionally, if I look in EM on
the subscrtiption server under the pull subscriptions
folder of the subscribing database, they concur with the results from the
sp_helppullsubscription.
So that is why I am confused as to snapshot versus transactional. They are
suppose to be transcational.
The one (out of 20 subscriptions) that is coming up as transactional, is the
one causing me all of the problems. It has a sync type of automatic, because
when it expired because latency got too high (or was marked that it had to
be reinitialized) at that point a snapshot had to be created for that
subscription. The others have a sync_type of 2 (no sync). Whenever I run
sp_browsereplcmds, it seems like all of the outstanding transactions are
stuck in there and are not moving. So latency build up and again reaches an
unacceptable level. EM will say "delivering transactions" but I never see it
say anything else, and the commands seem to just sit in sp_browsereplcmds
result set.
The latency only goes up for this subscription, it never goes down. It just
climbs for a few days and then gets marked for reintialization (which I
would have to do anyways because I can't have a latecny of more than a few
hours.) All of my other subscriptions flux between 1 sec and 10 seconds.
Even the one that is suppose to be delivering the same subscription to a
different server.
So I am at a complete loss at this point. I don't even know if the other
subscriptions are working right since they say in sp_helppullsubscription
that they are snapshot. They are usually having commands dielivered
according to EM or are Idle. I need to figure out 1.) the others are working
right and 2.) what in the heck is wrong with the rogue subscription and how
to fix it.
I have Hiliary's book, but I can't find anything with this problem in it.
I'd sit down and cry right now if it would make me such a drama queen. ;-)
--Kristy
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:elr$CwJVGHA.5852@.TK2MSFTNGP10.phx.gbl...
> Kirsty,
> sorry but I don't follow this scenarion. To be honest I think you're
getting
> the terminology confused as what you're describing doesn't sound like
> snapshot replication. Let's get as much systematic info as we can, so pls
> can you confirm :
> (a) have you set up Transactional Replication with a nosync
initialization?
> (b) what are the current issues you are encountering?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kristy,
please can you script out 2 pull subscriptions - one that reports that it is
transactional and the other that reports being snapshot and I'll take a
look.
Cheers,
Paul
|||Here is the one that lists as Transactional:
/****** Begin: Script to be run at Subscriber: ******/
exec sp_addpullsubscription @.publisher = N'OPT3', @.publisher_db = N'MyDB',
@.publication = N'5 - Asset', @.independent_agent = N'true',
@.subscription_type = N'pull', @.description = N'', @.update_mode = N'read
only', @.immediate_sync = 0
exec sp_addpullsubscription_agent @.publisher = N'OPT3', @.publisher_db =
N'MyDB', @.publication = N'5 - Asset', @.distributor = N'OPT3',
@.subscriber_security_mode = 1, @.distributor_security_mode = 0,
@.distributor_login = N'mine', @.distributor_password = N'', @.frequency_type =
64, @.frequency_interval = 1, @.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0, @.frequency_subday = 4,
@.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date =
0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959,
@.enabled_for_syncmgr = N'false', @.use_ftp = N'false', @.publication_type = 0,
@.dts_package_location = N'subscriber', @.offloadagent = N'false'
GO
/****** End: Script to be run at Subscriber: ******/
Here is the one listed as Snapshot:
/****** Begin: Script to be run at Subscriber: ******/
exec sp_addpullsubscription @.publisher = N'OPT3', @.publisher_db = N'MyDB',
@.publication = N'5 - Asset', @.independent_agent = N'true',
@.subscription_type = N'pull', @.update_mode = N'read only', @.immediate_sync =
0
exec sp_addpullsubscription_agent @.publisher = N'OPT3', @.publisher_db =
N'MyDB', @.publication = N'5 - Asset', @.distributor = N'OPT3',
@.subscriber_security_mode = 1, @.distributor_security_mode = 0,
@.distributor_login = N'mine', @.distributor_password = N'', @.frequency_type =
64, @.frequency_interval = 1, @.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0, @.frequency_subday = 4,
@.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date =
0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959,
@.enabled_for_syncmgr = N'false', @.use_ftp = N'false', @.publication_type = 1,
@.dts_package_location = N'subscriber', @.offloadagent = N'false'
GO
/****** End: Script to be run at Subscriber: ******/
--Kristy
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23e4uaivVGHA.4976@.TK2MSFTNGP11.phx.gbl...
> Kristy,
> please can you script out 2 pull subscriptions - one that reports that it
is
> transactional and the other that reports being snapshot and I'll take a
> look.
> Cheers,
> Paul
>
|||Kristy,
I notice that the @.publication_type = 1 is set differently - were these
created from scripts and perhaps the update was missed out?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I did a "generate script" on them from your request. They were both done
originally via Enterprise manager. THe transactional one (via
publication_type) has been redone many times because it keeps running at
high latency and I have to end up redoing it. I will just sit there and seem
not to deliver anything and just grow and grow until I have to reinitialize.
--Kristy
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OXbhTcLWGHA.3660@.TK2MSFTNGP04.phx.gbl...
> Kristy,
> I notice that the @.publication_type = 1 is set differently - were these
> created from scripts and perhaps the update was missed out?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kristy,
please can you post up your database schema and a script to create the
publication and subscriptions and I'll try to repro.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Wednesday, 7 March 2012
Saturday, 25 February 2012
Conflits with transactional replication
hello,
i need your help.
i have a transactional plublisher database, with updatable subscribers, and
used a queued update.
i have a lot of problems, first i need to use triggers in both siders, to
update 2 replicates tables, and now i have many conflicts, because they
update both records.
my first question is, i need to change my transactional replication, to
remove tow tables for replication, if i remove that, the transactional
replication reinitialize?
second question is, i need that a replicated table use triggers in both side
(publisher and subscriber), but not "not for replication" can i do this?
thanks,
manuel ralha
I think the majority of your problems can be solved by using the Not For
Replication attribute on your triggers.
Dropping articles will require a new snapshot to be generated or a
reinitailzation.
If you really need the triggers to fire on both side and to be fired by a
replication process you must make sure they do not modify the same data, or
if so, do an existence check to see if they really need to update/insert/ or
delete rows. I don't know what your trigger logic is, but to avoid the
conflicts it sounds like you need to re-write your trigger.
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
"manuel ralha" <manuelralha@.discussions.microsoft.com> wrote in message
news:723FD107-DE30-4755-AC0F-6181E352F9CC@.microsoft.com...
> hello,
> i need your help.
> i have a transactional plublisher database, with updatable subscribers,
> and
> used a queued update.
> i have a lot of problems, first i need to use triggers in both siders, to
> update 2 replicates tables, and now i have many conflicts, because they
> update both records.
> my first question is, i need to change my transactional replication, to
> remove tow tables for replication, if i remove that, the transactional
> replication reinitialize?
> second question is, i need that a replicated table use triggers in both
> side
> (publisher and subscriber), but not "not for replication" can i do this?
> thanks,
> manuel ralha
i need your help.
i have a transactional plublisher database, with updatable subscribers, and
used a queued update.
i have a lot of problems, first i need to use triggers in both siders, to
update 2 replicates tables, and now i have many conflicts, because they
update both records.
my first question is, i need to change my transactional replication, to
remove tow tables for replication, if i remove that, the transactional
replication reinitialize?
second question is, i need that a replicated table use triggers in both side
(publisher and subscriber), but not "not for replication" can i do this?
thanks,
manuel ralha
I think the majority of your problems can be solved by using the Not For
Replication attribute on your triggers.
Dropping articles will require a new snapshot to be generated or a
reinitailzation.
If you really need the triggers to fire on both side and to be fired by a
replication process you must make sure they do not modify the same data, or
if so, do an existence check to see if they really need to update/insert/ or
delete rows. I don't know what your trigger logic is, but to avoid the
conflicts it sounds like you need to re-write your trigger.
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
"manuel ralha" <manuelralha@.discussions.microsoft.com> wrote in message
news:723FD107-DE30-4755-AC0F-6181E352F9CC@.microsoft.com...
> hello,
> i need your help.
> i have a transactional plublisher database, with updatable subscribers,
> and
> used a queued update.
> i have a lot of problems, first i need to use triggers in both siders, to
> update 2 replicates tables, and now i have many conflicts, because they
> update both records.
> my first question is, i need to change my transactional replication, to
> remove tow tables for replication, if i remove that, the transactional
> replication reinitialize?
> second question is, i need that a replicated table use triggers in both
> side
> (publisher and subscriber), but not "not for replication" can i do this?
> thanks,
> manuel ralha
Conflicts with Merge Replication
I am seeing the following conflict message in a merge replication
system.
Statler is the subscriber (also, remote distributor) and
EMGSQL1 is the publisher
The row was updated at Statler.Caryn but could not be updated
at emgsql1.caryn. Can not update Tlightbox because TlightboxFile
exists.
Column Conflight WinnerConflict Loser
lightbox_id19881988
member_id10531053
lb_nameCustomerCustwomer
create_dt5/13/2005 12:00:00 am5/13/2005 12:00:00 AM
last_mod_dt
expiration_dt8/11/2005 12:00:00 am8/11/2005 12:00:00 am
email_check{74434092-E95F-40B6-9211-99700DB
rowguidrowguid_valueidentical
the table definition for tlightbox is:
CREATE TABLE [dbo].[TLightBox] (
[lightbox_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[member_id] [int] NULL ,
[lb_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[create_dt] [datetime] NULL ,
[last_mod_dt] [datetime] NULL ,
[expiration_dt] [datetime] NULL ,
[email_check] [uniqueidentifier] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TLightboxFile] (
[lightbox_id] [int] NOT NULL ,
[modeltalent_id] [int] NOT NULL ,
[rank] [int] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
These tables are on both servers.
Any Ideas?
Thanks
Dave
what is the relationship between TLightBox and TLightBoxFile?
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 Gresham" <gresham@.panix.com> wrote in message
news:d62p39$lks$1@.reader1.panix.com...
> I am seeing the following conflict message in a merge replication
> system.
> Statler is the subscriber (also, remote distributor) and
> EMGSQL1 is the publisher
> The row was updated at Statler.Caryn but could not be updated
> at emgsql1.caryn. Can not update Tlightbox because TlightboxFile
> exists.
> Column Conflight Winner Conflict Loser
> lightbox_id 1988 1988
> member_id 1053 1053
> lb_name Customer Custwomer
> create_dt 5/13/2005 12:00:00 am 5/13/2005 12:00:00 AM
> last_mod_dt
> expiration_dt 8/11/2005 12:00:00 am 8/11/2005 12:00:00 am
> email_check {74434092-E95F-40B6-9211-99700DB
> rowguid rowguid_value identical
>
> the table definition for tlightbox is:
> CREATE TABLE [dbo].[TLightBox] (
> [lightbox_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [member_id] [int] NULL ,
> [lb_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [create_dt] [datetime] NULL ,
> [last_mod_dt] [datetime] NULL ,
> [expiration_dt] [datetime] NULL ,
> [email_check] [uniqueidentifier] NULL ,
> [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[TLightboxFile] (
> [lightbox_id] [int] NOT NULL ,
> [modeltalent_id] [int] NOT NULL ,
> [rank] [int] NULL ,
> [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
> ) ON [PRIMARY]
> GO
>
> These tables are on both servers.
>
> Any Ideas?
> Thanks
>
> Dave
>
|||In article <ek417yCWFHA.2960@.TK2MSFTNGP15.phx.gbl>,
Hilary Cotter <hilary.cotter@.gmail.com> wrote:
>what is the relationship between TLightBox and TLightBoxFile?
>
I just looked at the tables in question and there is no
defined releationship set between them. The only relationship
defined for TlightBox was for another table called TMember
with MemberID as the relationship column.
>"David Gresham" <gresham@.panix.com> wrote in message
>news:d62p39$lks$1@.reader1.panix.com...
>
|||Just seeing if I had given you all the info you needed for
this question. This database is a legacy database where
the original developers are no longer around. There
are no fk's that would join the two tables in question.
Sincerely,
Dave Gresham
In article <ek417yCWFHA.2960@.TK2MSFTNGP15.phx.gbl>,
Hilary Cotter <hilary.cotter@.gmail.com> wrote:
>what is the relationship between TLightBox and TLightBoxFile?
>--
>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 Gresham" <gresham@.panix.com> wrote in message
>news:d62p39$lks$1@.reader1.panix.com...
>
|||Your problem does not make a whole lot of sense. the best approach is to try
to repro it.
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 Gresham" <gresham@.panix.com> wrote in message
news:d6algu$st6$1@.reader1.panix.com...
> Just seeing if I had given you all the info you needed for
> this question. This database is a legacy database where
> the original developers are no longer around. There
> are no fk's that would join the two tables in question.
> Sincerely,
>
> Dave Gresham
>
> In article <ek417yCWFHA.2960@.TK2MSFTNGP15.phx.gbl>,
> Hilary Cotter <hilary.cotter@.gmail.com> wrote:
>
system.
Statler is the subscriber (also, remote distributor) and
EMGSQL1 is the publisher
The row was updated at Statler.Caryn but could not be updated
at emgsql1.caryn. Can not update Tlightbox because TlightboxFile
exists.
Column Conflight WinnerConflict Loser
lightbox_id19881988
member_id10531053
lb_nameCustomerCustwomer
create_dt5/13/2005 12:00:00 am5/13/2005 12:00:00 AM
last_mod_dt
expiration_dt8/11/2005 12:00:00 am8/11/2005 12:00:00 am
email_check{74434092-E95F-40B6-9211-99700DB
rowguidrowguid_valueidentical
the table definition for tlightbox is:
CREATE TABLE [dbo].[TLightBox] (
[lightbox_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[member_id] [int] NULL ,
[lb_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[create_dt] [datetime] NULL ,
[last_mod_dt] [datetime] NULL ,
[expiration_dt] [datetime] NULL ,
[email_check] [uniqueidentifier] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TLightboxFile] (
[lightbox_id] [int] NOT NULL ,
[modeltalent_id] [int] NOT NULL ,
[rank] [int] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
These tables are on both servers.
Any Ideas?
Thanks
Dave
what is the relationship between TLightBox and TLightBoxFile?
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 Gresham" <gresham@.panix.com> wrote in message
news:d62p39$lks$1@.reader1.panix.com...
> I am seeing the following conflict message in a merge replication
> system.
> Statler is the subscriber (also, remote distributor) and
> EMGSQL1 is the publisher
> The row was updated at Statler.Caryn but could not be updated
> at emgsql1.caryn. Can not update Tlightbox because TlightboxFile
> exists.
> Column Conflight Winner Conflict Loser
> lightbox_id 1988 1988
> member_id 1053 1053
> lb_name Customer Custwomer
> create_dt 5/13/2005 12:00:00 am 5/13/2005 12:00:00 AM
> last_mod_dt
> expiration_dt 8/11/2005 12:00:00 am 8/11/2005 12:00:00 am
> email_check {74434092-E95F-40B6-9211-99700DB
> rowguid rowguid_value identical
>
> the table definition for tlightbox is:
> CREATE TABLE [dbo].[TLightBox] (
> [lightbox_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [member_id] [int] NULL ,
> [lb_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [create_dt] [datetime] NULL ,
> [last_mod_dt] [datetime] NULL ,
> [expiration_dt] [datetime] NULL ,
> [email_check] [uniqueidentifier] NULL ,
> [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[TLightboxFile] (
> [lightbox_id] [int] NOT NULL ,
> [modeltalent_id] [int] NOT NULL ,
> [rank] [int] NULL ,
> [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
> ) ON [PRIMARY]
> GO
>
> These tables are on both servers.
>
> Any Ideas?
> Thanks
>
> Dave
>
|||In article <ek417yCWFHA.2960@.TK2MSFTNGP15.phx.gbl>,
Hilary Cotter <hilary.cotter@.gmail.com> wrote:
>what is the relationship between TLightBox and TLightBoxFile?
>
I just looked at the tables in question and there is no
defined releationship set between them. The only relationship
defined for TlightBox was for another table called TMember
with MemberID as the relationship column.
>"David Gresham" <gresham@.panix.com> wrote in message
>news:d62p39$lks$1@.reader1.panix.com...
>
|||Just seeing if I had given you all the info you needed for
this question. This database is a legacy database where
the original developers are no longer around. There
are no fk's that would join the two tables in question.
Sincerely,
Dave Gresham
In article <ek417yCWFHA.2960@.TK2MSFTNGP15.phx.gbl>,
Hilary Cotter <hilary.cotter@.gmail.com> wrote:
>what is the relationship between TLightBox and TLightBoxFile?
>--
>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 Gresham" <gresham@.panix.com> wrote in message
>news:d62p39$lks$1@.reader1.panix.com...
>
|||Your problem does not make a whole lot of sense. the best approach is to try
to repro it.
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 Gresham" <gresham@.panix.com> wrote in message
news:d6algu$st6$1@.reader1.panix.com...
> Just seeing if I had given you all the info you needed for
> this question. This database is a legacy database where
> the original developers are no longer around. There
> are no fk's that would join the two tables in question.
> Sincerely,
>
> Dave Gresham
>
> In article <ek417yCWFHA.2960@.TK2MSFTNGP15.phx.gbl>,
> Hilary Cotter <hilary.cotter@.gmail.com> wrote:
>
Conflicts in Updateable Subscriptions
I've been experiencing conflicts in my replication system that I can't seem to get my head around. The following is the scenario:
3 sqlservers, all running sql server 2005. Server B is the publisher and Server A and Server C (64 bit) are subscribers. The Queue Reader Agent runs on the publisher. I set up transactional repl with updateable subscriptions with the default conflict resolution policy of 'Publisher wins'.
There are 2 kinds of processes: 1. Nightly batch updates and 2. Daytime updates by real clients. The Nightly batch updates runs an on the publisher, which is B. Batch updates are massive updates and running it on the publisher makes sense and it works like a charm. Online updates are made on the subscriber 'C'. This subscriber is set to Queued update mode, and everyday I see a significant number of transactions that are detected as conflicts and the Publisher wins. As a result the changes made on Server C are getting lost. I have verified that no user/client is logged into Server B to do any updates. Users complain that their updates are lost. This is the most puzzling and frustrating bit. I don't see how a conflict can happen if nobody is updating data on the Publisher during the day. SQL Updates on Server C are getting rolled back on a conflict detection because the "Publisher wins", and SQL Inserts on server C are getting deleted because they don't exist on the publisher. Now, how can a insert done on the subscriber be marked as a conflict. There is no row on the publisher to compare the unique guid with, how can it be a conflict?
And the Queue Reader Agent crashes every 3-4 days. No useful information except it creates a dump file for which users have no tools to read it.
Has anyone seen this behavior ? Or is there a known bug in the QueueReader Agent?
My users are losing faith in the replication system and so am I.
Thanks for your time,
-chiraj.
Hi,
Have you use the replication conflict viewer to see who is the winner of the conflict and what type is the winner transactions? It may be able to help you figure out where the conflict is from.
Peng
|||Hi Peng,
Thank you for responding.
I looked at the conflict viewer, and the conflict winner is always the Publisher, and the conflct type is "Update conflict" WHen I click on any the rows in the conflict list, I get a popup error "Incorrect syntax near the keyword 'from', Error 21465, severity 16, state 1 raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage. (Microsoft SQL Server,Error: 156)". It pops up twice and when I get rid of them, the bottom pane of the viewer has the info: Queued update conflict. Publisher wins. Updating Subscriber. There are 3 columns: Column name, Conflict winner and Conflict loser. The Conflict winner column says "this row no longer exists in 'schema.tablename'". The conflict loser column has values for the table columns. I don't know the contents of the winner, maybe because of the earlier error with the popup message.
Any ideas?
Thanks.
|||I did some digging and the popup error comes from sys.sp_MSgettrancftsrcrow procedure. Towards the end of the procedure, you have :
if (@.is_debug = 0)
begin
select @.cmd = N'select cmdtext from MSsrcrow_codetext order by step'
,@.dbname = db_name()
exec @.retcode = sys.xp_execresultset @.cmd, @.dbname
if (@.@.error != 0 or @.retcode != 0)
begin
raiserror(21465, 16, 1, 'xp_execresultset')
return (1)
end
end
else
I wonder why the message for #21465 is not installed.
I basically want to see and compare all the columns ( except timestamp ) from the subscriber and the publisher and see why there is a conflict.
I made sure the data was in sync this morning and then I start seeing conflicts during the day although no one is logged into the publisher. Beats me. Is there any trace flag I can turn on for replication to see what's going on?
-chiraj.
Conflicts in Updateable Subscriptions
I've been experiencing conflicts in my replication system that I can't seem to get my head around. The following is the scenario:
3 sqlservers, all running sql server 2005. Server B is the publisher and Server A and Server C (64 bit) are subscribers. The Queue Reader Agent runs on the publisher. I set up transactional repl with updateable subscriptions with the default conflict resolution policy of 'Publisher wins'.
There are 2 kinds of processes: 1. Nightly batch updates and 2. Daytime updates by real clients. The Nightly batch updates runs an on the publisher, which is B. Batch updates are massive updates and running it on the publisher makes sense and it works like a charm. Online updates are made on the subscriber 'C'. This subscriber is set to Queued update mode, and everyday I see a significant number of transactions that are detected as conflicts and the Publisher wins. As a result the changes made on Server C are getting lost. I have verified that no user/client is logged into Server B to do any updates. Users complain that their updates are lost. This is the most puzzling and frustrating bit. I don't see how a conflict can happen if nobody is updating data on the Publisher during the day. SQL Updates on Server C are getting rolled back on a conflict detection because the "Publisher wins", and SQL Inserts on server C are getting deleted because they don't exist on the publisher. Now, how can a insert done on the subscriber be marked as a conflict. There is no row on the publisher to compare the unique guid with, how can it be a conflict?
And the Queue Reader Agent crashes every 3-4 days. No useful information except it creates a dump file for which users have no tools to read it.
Has anyone seen this behavior ? Or is there a known bug in the QueueReader Agent?
My users are losing faith in the replication system and so am I.
Thanks for your time,
-chiraj.
Hi,
Have you use the replication conflict viewer to see who is the winner of the conflict and what type is the winner transactions? It may be able to help you figure out where the conflict is from.
Peng
|||Hi Peng,
Thank you for responding.
I looked at the conflict viewer, and the conflict winner is always the Publisher, and the conflct type is "Update conflict" WHen I click on any the rows in the conflict list, I get a popup error "Incorrect syntax near the keyword 'from', Error 21465, severity 16, state 1 raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage. (Microsoft SQL Server,Error: 156)". It pops up twice and when I get rid of them, the bottom pane of the viewer has the info: Queued update conflict. Publisher wins. Updating Subscriber. There are 3 columns: Column name, Conflict winner and Conflict loser. The Conflict winner column says "this row no longer exists in 'schema.tablename'". The conflict loser column has values for the table columns. I don't know the contents of the winner, maybe because of the earlier error with the popup message.
Any ideas?
Thanks.
|||I did some digging and the popup error comes from sys.sp_MSgettrancftsrcrow procedure. Towards the end of the procedure, you have :
if (@.is_debug = 0)
begin
select @.cmd = N'select cmdtext from MSsrcrow_codetext order by step'
,@.dbname = db_name()
exec @.retcode = sys.xp_execresultset @.cmd, @.dbname
if (@.@.error != 0 or @.retcode != 0)
begin
raiserror(21465, 16, 1, 'xp_execresultset')
return (1)
end
end
else
I wonder why the message for #21465 is not installed.
I basically want to see and compare all the columns ( except timestamp ) from the subscriber and the publisher and see why there is a conflict.
I made sure the data was in sync this morning and then I start seeing conflicts during the day although no one is logged into the publisher. Beats me. Is there any trace flag I can turn on for replication to see what's going on?
-chiraj.
Conflicts in Updateable Subscriptions
I've been experiencing conflicts in my replication system that I can't seem to get my head around. The following is the scenario:
3 sqlservers, all running sql server 2005. Server B is the publisher and Server A and Server C (64 bit) are subscribers. The Queue Reader Agent runs on the publisher. I set up transactional repl with updateable subscriptions with the default conflict resolution policy of 'Publisher wins'.
There are 2 kinds of processes: 1. Nightly batch updates and 2. Daytime updates by real clients. The Nightly batch updates runs an on the publisher, which is B. Batch updates are massive updates and running it on the publisher makes sense and it works like a charm. Online updates are made on the subscriber 'C'. This subscriber is set to Queued update mode, and everyday I see a significant number of transactions that are detected as conflicts and the Publisher wins. As a result the changes made on Server C are getting lost. I have verified that no user/client is logged into Server B to do any updates. Users complain that their updates are lost. This is the most puzzling and frustrating bit. I don't see how a conflict can happen if nobody is updating data on the Publisher during the day. SQL Updates on Server C are getting rolled back on a conflict detection because the "Publisher wins", and SQL Inserts on server C are getting deleted because they don't exist on the publisher. Now, how can a insert done on the subscriber be marked as a conflict. There is no row on the publisher to compare the unique guid with, how can it be a conflict?
And the Queue Reader Agent crashes every 3-4 days. No useful information except it creates a dump file for which users have no tools to read it.
Has anyone seen this behavior ? Or is there a known bug in the QueueReader Agent?
My users are losing faith in the replication system and so am I.
Thanks for your time,
-chiraj.
Hi,
Have you use the replication conflict viewer to see who is the winner of the conflict and what type is the winner transactions? It may be able to help you figure out where the conflict is from.
Peng
|||Hi Peng,
Thank you for responding.
I looked at the conflict viewer, and the conflict winner is always the Publisher, and the conflct type is "Update conflict" WHen I click on any the rows in the conflict list, I get a popup error "Incorrect syntax near the keyword 'from', Error 21465, severity 16, state 1 raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage. (Microsoft SQL Server,Error: 156)". It pops up twice and when I get rid of them, the bottom pane of the viewer has the info: Queued update conflict. Publisher wins. Updating Subscriber. There are 3 columns: Column name, Conflict winner and Conflict loser. The Conflict winner column says "this row no longer exists in 'schema.tablename'". The conflict loser column has values for the table columns. I don't know the contents of the winner, maybe because of the earlier error with the popup message.
Any ideas?
Thanks.
|||I did some digging and the popup error comes from sys.sp_MSgettrancftsrcrow procedure. Towards the end of the procedure, you have :
if (@.is_debug = 0)
begin
select @.cmd = N'select cmdtext from MSsrcrow_codetext order by step'
,@.dbname = db_name()
exec @.retcode = sys.xp_execresultset @.cmd, @.dbname
if (@.@.error != 0 or @.retcode != 0)
begin
raiserror(21465, 16, 1, 'xp_execresultset')
return (1)
end
end
else
I wonder why the message for #21465 is not installed.
I basically want to see and compare all the columns ( except timestamp ) from the subscriber and the publisher and see why there is a conflict.
I made sure the data was in sync this morning and then I start seeing conflicts during the day although no one is logged into the publisher. Beats me. Is there any trace flag I can turn on for replication to see what's going on?
-chiraj.
Conflicts in Merge Replication
Hi,
I am using SQL Server 2000 Merge Replication. Sometimes when the data is replicated there are conflicts which when examined show it is due to voliation of foreign key constraint. But the data (keys) in already present in the master tables. Is there a way to give an order to the way the tables are replicated. This is so because i think the data in the details table is relpicated first instead of the master table. The conflicts are resolved properly when done using the conflict viewer.
In SQL Server 2000, it is not possible to specify an order. An order can be speficied in SQL Server 2005.|||
Li Zhang wrote:
In SQL Server 2000, it is not possible to specify an order. An order can be speficied in SQL Server 2005.
Then, could you tell me how do you specify the order in sql server 2005?
Cheers
|||Agreed, I have the exact same issue - how do you specify an order in 2005?|||Hello,
Use the processing_order parameter of sp_addmergearticle stored proc, when adding articles to the publication.
[ @.processing_order = ] processing_order
Indicates the processing order of articles in a merge publication. processing_order is int, with a default of 0. 0 specifies that the article is unordered, and any other value represents the ordinal value of the processing order for this article. Articles are processed in order from lowest to highest value. If two articles have the same value, processing order is determined by the order of the article nickname in the sysmergearticles (Transact-SQL) system table. For more information, see Specifying the Processing Order of Merge Articles.
Thanks
Udaya.
|||Hey,
I don't think there is a way to specify the order under sql server 2005.
Junhai Kan
Conflicts in Merge Replication
Hi,
I am using SQL Server 2000 Merge Replication. Sometimes when the data is replicated there are conflicts which when examined show it is due to voliation of foreign key constraint. But the data (keys) in already present in the master tables. Is there a way to give an order to the way the tables are replicated. This is so because i think the data in the details table is relpicated first instead of the master table. The conflicts are resolved properly when done using the conflict viewer.
In SQL Server 2000, it is not possible to specify an order. An order can be speficied in SQL Server 2005.|||
In SQL Server 2000, it is not possible to specify an order. An order can be speficied in SQL Server 2005.
Then, could you tell me how do you specify the order in sql server 2005?
Cheers
|||Agreed, I have the exact same issue - how do you specify an order in 2005?|||Hello,
Use the processing_order parameter of sp_addmergearticle stored proc, when adding articles to the publication.
[ @.processing_order = ] processing_order
Indicates the processing order of articles in a merge publication. processing_order is int, with a default of 0. 0 specifies that the article is unordered, and any other value represents the ordinal value of the processing order for this article. Articles are processed in order from lowest to highest value. If two articles have the same value, processing order is determined by the order of the article nickname in the sysmergearticles (Transact-SQL) system table. For more information, see Specifying the Processing Order of Merge Articles.
Thanks
Udaya.
|||Hey,
I don't think there is a way to specify the order under sql server 2005.
Junhai Kan
Conflicts in Merge Replication
Hi,
I am using SQL Server 2000 Merge Replication. Sometimes when the data is replicated there are conflicts which when examined show it is due to voliation of foreign key constraint. But the data (keys) in already present in the master tables. Is there a way to give an order to the way the tables are replicated. This is so because i think the data in the details table is relpicated first instead of the master table. The conflicts are resolved properly when done using the conflict viewer.
In SQL Server 2000, it is not possible to specify an order. An order can be speficied in SQL Server 2005.|||
In SQL Server 2000, it is not possible to specify an order. An order can be speficied in SQL Server 2005.
Then, could you tell me how do you specify the order in sql server 2005?
Cheers
|||Agreed, I have the exact same issue - how do you specify an order in 2005?|||Hello,
Use the processing_order parameter of sp_addmergearticle stored proc, when adding articles to the publication.
[ @.processing_order = ] processing_order
Indicates the processing order of articles in a merge publication. processing_order is int, with a default of 0. 0 specifies that the article is unordered, and any other value represents the ordinal value of the processing order for this article. Articles are processed in order from lowest to highest value. If two articles have the same value, processing order is determined by the order of the article nickname in the sysmergearticles (Transact-SQL) system table. For more information, see Specifying the Processing Order of Merge Articles.
Thanks
Udaya.
|||Hey,
I don't think there is a way to specify the order under sql server 2005.
Junhai Kan
Conflicts during replication
Hello,
I read in articles that the SQL Server Reconciler solves conflicts which can
occur during the replication.
The kind of conflicts are:
1) If more subscriber want to modify the same row on the publisher database
at the same time.
2) If a certain row is changed at the subscriber database as well as on the
publisher database - and so the question is which database (subscriber or
publisher) will be updated with the information of the other.
Can anybody explain me how such conflict solution could be look like for my
above mentioned scenarios?
thx
mat
Conflicts fall into three categories
1) primary key collisions, where the publisher and subscriber enter a row
with the same value for the primary key between merge agent runs
2) a row is updated on the publisher or subscriber and deleted on the
subscriber or publisher. When the merge agent runs it attempts to update a
row which no longer exists.
3) you update the same row on both sides of a replication solution and do
not have column level tracking enabled for your publication.
In all three conflict types these winning and loosing changes will be logged
in conflict tables and you can view them using the conflict viewer.
I hope this answers your question. I am not really able to understand your
question.
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
"mathon" <mathon@.discussions.microsoft.com> wrote in message
news:7CCCFD74-4B03-423A-96D9-835A5B604230@.microsoft.com...
> Hello,
> I read in articles that the SQL Server Reconciler solves conflicts which
can
> occur during the replication.
> The kind of conflicts are:
> 1) If more subscriber want to modify the same row on the publisher
database
> at the same time.
> 2) If a certain row is changed at the subscriber database as well as on
the
> publisher database - and so the question is which database (subscriber or
> publisher) will be updated with the information of the other.
> Can anybody explain me how such conflict solution could be look like for
my
> above mentioned scenarios?
> thx
> mat
|||Hello
That is my problem of understanding. How does the SQL Server Reconciler
solve these three conflicts?
mathon
> 1) primary key collisions, where the publisher and subscriber enter a row
> with the same value for the primary key between merge agent runs
> 2) a row is updated on the publisher or subscriber and deleted on the
> subscriber or publisher. When the merge agent runs it attempts to update a
> row which no longer exists.
> 3) you update the same row on both sides of a replication solution and do
> not have column level tracking enabled for your publication.
|||Unfortunately I do not find any good online resource where it is explained
how the Reconciler solves these conficts...?
thx
mat
I read in articles that the SQL Server Reconciler solves conflicts which can
occur during the replication.
The kind of conflicts are:
1) If more subscriber want to modify the same row on the publisher database
at the same time.
2) If a certain row is changed at the subscriber database as well as on the
publisher database - and so the question is which database (subscriber or
publisher) will be updated with the information of the other.
Can anybody explain me how such conflict solution could be look like for my
above mentioned scenarios?
thx
mat
Conflicts fall into three categories
1) primary key collisions, where the publisher and subscriber enter a row
with the same value for the primary key between merge agent runs
2) a row is updated on the publisher or subscriber and deleted on the
subscriber or publisher. When the merge agent runs it attempts to update a
row which no longer exists.
3) you update the same row on both sides of a replication solution and do
not have column level tracking enabled for your publication.
In all three conflict types these winning and loosing changes will be logged
in conflict tables and you can view them using the conflict viewer.
I hope this answers your question. I am not really able to understand your
question.
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
"mathon" <mathon@.discussions.microsoft.com> wrote in message
news:7CCCFD74-4B03-423A-96D9-835A5B604230@.microsoft.com...
> Hello,
> I read in articles that the SQL Server Reconciler solves conflicts which
can
> occur during the replication.
> The kind of conflicts are:
> 1) If more subscriber want to modify the same row on the publisher
database
> at the same time.
> 2) If a certain row is changed at the subscriber database as well as on
the
> publisher database - and so the question is which database (subscriber or
> publisher) will be updated with the information of the other.
> Can anybody explain me how such conflict solution could be look like for
my
> above mentioned scenarios?
> thx
> mat
|||Hello
That is my problem of understanding. How does the SQL Server Reconciler
solve these three conflicts?
mathon
> 1) primary key collisions, where the publisher and subscriber enter a row
> with the same value for the primary key between merge agent runs
> 2) a row is updated on the publisher or subscriber and deleted on the
> subscriber or publisher. When the merge agent runs it attempts to update a
> row which no longer exists.
> 3) you update the same row on both sides of a replication solution and do
> not have column level tracking enabled for your publication.
|||Unfortunately I do not find any good online resource where it is explained
how the Reconciler solves these conficts...?
thx
mat
Conflicts due to table order
Merge replication, SQL 2000. Does anyone know if there is a problem with
syncronizing caused by the physical order of the inserts/updates in related
tables? I am getting some conflicts that indicate a failure due to "...
Referential integrity rules require a related record in the table
'<tablename>'
When I check the tables on the publisher they are all there and fine, but
when a laptop user tries to synch they get the above message and the related
table records do not get added.
David
Modify your tables so that each constraint has a not for replication clause
on all constraints.
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" <dlchase@.lifetimeinc.com> wrote in message
news:OmfYQNuIHHA.3952@.TK2MSFTNGP02.phx.gbl...
> Merge replication, SQL 2000. Does anyone know if there is a problem with
> syncronizing caused by the physical order of the inserts/updates in
> related tables? I am getting some conflicts that indicate a failure due
> to "... Referential integrity rules require a related record in the table
> '<tablename>'
> When I check the tables on the publisher they are all there and fine, but
> when a laptop user tries to synch they get the above message and the
> related table records do not get added.
> David
>
|||When I look at table properties and the Relationships tab, it has the box
checked "Enforce relationship for replication". Is that what you are
referring to...and if so, do I uncheck it?
Also, I assume I will need to completely re-create all merge publications to
the laptops?
David
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ulOPb%23xIHHA.1252@.TK2MSFTNGP02.phx.gbl...
> Modify your tables so that each constraint has a not for replication
> clause on all constraints.
> --
> 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" <dlchase@.lifetimeinc.com> wrote in message
> news:OmfYQNuIHHA.3952@.TK2MSFTNGP02.phx.gbl...
>
|||Yes, that is it. I think you will need re-create all publications and
subscriptions.
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" <dlchase@.lifetimeinc.com> wrote in message
news:u$G8sG8IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> When I look at table properties and the Relationships tab, it has the box
> checked "Enforce relationship for replication". Is that what you are
> referring to...and if so, do I uncheck it?
> Also, I assume I will need to completely re-create all merge publications
> to the laptops?
> David
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ulOPb%23xIHHA.1252@.TK2MSFTNGP02.phx.gbl...
>
syncronizing caused by the physical order of the inserts/updates in related
tables? I am getting some conflicts that indicate a failure due to "...
Referential integrity rules require a related record in the table
'<tablename>'
When I check the tables on the publisher they are all there and fine, but
when a laptop user tries to synch they get the above message and the related
table records do not get added.
David
Modify your tables so that each constraint has a not for replication clause
on all constraints.
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" <dlchase@.lifetimeinc.com> wrote in message
news:OmfYQNuIHHA.3952@.TK2MSFTNGP02.phx.gbl...
> Merge replication, SQL 2000. Does anyone know if there is a problem with
> syncronizing caused by the physical order of the inserts/updates in
> related tables? I am getting some conflicts that indicate a failure due
> to "... Referential integrity rules require a related record in the table
> '<tablename>'
> When I check the tables on the publisher they are all there and fine, but
> when a laptop user tries to synch they get the above message and the
> related table records do not get added.
> David
>
|||When I look at table properties and the Relationships tab, it has the box
checked "Enforce relationship for replication". Is that what you are
referring to...and if so, do I uncheck it?
Also, I assume I will need to completely re-create all merge publications to
the laptops?
David
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ulOPb%23xIHHA.1252@.TK2MSFTNGP02.phx.gbl...
> Modify your tables so that each constraint has a not for replication
> clause on all constraints.
> --
> 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" <dlchase@.lifetimeinc.com> wrote in message
> news:OmfYQNuIHHA.3952@.TK2MSFTNGP02.phx.gbl...
>
|||Yes, that is it. I think you will need re-create all publications and
subscriptions.
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" <dlchase@.lifetimeinc.com> wrote in message
news:u$G8sG8IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> When I look at table properties and the Relationships tab, it has the box
> checked "Enforce relationship for replication". Is that what you are
> referring to...and if so, do I uncheck it?
> Also, I assume I will need to completely re-create all merge publications
> to the laptops?
> David
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ulOPb%23xIHHA.1252@.TK2MSFTNGP02.phx.gbl...
>
Conflicting SQL Version Info
Hi,
I am managing an instance of SQL server which is running replication, I
am not sure what SP is running on this version as I am getting
conflicting information when I run "Print @.@.Version", the following is
the result:
"Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)"
The version number tells me it is SP4 but the last line "Build 3790:
Service Pack 1" tells me otherwise.
Has anyone come across this before? I tried running the SP4 upgrade but
got an error during installation, something about replication.
Also the subscribers are running the desktop engine version of SQL and
have a different version than the main server. When I run "Print
@.@.Version" I get:
"Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)"
Will this cause me issues? Should I upgrade the subscribers to SP4 as
well?
Please help.
Ben
the last line refers to the version of the os. The first one is SQL 2000 sp
4 running on win2003 sp 1. The second is SQL 2000 RTM, running on Windows xp
sp2.
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
"Benzine" <bfausti@.gmail.com> wrote in message
news:1168386601.375776.238150@.i56g2000hsf.googlegr oups.com...
> Hi,
> I am managing an instance of SQL server which is running replication, I
> am not sure what SP is running on this version as I am getting
> conflicting information when I run "Print @.@.Version", the following is
> the result:
> "Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)"
> The version number tells me it is SP4 but the last line "Build 3790:
> Service Pack 1" tells me otherwise.
> Has anyone come across this before? I tried running the SP4 upgrade but
> got an error during installation, something about replication.
> Also the subscribers are running the desktop engine version of SQL and
> have a different version than the main server. When I run "Print
> @.@.Version" I get:
> "Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)"
> Will this cause me issues? Should I upgrade the subscribers to SP4 as
> well?
> Please help.
> Ben
>
I am managing an instance of SQL server which is running replication, I
am not sure what SP is running on this version as I am getting
conflicting information when I run "Print @.@.Version", the following is
the result:
"Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)"
The version number tells me it is SP4 but the last line "Build 3790:
Service Pack 1" tells me otherwise.
Has anyone come across this before? I tried running the SP4 upgrade but
got an error during installation, something about replication.
Also the subscribers are running the desktop engine version of SQL and
have a different version than the main server. When I run "Print
@.@.Version" I get:
"Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)"
Will this cause me issues? Should I upgrade the subscribers to SP4 as
well?
Please help.
Ben
the last line refers to the version of the os. The first one is SQL 2000 sp
4 running on win2003 sp 1. The second is SQL 2000 RTM, running on Windows xp
sp2.
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
"Benzine" <bfausti@.gmail.com> wrote in message
news:1168386601.375776.238150@.i56g2000hsf.googlegr oups.com...
> Hi,
> I am managing an instance of SQL server which is running replication, I
> am not sure what SP is running on this version as I am getting
> conflicting information when I run "Print @.@.Version", the following is
> the result:
> "Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)"
> The version number tells me it is SP4 but the last line "Build 3790:
> Service Pack 1" tells me otherwise.
> Has anyone come across this before? I tried running the SP4 upgrade but
> got an error during installation, something about replication.
> Also the subscribers are running the desktop engine version of SQL and
> have a different version than the main server. When I run "Print
> @.@.Version" I get:
> "Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)"
> Will this cause me issues? Should I upgrade the subscribers to SP4 as
> well?
> Please help.
> Ben
>
Friday, 24 February 2012
Conflict Viewer hangs
Hello, All!
MS SQL 2k EE SP3a, merge replication.
After long time Conflict Viewer began to hang when viewing conflicts on some
articles (not on all). There is no waiting locks. Workload is minimal.
What's wrong?
Thanx in advance.
I've run it under debugger and found that it runs an infinite loop in the
sqloledb.dll
No commands sent to server.
Guys from MS - whats wrong?
"Ray D" wrote:
> Hello, All!
> MS SQL 2k EE SP3a, merge replication.
> After long time Conflict Viewer began to hang when viewing conflicts on some
> articles (not on all). There is no waiting locks. Workload is minimal.
> What's wrong?
> Thanx in advance.
MS SQL 2k EE SP3a, merge replication.
After long time Conflict Viewer began to hang when viewing conflicts on some
articles (not on all). There is no waiting locks. Workload is minimal.
What's wrong?
Thanx in advance.
I've run it under debugger and found that it runs an infinite loop in the
sqloledb.dll
No commands sent to server.
Guys from MS - whats wrong?
"Ray D" wrote:
> Hello, All!
> MS SQL 2k EE SP3a, merge replication.
> After long time Conflict Viewer began to hang when viewing conflicts on some
> articles (not on all). There is no waiting locks. Workload is minimal.
> What's wrong?
> Thanx in advance.
Conflict tables
Hello,
We are creating merge replication.
Is there any way to make the conflict tables to be create
in another database then the publisher database?
or to move them after creation.
Many Thanks for reply.
No, I don't think the conflict tables can be moved.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DIRYU" <anonymous@.discussions.microsoft.com> wrote in message
news:653f01c49336$0acd99e0$a301280a@.phx.gbl...
> Hello,
> We are creating merge replication.
> Is there any way to make the conflict tables to be create
> in another database then the publisher database?
> or to move them after creation.
>
> Many Thanks for reply.
We are creating merge replication.
Is there any way to make the conflict tables to be create
in another database then the publisher database?
or to move them after creation.
Many Thanks for reply.
No, I don't think the conflict tables can be moved.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DIRYU" <anonymous@.discussions.microsoft.com> wrote in message
news:653f01c49336$0acd99e0$a301280a@.phx.gbl...
> Hello,
> We are creating merge replication.
> Is there any way to make the conflict tables to be create
> in another database then the publisher database?
> or to move them after creation.
>
> Many Thanks for reply.
Conflict tables
Hello,
We are creating merge replication.
Is there any way to make the conflict tables to be create
in another database then the publisher database?
or to move them after creation.
Many Thanks for reply.No, I don't think the conflict tables can be moved.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DIRYU" <anonymous@.discussions.microsoft.com> wrote in message
news:653f01c49336$0acd99e0$a301280a@.phx.gbl...
> Hello,
> We are creating merge replication.
> Is there any way to make the conflict tables to be create
> in another database then the publisher database?
> or to move them after creation.
>
> Many Thanks for reply.
We are creating merge replication.
Is there any way to make the conflict tables to be create
in another database then the publisher database?
or to move them after creation.
Many Thanks for reply.No, I don't think the conflict tables can be moved.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DIRYU" <anonymous@.discussions.microsoft.com> wrote in message
news:653f01c49336$0acd99e0$a301280a@.phx.gbl...
> Hello,
> We are creating merge replication.
> Is there any way to make the conflict tables to be create
> in another database then the publisher database?
> or to move them after creation.
>
> Many Thanks for reply.
CONFLICT TABLES
Hi,
I am dealing with merge replication.
Is there any option(query) to view the lattest conflict records(for current
date)
for all the conflicted tables?
Thanks,
Soura.
You can query the conflict tables directly on the publisher and sort by
MSrepl_create_time desc. You can view conflicts using the conflict viewer,
but they will be presented in the order they occur, i.e. the earliest will
be first.
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
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:3398E5D9-EDA6-460F-B9C3-7921CF7DDFF5@.microsoft.com...
> Hi,
> I am dealing with merge replication.
> Is there any option(query) to view the lattest conflict records(for
current
> date)
> for all the conflicted tables?
> Thanks,
> Soura.
>
I am dealing with merge replication.
Is there any option(query) to view the lattest conflict records(for current
date)
for all the conflicted tables?
Thanks,
Soura.
You can query the conflict tables directly on the publisher and sort by
MSrepl_create_time desc. You can view conflicts using the conflict viewer,
but they will be presented in the order they occur, i.e. the earliest will
be first.
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
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:3398E5D9-EDA6-460F-B9C3-7921CF7DDFF5@.microsoft.com...
> Hi,
> I am dealing with merge replication.
> Is there any option(query) to view the lattest conflict records(for
current
> date)
> for all the conflicted tables?
> Thanks,
> Soura.
>
Conflict table Problem
Hi All,
I Want to know the following
1) when are the conflict tables in Merge replication created and are they dropped automatically after certain duration or do they exists once created?
2) data for how many days exist on the conflict tables.
Lindly reply even for one question its very urgent for me
Thanks in advance to all u helpfull people out there
1. MSmerge_conflicts_info table is created when you create the publication. The conflict tables for each article are created in the publication database, subscription database, or both (the default), depending on the value specified for the @.conflict_logging parameter of sp_addmergepublication. Each conflict table has the same structure as the article on which it is based, with the addition of the origin_datasource_id column. The Merge Agent deletes data from the conflict table if it is older than the conflict retention period for the publication, which is specified using the @.conflict_retention parameter of sp_addmergepublication (the default is 14 days).
2. The Merge Agent deletes data from the conflict table if it is older than the conflict retention period for the publication, which is specified using the @.conflict_retention parameter of sp_addmergepublication (the default is 14 days).
hope that helps
Conflict table Problem
Hi All,
I Want to know the following
1) when are the conflict tables in Merge replication created and are they dropped automatically after certain duration or do they exists once created?
2) data for how many days exist on the conflict tables.
Lindly reply even for one question its very urgent for me
Thanks in advance to all u helpfull people out there
1. MSmerge_conflicts_info table is created when you create the publication. The conflict tables for each article are created in the publication database, subscription database, or both (the default), depending on the value specified for the @.conflict_logging parameter of sp_addmergepublication. Each conflict table has the same structure as the article on which it is based, with the addition of the origin_datasource_id column. The Merge Agent deletes data from the conflict table if it is older than the conflict retention period for the publication, which is specified using the @.conflict_retention parameter of sp_addmergepublication (the default is 14 days).
2. The Merge Agent deletes data from the conflict table if it is older than the conflict retention period for the publication, which is specified using the @.conflict_retention parameter of sp_addmergepublication (the default is 14 days).
hope that helps
Conflict table cleanup interval
What replication parameter sets the interval in which data in the conflict tables is deleted in SQL Server 2000 SP4?Apparently when the snapshots are generated the conflict tables are cleaned out as part of the process. This was causing my conflicts to disappear each night when the snapshot agent ran.|||
The publication is a filtered merge publication.|||
Rerunning snapshot should not be cleaning the tables.
@.conflict_retention governs how long the data will be kept in these tables before purging.
|||If I have data in the conflict tables and then execute the snapshot SQL Agent job the conflict tables are cleaned out. I have the @.conflict_retention value set to 90 days.The publication is a filtered merge publication.|||
Sorry I missed your earlier comment that this was SQL Server 2000.
This is a known issue in SQL Server 2000 and has been fixed in SQL Server 2005.
Conflict resolution for Transactional Replication with Updating Subscribers
Hi.
I'm trying to establish whether Transactional Replication with
Updating Subscribers meets my requirements. I have chosen to use
"publisher wins" for the conflict resolution. (I'm using SqlServer
2005 Developer Edition on Win XP SP2 on both machines)
When I cause a conflict to occur by making an update to rows with the
same PK on both servers, the publisher indicates that there has been a
conflict, and that it chose the publisher as the winner. However, the
losing transaction on the subscriber does not seem to be rolled back
like this article says it should: http://msdn2.microsoft.com/en-us/library/ms151177.aspx.
I'm then left in the state where the two tables are out of sync.
Any ideas anyone?
Cheers!
Some extra info...
I forgot to mention earlier that I'm using a push subscription.
I've found that if I choose 'subscriber-wins' instead, then it seems
to work OK, but unfortunately this is not what I require.
'Normal' changes to data on both servers are correctly replicated to
the other server. It's just publisher-wins conflict resolution that
doesn't seem to work. The conflict is detected, but the publisher's
data is not applied to the subscriber (i.e. the subscriber change is
not rolled back).. Any further changes to that row fail to go through
following the conflict.
Any comments appreciated,
Rich.
On 23 Feb, 08:22, ricisb...@.yahoo.co.uk wrote:
> Hi.
> I'm trying to establish whether Transactional Replication with
> Updating Subscribers meets my requirements. I have chosen to use
> "publisher wins" for the conflict resolution. (I'm using SqlServer
> 2005 Developer Edition on Win XP SP2 on both machines)
> When I cause a conflict to occur by making an update to rows with the
> same PK on both servers, the publisher indicates that there has been a
> conflict, and that it chose the publisher as the winner. However, the
> losing transaction on the subscriber does not seem to be rolled back
> like this article says it should:http://msdn2.microsoft.com/en-us/library/ms151177.aspx.
> I'm then left in the state where the two tables are out of sync.
> Any ideas anyone?
> Cheers!
I'm trying to establish whether Transactional Replication with
Updating Subscribers meets my requirements. I have chosen to use
"publisher wins" for the conflict resolution. (I'm using SqlServer
2005 Developer Edition on Win XP SP2 on both machines)
When I cause a conflict to occur by making an update to rows with the
same PK on both servers, the publisher indicates that there has been a
conflict, and that it chose the publisher as the winner. However, the
losing transaction on the subscriber does not seem to be rolled back
like this article says it should: http://msdn2.microsoft.com/en-us/library/ms151177.aspx.
I'm then left in the state where the two tables are out of sync.
Any ideas anyone?
Cheers!
Some extra info...
I forgot to mention earlier that I'm using a push subscription.
I've found that if I choose 'subscriber-wins' instead, then it seems
to work OK, but unfortunately this is not what I require.
'Normal' changes to data on both servers are correctly replicated to
the other server. It's just publisher-wins conflict resolution that
doesn't seem to work. The conflict is detected, but the publisher's
data is not applied to the subscriber (i.e. the subscriber change is
not rolled back).. Any further changes to that row fail to go through
following the conflict.
Any comments appreciated,
Rich.
On 23 Feb, 08:22, ricisb...@.yahoo.co.uk wrote:
> Hi.
> I'm trying to establish whether Transactional Replication with
> Updating Subscribers meets my requirements. I have chosen to use
> "publisher wins" for the conflict resolution. (I'm using SqlServer
> 2005 Developer Edition on Win XP SP2 on both machines)
> When I cause a conflict to occur by making an update to rows with the
> same PK on both servers, the publisher indicates that there has been a
> conflict, and that it chose the publisher as the winner. However, the
> losing transaction on the subscriber does not seem to be rolled back
> like this article says it should:http://msdn2.microsoft.com/en-us/library/ms151177.aspx.
> I'm then left in the state where the two tables are out of sync.
> Any ideas anyone?
> Cheers!
Subscribe to:
Posts (Atom)