replication triggers

Hi all:
Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
index 'uc1MSmerge_tombstone'.
The statement has been terminated.
that is the error i got when i try to delet a record from table which has
merge replication on.
i had a check that this error actually is from the trigger generated by
replication..
so any helps will be appreciated.
Thanks
Nick
"nick" schrieb:
> Hi all:
> Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
> index 'uc1MSmerge_tombstone'.
> The statement has been terminated.
> that is the error i got when i try to delet a record from table which has
> merge replication on.
> i had a check that this error actually is from the trigger generated by
> replication..
> so any helps will be appreciated.
> Thanks
> Nick
Run the merge agent and try again ...

replication tables with forign key

Hi all

i have one central site and 7 remote sites, there are one mssqlserver 2k in each sites.
i have to replicate 4 table in one DB(my DB have about 20 tables) in my 8 sites.
this 4 table have forignkey between themselves and other15 tables
i planed to transactional replication but i cant becuse forign keys occure erros
if needed i can send my DB digram to you.

can anyone help me? :confused:

Thanx
M.J.Daneshhttp://www.microsoft.com/technet/prodtechnol/sql/2000/books/c09ppcsq.mspx for reference on configuring Merge/Transactional/snapshot replication types.

Replication problem, update on table with varchar primary key

hi
i have a problem in the distribution of a replicated table.
The execution sequence is:
-i create a table (the primary key is varchar)
-i publish this table
-i insert a row in the table (no problem in the replication)
-i update the row (no the primary key)
-there is a problem in the replication
The problem is the varchar primary key... you know why?!?!
Tnx!
Can you post the schema of the problem table here? Also is this
transactional replication.
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
<paolofresujazz@.libero.it> wrote in message
news:1161350093.758198.225150@.f16g2000cwb.googlegr oups.com...
> hi
> i have a problem in the distribution of a replicated table.
> The execution sequence is:
> -i create a table (the primary key is varchar)
> -i publish this table
> -i insert a row in the table (no problem in the replication)
> -i update the row (no the primary key)
> -there is a problem in the replication
> The problem is the varchar primary key... you know why?!?!
> Tnx!
>
|||Hilary Cotter ha scritto:

> Can you post the schema of the problem table here? Also is this
> transactional replication.
Yes is transaction replication.
If i create a simple table with 2 field:
id: varchar(50) (primary key)
name: varchar(50)
and update the name field of a row...
Tnx Hilary

Replication problem, update on table with varchar primary key

hi
i have a problem in the distribution of a replicated table.
The execution sequence is:
-i create a table (the primary key is varchar)
-i publish this table
-i insert a row in the table (no problem in the replication)
-i update the row (no the primary key)
-there is a problem in the replication
The problem is the varchar primary key... you know why?!?!
Tnx!Can you post the schema of the problem table here? Also is this
transactional replication.
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
<paolofresujazz@.libero.it> wrote in message
news:1161350093.758198.225150@.f16g2000cwb.googlegroups.com...
> hi
> i have a problem in the distribution of a replicated table.
> The execution sequence is:
> -i create a table (the primary key is varchar)
> -i publish this table
> -i insert a row in the table (no problem in the replication)
> -i update the row (no the primary key)
> -there is a problem in the replication
> The problem is the varchar primary key... you know why?!?!
> Tnx!
>|||Hilary Cotter ha scritto:

> Can you post the schema of the problem table here? Also is this
> transactional replication.
Yes is transaction replication.
If i create a simple table with 2 field:
id: varchar(50) (primary key)
name: varchar(50)
and update the name field of a row...
Tnx Hilarysql

Replication problem, update on table with varchar primary key

hi
i have a problem in the distribution of a replicated table.
The execution sequence is:
-i create a table (the primary key is varchar)
-i publish this table
-i insert a row in the table (no problem in the replication)
-i update the row (no the primary key)
-there is a problem in the replication
The problem is the varchar primary key... you know why?!?!
Tnx!Can you post the schema of the problem table here? Also is this
transactional replication.
--
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
<paolofresujazz@.libero.it> wrote in message
news:1161350093.758198.225150@.f16g2000cwb.googlegroups.com...
> hi
> i have a problem in the distribution of a replicated table.
> The execution sequence is:
> -i create a table (the primary key is varchar)
> -i publish this table
> -i insert a row in the table (no problem in the replication)
> -i update the row (no the primary key)
> -there is a problem in the replication
> The problem is the varchar primary key... you know why?!?!
> Tnx!
>|||Hilary Cotter ha scritto:
> Can you post the schema of the problem table here? Also is this
> transactional replication.
Yes is transaction replication.
If i create a simple table with 2 field:
id: varchar(50) (primary key)
name: varchar(50)
and update the name field of a row...
Tnx Hilary

replication locking (sql2000 / transactional)

Currently, we have a table replicated from one database to another on the
same server.
If I update 127 records based on the primary key, the subscription process
locks all the records until all the 127 commands have been executed.
Seamingly regardless of how they were updated.
eg.
CREATE TABLE Orgs ( OrganisationID INT PRIMARY KEY CLUSTERED ,
TimeLastUpdated DATETIME NOT NULL )
DECLARE @.SelectedOrgs ( OrganisationID INT PRIMARY KEY CLUSTERED )
/* ... insert 127 organisations to update here ... */
-- Process Type 1
UPDATE Orgs SET TimeLastUpdated = GETDATE()
FROM Orgs INNER JOIN @.SelectedOrgs SO ON Orgs.OrganisationID =
SO.OrganisationID
-- Process Type 2
WHILE ( blah )
BEGIN
-- one at a time in asc order
SELECT @.OrgID = OrganisationID FROM @.SelectedOrgs WHERE ( blah )
UPDATE Orgs SET TimeLastUpdated = GETDATE() WHERE OrganisationID =
@.OrgID
END
Using the default isolation level and no explicit transactions the table in
the subscriber database was locked until the commands had all been executed.
What gives - both types lock the whole table on the subscriber.
Surely it's not meant to use the same locks as were applied when the data
was updated?
Is there any to stop it locking the whole thing while it processes the
commands?
Ideally we'd upgrade to 2k5 and use row versioning... but that wont happen
for a while.

I need to implement a similar thing on a much larger database which needs to
be up 24/7 and can't be locked for long periods of time, mean while on the
publisher, 5% of the data may be changed in one go - but if we can't get it
to use optimistic locking on the subscriber we can't use replication.
Rebecca,
an update statement always has an implicit transaction. if you use
lumigent's Logexplorer you can see how this works. Your update statement will
be logged to the transaction log as :
BEGIN TRAN
Update Orgs SET TimeLastUpdated = GETDATE() where OrganizationID = 1
Update Orgs SET TimeLastUpdated = GETDATE() where OrganizationID = 2
..
..
..
Update Orgs SET TimeLastUpdated = GETDATE() where OrganizationID = 127
COMMIT TRAN
This is read by the log reader agent and converted to stored procedure
calls, but also with the transaction (AFAIR same XACT in msrepl_commands) .
The distribution agent applies it in the same way.
The table itself shouldn't be locked (lock escalation shouldn't occur on
such a low # rows), and inserting a record at the same time as the
distribution agent runs should prove this to be true.
Anyway, to answer your question more directly, -MaxCmdsInTran should allow
you to break the ACID properties and commit separate parts of the update as
separate transactions.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)