Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replication Error due to Trigger Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2012 9:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 9:40 PM
Points: 12, Visits: 171
Situation:
I have setup transactional replication between 2 computers.
In the publisher, there is a INSTEAD OF DELETE trigger to prevent deletion of any rows in a table. If an attempt to delete rows in that table occurs, this trigger will write the deleting rows to a local file C:\temp\yyyy.mm.dd.hh.mm.ss.tbl_deleted.csv, and then email this file to a list of recipients. This trigger only exists in the publisher.
Yesterday, while I'm working on the publisher DB, I accidentally executed a stored procedure which attempted to delete some rows in the table. The trigger fired, a file C:\2012.09.04.09.01.01.tbl_deleted.csv generated, and the email was sent. But then problem occurs.

Problem:
When I open replication monitor, I see a bunch of errors in "Distributor To Subscriber History". The error said:
Command attempted:
if @@trancount > 0 rollback tran
Error messages:
Attachment file C:\temp\2012.09.05.01.01.43.36.tbl_deleted.csv is invalid.
(Source: MSSQLServer. Error number: 22051)
Similar errors occur periodically, and the filename (C:\temp\yyyy.mm.dd.hh.mm.ss.tbl_deleted.csv) in the error messages reflect the actual time of the error.

So how should I fix this? I have tried to reinitialize the subscription with a new snapshot, but it does not help. After some time, the same errors occur again.
Post #1354302
Posted Thursday, September 6, 2012 1:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 9:40 PM
Points: 12, Visits: 171
Strange.
I added a RETURN at the top of all triggers to prevent them from running. Then I reinitialized the subscription, but the same error still happens.
I even tried to delete the publication and create again. The errors are still there (file with latest time not valid).
Any help?
Post #1355067
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse