July 29, 2009 at 2:34 pm
Hi there.
I'm using SQL Server 2005 transactional replication. I've created a simple replication UAT topology in which I publish AdventureWorks from DEV01, distribute to DEV02 and have DEV03 as the subscriber (push subscription).
I've configured Database Mail on the distributor and confirmed it works.
I've enabled the 'Subscriber has failed data validation' alert and the 'Subscriber has passed data validation' alert.
I have tinkered with a table on the subscriber such that the validation on that subscriber fails. When this table is in the publication and I perform a validation, the alert fires and I get an e-mail (and the failure is logged in the error log).
Here's the problem. If I remove this article from the publication and redo the validation, it does not fail (as I expected) but I do not get any e-mail nor do I see the validation pass logged in the error log.
I can't find any explanation for this. And yes, the alert is enabled on the distributor.
Any ideas anybody?
July 30, 2009 at 10:52 am
I figured out the problem.
This alert, number 20575, is not configured by default to be event-logged. I eventually found this out by running the following SQL ...
SELECT * FROM sys.messages WHERE message_id = 20575
... and saw that is_event_logged was set to 0.
I was going to use sp_altermessage to change this but found it wasn't permitted for system alerts. I found a blog somewhere that indicated this was corrected in SQL Server 2005 sp3 (and in SQL Server 2008). I was using SQL Server 2005 sp2.
I installed the service pack and ran the following SQL ...
EXEC sp_altermessage
@message_id = 20575
,@parameter = 'WITH_LOG'
,@parameter_value = 'true'
After that, the event was indeed logged and I got the e-mail as expected.
July 7, 2010 at 6:00 pm
Thanks for that - you just solved my problem 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply