replication conflicts for 2 tables

Hi,
we are using merge replication between remote sites and
our central office. couple months ago we had a situation
where we had to insert some records and update other
records with these inserted records and then delete the
inserted records. Then we reinserted these records again
without replicating the deletes to our remote sites.
by doing this we had lots of conflicts.
I mannually deleted replicated and reinserted the records
again.
all other sites are fine but 2 of our sites, which were
disconnected during the delete replication did not get the
deleted records and they still shows conflicts every day
when they replicate. even though these records are not
being edited at all. For some reason they get marked for
replication and shows up in conflicts.
we don't care if those changes doesn't get replicated to
the remote sites becos they don't need those changes. how
do I stop them to show up in conflicts.
thanks
Kiran
did you resolve these conflicts using the conflict viewer? This is the
preferred way of solving this problem?
"Kiran" <anonymous@.discussions.microsoft.com> wrote in message
news:16bc201c41752$0defc130$a101280a@.phx.gbl...
> Hi,
> we are using merge replication between remote sites and
> our central office. couple months ago we had a situation
> where we had to insert some records and update other
> records with these inserted records and then delete the
> inserted records. Then we reinserted these records again
> without replicating the deletes to our remote sites.
> by doing this we had lots of conflicts.
> I mannually deleted replicated and reinserted the records
> again.
> all other sites are fine but 2 of our sites, which were
> disconnected during the delete replication did not get the
> deleted records and they still shows conflicts every day
> when they replicate. even though these records are not
> being edited at all. For some reason they get marked for
> replication and shows up in conflicts.
> we don't care if those changes doesn't get replicated to
> the remote sites becos they don't need those changes. how
> do I stop them to show up in conflicts.
> thanks
> Kiran
sql

replication autogenerated procs - interesting ;2 notation

I was looking to modify how the INSERT happens with regards to replication only to find my solution in the proc itself. When I edit the proc this is what I am displayed in SQL QA or EM:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER procedure "sp_MSins_dboMEETING" @.c1 int,@.c2 int,@.c3 int,@.c4 varchar(250),@.c5 datetime,@.c6 datetime,@.c7 bit,@.c8 char(1),@.c9 datetime,@.c10 bit,@.c11 bit,@.c12 bit,@.c13 datetime,@.c14 smallint,@.c15 datetime,@.c16 smallint,@.c17 binary(8),@.c18 bit,@.c19 bit,@.c20 bit,@.c21 varchar(1000)

AS
BEGIN

insert into "dbo"."MEETING"(
"MEETING_ID", "MEETING_TYPE_ID", "MEETING_STATUS_ID", "TITLE", "START_DATE", "END_DATE", "PUBLISH_IND", "GROUP_IND", "PUBLISH_DATE", "MY_ADVISORS", "SUBMITTED_IND", "ACTIVE_IND", "CREATE_DATE", "CREATED_BY", "LAST_UPDATE_DATE", "LAST_UPDATED_BY", "DATE_INDEXED", "ON_DEMAND_IND", "NOT_REPORTED_IND", "MAJOR_PROJECT_IND", "MAJOR_PROJECT_COMMENT"
)

values (
@.c1, @.c2, @.c3, @.c4, @.c5, @.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, @.c13, @.c14, @.c15, @.c16, @.c17, @.c18, @.c19, @.c20, @.c21
)

END

GO

create procedure "sp_MSins_dboMEETING";2 @.c1 int,@.c2 int,@.c3 int,@.c4 varchar(250),@.c5 datetime,@.c6 datetime,@.c7 bit,@.c8 char(1),@.c9 datetime,@.c10 bit,@.c11 bit,@.c12 bit,@.c13 datetime,@.c14 smallint,@.c15 datetime,@.c16 smallint,@.c17 binary(8),@.c18 bit,@.c19 bit,@.c20 bit,@.c21 varchar(1000)
as
if exists ( select * from "dbo"."MEETING"
where "MEETING_ID" = @.c1
)
begin
update "dbo"."MEETING" set "MEETING_TYPE_ID" = @.c2,"MEETING_STATUS_ID" = @.c3,"TITLE" = @.c4,"START_DATE" = @.c5,"END_DATE" = @.c6,"PUBLISH_IND" = @.c7,"GROUP_IND" = @.c8,"PUBLISH_DATE" = @.c9,"MY_ADVISORS" = @.c10,"SUBMITTED_IND" = @.c11,"ACTIVE_IND" = @.c12,"CREATE_DATE" = @.c13,"CREATED_BY" = @.c14,"LAST_UPDATE_DATE" = @.c15,"LAST_UPDATED_BY" = @.c16,"DATE_INDEXED" = @.c17,"ON_DEMAND_IND" = @.c18,"NOT_REPORTED_IND" = @.c19,"MAJOR_PROJECT_IND" = @.c20,"MAJOR_PROJECT_COMMENT" = @.c21
where "MEETING_ID" = @.c1
end
else
begin
insert into "dbo"."MEETING" ( "MEETING_ID","MEETING_TYPE_ID","MEETING_STATUS_ID","TITLE","START_DATE","END_DATE","PUBLISH_IND","GROUP_IND","PUBLISH_DATE","MY_ADVISORS","SUBMITTED_IND","ACTIVE_IND","CREATE_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","DATE_INDEXED","ON_DEMAND_IND","NOT_REPORTED_IND","MAJOR_PROJECT_IND","MAJOR_PROJECT_COMMENT" ) values ( @.c1,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8,@.c9,@.c10,@.c11,@.c12 ,@.c13,@.c14,@.c15,@.c16,@.c17,@.c18,@.c19,@.c20,@.c21 )
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Is the second version listed at the bottom like a comment or can it actually get called? I am going to script out all of these procs and save them, and then remove the first version (listed at the top) and the use the second version since it does what I need. I just thought it was interesting to see two stored procedures in a single definition, never seen the "PROC_NAME";2 notation, have you? If so please tell me what it does, is it just a way to create a second version of the procedure in a comment type fashion or is it used another way?From Books online's Create Procedure reference;number
Is an optional integer used to group procedures of the same name so they can be
dropped together with a single DROP PROCEDURE statement. For example, the
procedures used with an application called orders may be named orderproc;1,
orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the
entire group. If the name contains delimited identifiers, the number should
not be included as part of the identifier; use the appropriate delimiter around
procedure_name only.
I'll admit that I have neither used this functionality, nor ever seen it used. It may be that the application specifies which version of the procedure is executed by including the number in the call. And in that case, I wouldn't go dropping or commenting out the code, since it could break your app.

Replication and User Defined Trigger

Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
ThanksCheck for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegroups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>

Replication and User Defined Trigger

Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
Thanks
Check for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegr oups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>

Replication and User Defined Trigger

Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
ThanksCheck for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegroups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>

Replication and triggers

Does anyone know if when applying the initial snapshot for merge replication if it fires the insert or update user written triggers?Does anyone know Yep

if it fires the insert or update user written triggers? No it does'nt|||Since I could not get an answer I went ahead and applied the snapshot. When it had completed there were no records that my triggers would have generated so I guess the answer is NO IT DOES NOT FIRE THE TRIGGERS.

replication and identity problem

Every time I start replication i get following error:
Cannot insert explicit value for identity column in table 'xxxx' when
IDENTITY_INSERT is seto to OFF
the publisher side:
xxxx table has identity set on one column
the subscriber side
xxxx table has identity set on one column.
How can I change or how can I set IDENTITY_INSERT to ON during replication
process?
regards,
DaliborNot sure if this is the right way but this is what I did:
Go to the insert stored proc for replication on the
Subscriber DB.(sp_msins_TableName)
Comment out thie insert line for that column.
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>|||Use NOT FOR REPLICAITON on the identity.
e.g.
CREATE TABLE dbo.test (
id int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
office_location varchar (10) NOT NULL ,
...
)
Linchi
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>

Replication

Dudes,
I have this customer who is interested to replicate a SQL Server 2005
database to a MySQL database, online... For example, you insert a record to
the SQL Server and it automatically inserts it on the MySQL database. Both
has the same schema. They are on a LAN or VPN.
I just need an idea on how you would solve this...
Cheers!
Officially this is not supported on SQL 2005. You can try to add an ole-db
provider to MySQL and create it as a subscriber that way.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Christian Strevel (Intellekt)" <cs@.prodigy.net.mx> wrote in message
news:u9UrgXw7GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Dudes,
> I have this customer who is interested to replicate a SQL Server 2005
> database to a MySQL database, online... For example, you insert a record
> to the SQL Server and it automatically inserts it on the MySQL database.
> Both has the same schema. They are on a LAN or VPN.
> I just need an idea on how you would solve this...
> Cheers!
>

Replication

Hi everybody.
I am working on merge replication first time and I ran a
synchronizing processing and get error message:
Cannot insert the value NULL into column 'objid',
table 'ReplicationDB_1.dbo.sysmergeschemaarticles';
column does not allow nulls. INSERT fails.
My snapshots has created successfully.
Any suggestions or advices?
Thank you Mike.
I believe this is a bug. Contact PSS for a hot fix.
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:8bbf01c432aa$26749460$a001280a@.phx.gbl...
> Hi everybody.
> I am working on merge replication first time and I ran a
> synchronizing processing and get error message:
> Cannot insert the value NULL into column 'objid',
> table 'ReplicationDB_1.dbo.sysmergeschemaarticles';
> column does not allow nulls. INSERT fails.
> My snapshots has created successfully.
> Any suggestions or advices?
> Thank you Mike.
>
>

Replicating Matched MS SQL HR Data to Active Directory's Metadirectory fields

I've managed to do the add linkedserver stuff and pull up of some active
directory information.
I even matched up some data but could never insert anything into the AD Meta
fields.
I used this article,
http://msluder.dk/Resources/ADSI%20S...%20HTML/dq.htm ,which never
promised insert capabilities.
But if this is all it can do is give the ability to run some obscure reports
then it's hardly useful.
What I would like to do is create a view that would dynamiclly update the AD
Meta data from the Human Resources Database using the CN and / or
Primary-Email Address as the linking field.
That way when people change office numbers or as new employees start that
Meta information would be updated once it was entered or altered by HR.
It seems this would be possible via some kind of replication or event based
update through the SQL Server.
Any one have some ideas on this?
-Ethan
Ethan,
have a look at the Microsoft Script Repository
(http://www.microsoft.com/downloads/d...n&FamilyID=B4C
B2678-DAFB-4E30-B2DA-B8814FE2DA5A) there are scripts there to update AD.
HTH,
Paul Ibison
|||Have a look at the Replication Distributor Interface Reference in BOL.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html