SQL Configure: conflicts

Showing posts with label conflicts. Show all posts

Saturday, 25 February 2012

Conflicts?

Does anyone know if there is a conflict between MS SQL 7.0 and Sybase's SQL client running on the same PC? Reason is I have a workstation that is config'ed like this and the MS SQL Server Agent will not run. Gets the error that a response from the Agent was not received in time.Hi,
No , I have Sybase 12 and MSSQL server client running my machine and running
smoothly. Just check the availability of environment variables
\binn for SQL server
\bin for Sybase.
Otherwise using explorer go to the appropriate directories and execute the
client softwares.
Thanks
Hari
MCDBA
"dbush" <anonymous@.discussions.microsoft.com> wrote in message
news:793F0A29-CE6E-4625-A042-DBA33B104B40@.microsoft.com...
> Does anyone know if there is a conflict between MS SQL 7.0 and Sybase's
SQL client running on the same PC? Reason is I have a workstation that is
config'ed like this and the MS SQL Server Agent will not run. Gets the error
that a response from the Agent was not received in time.

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

Conflicts table

Is is safe to delete the records in the conflict_ table for a merge
replication?
I have a lot of conflicts logged there that I just don't care about (I would
like to not record the conflict to start, but i think that's not possible).
Can I just delete the records?
Thanks, Jos Araujo.
These are purged IIRC after the retention period. It is safe to delete them,
but I would not do it - just cause I hate to mess with replication innards.
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
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:uDDhNHTCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> Is is safe to delete the records in the conflict_ table for a merge
> replication?
> I have a lot of conflicts logged there that I just don't care about (I
would
> like to not record the conflict to start, but i think that's not
possible).
> Can I just delete the records?
> Thanks, Jos Araujo.
>
|||Great... thanks for the answer.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uS09OCWCFHA.1524@.TK2MSFTNGP09.phx.gbl...
> These are purged IIRC after the retention period. It is safe to delete
them,
> but I would not do it - just cause I hate to mess with replication
innards.
> --
> 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
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:uDDhNHTCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> would
> possible).
>

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

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:

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.

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

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.

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

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.

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

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.

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

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.

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,

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.

Thanks

Udaya.

|||

Hey,

I don't think there is a way to specify the order under sql server 2005.

Junhai Kan

conflicts in data don't appear in conflicts for subscription

I have become aware that some database entries were not "syncd" from a
disconnected server when it came online. My only guess is that a conflict
resulted when the syncd finally happened. The problem is it was 5 days ago,
and when I look at the conflicts under the subscription, the box is empty.
How can I see conflicts from 5 days ago?
WB
Use the conflict viewer to view them. By default they are stored for 14
days or whatever you have set to be conflict_retention value to be in
sp_addmergepublication.
http://www.zetainteractive.com - Shift Happens!
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
"WB" <none> wrote in message news:OjAYszpQIHA.4440@.TK2MSFTNGP06.phx.gbl...
>I have become aware that some database entries were not "syncd" from a
> disconnected server when it came online. My only guess is that a conflict
> resulted when the syncd finally happened. The problem is it was 5 days
> ago,
> and when I look at the conflicts under the subscription, the box is empty.
> How can I see conflicts from 5 days ago?
> WB
>
|||I am not sure what you mean when you say "use the conflict viewer..." I
open Enterprise Manager, then replication and right click on the
publication. A popup menu appears and one of the options is to view
conflicts. Is that what you mean? Is there some way to view conflicts? I
will look at the properties of the publication to see if I can locate the
days for which conflicts are kept.
WB
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23nZH2nrQIHA.2396@.TK2MSFTNGP02.phx.gbl...
> Use the conflict viewer to view them. By default they are stored for 14
> days or whatever you have set to be conflict_retention value to be in
> sp_addmergepublication.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "WB" <none> wrote in message news:OjAYszpQIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||That is exactly what I mean. Conflict retention is not exposed in the
publication properties dialog.
http://www.zetainteractive.com - Shift Happens!
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
"wb" <none> wrote in message news:ucqfHHyQIHA.2208@.TK2MSFTNGP06.phx.gbl...
>I am not sure what you mean when you say "use the conflict viewer..." I
>open Enterprise Manager, then replication and right click on the
>publication. A popup menu appears and one of the options is to view
>conflicts. Is that what you mean? Is there some way to view conflicts?
>I will look at the properties of the publication to see if I can locate the
>days for which conflicts are kept.
> WB
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23nZH2nrQIHA.2396@.TK2MSFTNGP02.phx.gbl...
>

Conflicts how can it be possible

Dear Friends
I am getting the conflict for the records with following
remark.
The row was updated at 'SERVER1.DATABASE1' but could not
be updated at 'SERVER2.DATABASE2'. Violation of PRIMARY
KEY constraint 'PK__TABLE04__5DF6A344'. Cannot insert
duplicate key in object 'TABLE04'.
This record is created in SERVER2 AND ALSO CHANGED IN
SERVER2. The replication was down and then it come up
with this error when the user have change on same record.
Can you please suggest what can be the reason.
Publisher have not change anything every change is done
by the supplier still it is in the conflict.
Your guidance will help me a lot.
Best regards
Sharad
Is it possible that you updated the primary key? This might cause this
problem.
You also might want to edit your update proc and comment out the section
where it updates the pk in the second portion of the update proc.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Sharad" <niitmalad@.yahoo.co.in> wrote in message
news:06f901c4ec1e$5ed33310$a501280a@.phx.gbl...
> Dear Friends
> I am getting the conflict for the records with following
> remark.
> The row was updated at 'SERVER1.DATABASE1' but could not
> be updated at 'SERVER2.DATABASE2'. Violation of PRIMARY
> KEY constraint 'PK__TABLE04__5DF6A344'. Cannot insert
> duplicate key in object 'TABLE04'.
> This record is created in SERVER2 AND ALSO CHANGED IN
> SERVER2. The replication was down and then it come up
> with this error when the user have change on same record.
> Can you please suggest what can be the reason.
> Publisher have not change anything every change is done
> by the supplier still it is in the conflict.
> Your guidance will help me a lot.
> Best regards
> Sharad

Conflicts from nowhere

I have two tables that are getting conflicts between the subscriber and the publisher, however I am pretty sure we only update these tables at the subscriber.

I have column level tracking turned on.

The tables both have Nonoverlapping, single subscription (3) set for partition options. I wonder does this do any maintenance to the tables?

I have put in place some triggers to audit what makes changes to the data, but won't know until tomorrow, so if there is something that anyone knows about that might help, please let me know.

Thanks

Hi,

Could you return the contents of MSmerge_conflicts_info and MSmerge_conflict_publication_article table for the article where the conflict occurred? Also, there are probably some information about conflict in merge agent history. Do you have some primary key, foreign key constraint on publisher only? That will cause conflict if the subscriber row violated the constraint and failed to be applied on publisher.

Hope it helps.

Wanwen

|||some more questions as well - what does the filtered column look like, and are you updating the filtered column?|||The filtered column is a bit field and is only updated at the server - with the partition options set to 3 (and use partition groups set to true) you cannot change that value at the subscriber. The field gets read only permissions in the database.|||The triggers I put in place to audit the updates to the table I am conerned with revealed that there were no updates occuring that were not expected.

So, what happens is the data gets inserted via a biztalk process. The data then gets synchronised to a mobile device where an update occurs.

Upon synchronisation, the data updates going back are met with a conflct - wwhich must surely be all but impossible if nothing has touched the data on the server.

To make matters worse, this doesn't occur for every updated record only for some of them. It also does not occur to other tables set up in the same way.

Something is very fishy about this..|||

Hi,

Could you please give more information so we can try to understand and repro the problem?

Do you have join filters defined in your publication? when you do update, do you update the rows on both base article and join article? what is your subset_filter and join filter looks like?

Thanks

Wanwen

|||OK, more information as requested (should have done this when you asked the first time).

Two articles in my database that I am getting these unexplained
conflicts for.
job_travel
meter

CREATE TABLE [dbo].[job_travel](
[jot_id] [uniqueidentifier] NOT NULL CONSTRAINT [df_jot_id] DEFAULT (newid()),
[jot_guid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [df_jot_guid] DEFAULT (newsequentialid()),
[jot_job_id] [uniqueidentifier] NOT NULL,
[jot_travel_start] [datetime] NULL,
[jot_travel_stop] [datetime] NULL,
[jot_travel_duration] [int] NULL,
[jot_is_active] [bit] NOT NULL CONSTRAINT [df_jot_is_active] DEFAULT ((1)),
CONSTRAINT [pk_jot_id] PRIMARY KEY CLUSTERED
(
[jot_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
)

CREATE TABLE [dbo].[meter](
[met_id] [uniqueidentifier] NOT NULL CONSTRAINT [df_met_id] DEFAULT (newid()),
[met_guid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [df_met_guid] DEFAULT (newsequentialid()),
[met_job_id] [uniqueidentifier] NOT NULL,
[met_reading] [int] NULL,
[met_number] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[met_is_active] [bit] NOT NULL CONSTRAINT [df_met_is_active] DEFAULT ((1)),
CONSTRAINT [pk_met_id] PRIMARY KEY CLUSTERED
(
[met_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF)
)

Each article hangs from the job table which in turn hangs from the sys_user table

joins to get there are as follows

-- Select the technician information
SELECT <published_columns> FROM [dbo].[sys_user] WHERE syu_code = SUSER_SNAME() AND syu_is_active = 1

-- select the jobs for the technician
SELECT <published_columns> FROM [dbo].[sys_user] INNER JOIN [dbo].[job] ON sys_user.syu_id = job.job_syu_id AND job.job_is_active = 1

-- select the job travel records for each job
SELECT <published_columns> FROM [dbo].[job] INNER JOIN [dbo].[job_travel] ON job.job_id = job_travel.jot_job_id AND job_travel.jot_is_active = 1

-- select the meter records for each job
SELECT <published_columns> FROM [dbo].[job] INNER JOIN [dbo].[meter] ON job.job_id = meter.met_job_id AND meter.met_is_active = 1

MSmerge conflict info for job_travel (3 useful columns - let me know if you need more)
conflict_type reason_code reason_text
2 2 The same column(s) of the same row was updated at both '25a - 1193b9285dee' and 'mas.canon_map'. The resolver chose the update from '25a - 1193b9285dee' as the winner.

A typical job_travel record from the conflict table

jot_id jot_guid jot_job_id jot_travel_start jot_travel_stop jot_travel_duration jot_is_active origin_datasource_id
5ED06CA2-C513-46EE-BA52-A7CD7327D195 A8243A8C-0316-DB11-807E-00145E2A163D 95BFBB97-694E-4526-8FBE-7D31CB35251C 2006-07-18 14:18:00.000 NULL NULL 1 1CEA745B-FAF4-4772-A5D1-3E644DD40874

MSmerge conflict info for meter (3 useful columns - let me know if you need more)

conflict_type reason_code reason_text
2 2 The same column(s) of the same row was updated at both '73w - e17b9ff3c3eb' and 'mas.canon_map'. The resolver chose the update from '73w - e17b9ff3c3eb' as the winner.

A typical meter record from the conflict table
met_id met_guid met_job_id met_reading met_number met_is_active origin_datasource_id
127380E3-E1AF-49BD-8B6C-FB5338287CDD F2D5A09B-0C15-DB11-807E-00145E2A163D 7594C31D-1805-46B8-B2A8-F1EDB46E8C06 NULL 1 1 1CEA745B-FAF4-4772-A5D1-3E644DD40874

<quote>
Also, there are probably some information about conflict in merge agent history
</quote>

Not sure what you're looking for here?

<quote>

Do you have some primary key, foreign key constraint on publisher only?

</quote>

No, constraints are consistent at both publisher and subscriber.

<quote>

you have join filters defined in your publication?
</quote>

Yes, as shown above

<quote>

when you do update,

do you update the rows on both base article and join article?
</quote>

potentially.

Sometimes we will and sometimes we won't. Information on a meter or a job_travel record may change without any change to the job data. A meter or job_travel change does not force a change to the job record.

To re-iterate to data flow.

A record gets create at the publisher.
Synchronisation ensures the subscriber has access to the record.
A data update occurs at the subscriber. (job_travel.jot_travel_stop) (meter.met_reading)
NO data updates occur at the publisher.
Synchronsation ensures the change goes from the subscriber to the publisher.
On some occasions - not all - a conflict occurs.
The custom resolver (subscriber always wins) kicks in and resolves the data - EVENTUALLY. There is a definite delay in this process causing the data extract that has been triggered to extract unresolved data (publisher data before the update)

Hopefully this is sufficient information for you to continue with. Ask for more and I'll see what I can provide.

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

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

Conflicts consistently occur on merge

Hi,

I was hoping a sharp eye may be able to pick up on what I am doing wrong here, I am i must be making a comon mistake. If necessary I am happy to create and post a sample if necessary.

Problem:

The publication parent table has a filter (defined below) and also has a join filter(defined below) to a child table. When changes are made to the parent and child table and synchronised a conflict is raised. For the child table for some reason the merge agent is saying an explicit update occured at the publisher but the values at the publisher have not changed at all and no sql has been executed to update the publisher rows. This in effect is stopping the changes to the child table being applied until i manually apply them in the conflict resolver. The strange thing is the conflict should not occur in the first place.

Perhaps my SQL server 2005 version? 9.00.2050.00 SP1 Standard Edition

Environment:

SQL Server 2005 - 9.00.2050.00 SP1 Standard Edition

SQL Server Mobile

Detail:

FKs are

FK: Job.JobScheduleID --> JobSchedule.ID

FK: JobDetail.JobID --> Job.ID

All three tables have int based IDs that are auto increment.

Publication Articles:

JobSchedule is download only

Job is Bidirectional, identity range management is MANUAL (only updates occur on this table)

JobDetail is Bidirectional, identity range management is MANUAL (only updates occur on this table)

Filters are of the following form:

Filter Job:

SELECT <published_columns> FROM [dbo].[Job] WHERE convert(nchar,[companyID])=Host_Name() AND [JobCompletedDate] IS NULL AND

( [JobScheduleID] in (SELECT distinct ID from JobSchedule where GETDATE() BETWEEN [JobSchedule].[start] AND [JobSchedule].[end]) )

Join Filter Job --> JobDetail

SELECT <published_columns> FROM [dbo].[Job] INNER JOIN [dbo].[JobDetail] ON [Job].[ID] = [JobDetail].[JobID]

Now the first thing to note with respect to using GetDate() i have read http://msdn2.microsoft.com/en-us/library/ms365153.aspx so i thought that i would remove that portion of the Filter on the Job table just to see what happens.

SELECT <published_columns> FROM [dbo].[Job] WHERE convert(nchar,[companyID])=Host_Name() AND [JobCompletedDate] IS NULL

This still did not resolve the issue. I then Remove the AND [JobCompletedDate] IS NULL and it then started working fine (cool) but of course no longer satisfys the rule i want to create (seriously uncool Sad ).

Any ideas out there ?

Much appreciated,

pdns

Could you post the message from the conflict resolver.

Also did you set your relationships to be not for replication?

Martin

|||Quick thought - merge replication will perform dummy updates on your children tables when an update on the parent table results in a partition movement, so if your sql is updating the partition columns on the parent table this could be the cause of the child updates.|||

Hi Martin,

The conflict resolve message are as follows:

16/08/2007 12:00:00 AM "submit loser" "JOBDB" "JobPub" "JobDetail" JobPub - 25891a8ed36f 38c09202-0d4b-dc11-ae51-00c09f31ae1d 2(Column update conflict) The same column(s) of the same row was updated at both 'sql52.JOBDB' and 'JobPub - 25891a8ed36f'. The resolver chose the update from 'sql52.JobDB' as the winner. set DATEFORMAT mdy UPDATE [dbo].[JobDetail] SET [dbo].[JobDetail].[bit_sitevisited] MSmerge_conflict_JobPub_JobDetail].[bit_sitevisited] FROM [dbo].[JobDetail],[MSmerge_conflict_JobPub_JobDetail] WHERE [dbo].[JobDetail].[rowguid]='38c09202-0d4b-dc11-ae51-00c09f31ae1d' AND [dbo].[JobDetail].[rowguid]=[MSmerge_conflict_JobPub_JobDetail].[rowguid]

The Only the ID columns on the Job and JobDetail are set to NOT For Replication, are you talking about the FK constraints being set to Not For Replication.

Thnx,

pdns

|||

Hi roamingsim,

The filter for the parent table, namely Job, uses [JobCompletedDate] IS NULL in the where clause. On the device the JobCompletedDate is updated when a job is completed. Is there any way i can determine if this is causing the child updates ? and if it is what would be the normal way of avoiding this?

Thnx,

pdns

|||I believe it almost certainly will be causing dummy updates as setting the JobCompletedDate to not null effectively excludes the parent record from the partition - replication then needs to perform dummy updates to the children records so that they to are re-evaluated and removed from the partition. I don't think you want to stop this, as I assume you want these children records removed from the subscriber, so the question is more how you handle the conflicts if this is indeed the cause. Sorry I'm less familiar with conflict resolution.

If you want to check that these dummy updates you can perform the following simple test (no doubt there's better ways to find the pending changes involving querying the msmerge tables, but this test should give you some idea):

- Sync the subscriber to make sure it's up-to-date. (sync this a couple of times and you should see in the replication monitor that there are no updates being sent to or from the subscriber.
- Update one record on the parent table, setting the JobCompletedDate to something other than null
- sync again and check the details in the replication monitor. You should see one record updated or deleted on the parent table and one or more records being updated or deleted on the children tables.

This is probably not the source of your problem, but it may answer your original question about where these updates are coming from.

Conflicts consistently occur on merge

Hi,

I was hoping a sharp eye may be able to pick up on what I am doing wrong here, I am i must be making a comon mistake. If necessary I am happy to create and post a sample if necessary.

Problem:

Perhaps my SQL server 2005 version? 9.00.2050.00 SP1 Standard Edition

Environment:

SQL Server 2005 - 9.00.2050.00 SP1 Standard Edition

SQL Server Mobile

Detail:

FKs are

FK: Job.JobScheduleID --> JobSchedule.ID

FK: JobDetail.JobID --> Job.ID

All three tables have int based IDs that are auto increment.

Publication Articles:

JobSchedule is download only

Job is Bidirectional, identity range management is MANUAL (only updates occur on this table)

JobDetail is Bidirectional, identity range management is MANUAL (only updates occur on this table)

Filters are of the following form:

Filter Job:

SELECT <published_columns> FROM [dbo].[Job] WHERE convert(nchar,[companyID])=Host_Name() AND [JobCompletedDate] IS NULL AND

( [JobScheduleID] in (SELECT distinct ID from JobSchedule where GETDATE() BETWEEN [JobSchedule].[start] AND [JobSchedule].[end]) )

Join Filter Job --> JobDetail

SELECT <published_columns> FROM [dbo].[Job] INNER JOIN [dbo].[JobDetail] ON [Job].[ID] = [JobDetail].[JobID]

SELECT <published_columns> FROM [dbo].[Job] WHERE convert(nchar,[companyID])=Host_Name() AND [JobCompletedDate] IS NULL

Any ideas out there ?

Much appreciated,

pdns

Could you post the message from the conflict resolver.

Also did you set your relationships to be not for replication?

Martin

Hi Martin,

The conflict resolve message are as follows:

The Only the ID columns on the Job and JobDetail are set to NOT For Replication, are you talking about the FK constraints being set to Not For Replication.

Thnx,

pdns

|||

Hi roamingsim,

Thnx,

pdns

This is probably not the source of your problem, but it may answer your original question about where these updates are coming from.

Conflicts and String or Binary Data would be truncated

I have an application using SQL Server 2000 and Delphi
for the front end. I have 2 SQL Server, on each side of a
WAN and 15 offline users with Laptops that sync using
Merge replication. This app has been running for about 6
months now. Lately, I am getting a few conflicts saying
that the row was inserted or updated at the Laptop, but
could not be updated or inserted at the Server - String
or Binary Data would be truncated. The Merge agent
reports no errors, saying that the session succeeded. I
have a Trigger that updates a Datestamp field, but I have
the "NOT FOR REPLICATION" line at the top. I do not get
the error using the app, only when the user replicates.
Out of 15 users, 3 are getting the message in the
conflicts screen.
Any ideas.
Thanks,
John,
2 possibilities I can think of:
are the schemas identical on publisher and subscribers? If you have done a
nosync subscription with different schemas, this could be the cause.
If the schemas are the same, then there is a known issue if you are not yet
on sp3:
http://support.microsoft.com/default...ticle%3D319258
HTH,
Paul Ibison
|||Well we are at service pack 3 (I had them go into query
analyzer)and the schemas seem to be identical. It only
seems to happen to 3 of our laptop users and no on e els
gets the errors

>--Original Message--
>John,
>2 possibilities I can think of:
>
>are the schemas identical on publisher and subscribers?
If you have done a
>nosync subscription with different schemas, this could
be the cause.
>If the schemas are the same, then there is a known issue
if you are not yet
>on sp3:
>http://support.microsoft.com/default.aspx?scid=%
2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D31 9258
>HTH,
>Paul Ibison
>
>.
>
|||OK - can you try using DATALENGTH on hte problematic row's text data to
compare to the publisher. Also, can you check that the collations are the
same between publisher and subscriber (at column level).
Regards,
Paul Ibison
|||I will look into this on Monday. I really appreciate
your input.
Thank You
>--Original Message--
>OK - can you try using DATALENGTH on hte problematic
row's text data to
>compare to the publisher. Also, can you check that the
collations are the
>same between publisher and subscriber (at column level).
>Regards,
>Paul Ibison
>
>.
>

Conflicts and "String and Binary Datawould be truncated"

Friday, 24 February 2012

Conflict Viewer usage for past conflicts

Hello,
When conflicts arise, we can use the conflict viewer. We have checked the
box that says to log them for future use. Help says they go to the system
tables.
I am trying to find conflict information from several replications ago, but
can't find them. Is there a certain system table they go to?
Thanks,
Steve
Steve,
the tables that maintain conflict information are:
conflict_<PublicationName>_<ArticleName>_usertable name and
MSmerge_delete_conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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.

Saturday, 25 February 2012

Conflicts?

Conflicts with Merge Replication

Conflicts table

Conflicts in Updateable Subscriptions

Conflicts in Updateable Subscriptions

Conflicts in Updateable Subscriptions

Conflicts in Merge Replication

Conflicts in Merge Replication

Conflicts in Merge Replication

conflicts in data don't appear in conflicts for subscription

Conflicts how can it be possible

Conflicts from nowhere

Conflicts during replication

Conflicts due to table order

Conflicts consistently occur on merge

Conflicts consistently occur on merge

Conflicts and String or Binary Data would be truncated

Conflicts and "String and Binary Datawould be truncated"

Friday, 24 February 2012

Conflict Viewer usage for past conflicts

Conflict Viewer hangs

SQL Configure

Blog Archive

About Me