General discussion

Locked

SQL Server 2005 Replication

By mg_roberts ·
Well, we got new database servers. 2 Dell 6850's. The main database server is running fine. But we have the second running transactional replication, as well as a 3rd server running transactional replication. both servers pull as subscribers. but we are getting tons of errors, and they seem to not be syncronizing in real time. the server is under very light load and it says its thousands of transactions behind.

the error we get is "The DELETE statement conflicted with the reference constraint"

this is the full error from the log:

Date 6/17/2006 7:10:46 PM
Log Job History (ALDB01-LMS_DB-LMS_DB_Pub-ALDB02-LMS_DB-6468C29A-C8DA-4014-948D-460B5D4F1442)

Step ID 1
Server ALDB02
Job Name ALDB01-LMS_DB-LMS_DB_Pub-ALDB02-LMS_DB-6468C29A-C8DA-4014-948D-460B5D4F1442
Step Name Run agent.
Duration 1.14:00:47
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
2006-06-19 13:11:32.906 Error executing a batch of commands. Retrying individual commands.
2006-06-19 13:11:33.171 Agent message code 547. The DELETE statement conflicted with the REFERENCE constraint "FK_Enrollment_Proficiencies_SH_Enrollments". The conflict occurred in database "LMS_DB", table "dbo.Enrollment_Proficiencies", column 'FK_SH_Enrollment_ID'.
2006-06-19 13:11:33.171 Category:COMMAND
Source: Failed Command
Number:
Message: if @@trancount > 0 rollback tran
2006-06-19 13:11:33.187 Category:NULL
Source: Microsoft SQL Native Client
Number: 547
Message: The DELETE statement conflicted with the REFERENCE constraint "FK_Enrollment_Proficiencies_SH_Enrollments". The conflict occurred in database "LMS_DB", table "dbo.Enrollment_Proficiencies", column 'FK_SH_Enrollment_ID'.
2006-06-19 13:11:33.187 Category:NULL
Source: Microsoft SQL Native Client
Number: 20598
Message: The row was not found at the Subscriber when applying the replicated command.

any help would be greatly appreciated.

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Fixed

by mg_roberts In reply to SQL Server 2005 Replicati ...

Well, it appears our enrollment_proficiencies table wasn't published at the distribution level due to a lack of a primary key column. as well as a couple other tables. there were also a bunch of commands and articles we had missed during the publicatoin process. apparently you primary keys are required to publish with a transactional replication.

Back to Software Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums