Replication Error due to Trigger

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

  • 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?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply