Frankly I was stumped! I had been through all the procedures that mentioned the table, I had scrolled through jobs, and audit logs. I had even looked through code embedded in reports. But I just could not see how a Customers title was being changed from ‘Mrs’ to ‘Ms’ in a report.
My last resort, and one I wish I had thought of sooner, was to put a trigger on the table to stop the specific text appearing. Here is the code:-
CREATE TRIGGER [dbo].[SALES_BY_SITE_stop]
ON [dbo].[SALES_BY_SITE]
AFTER INSERT, UPDATE
AS
BEGIN
IF EXISTS (SELECT 1 FROM INSERTED
WHERE Customer = 'Ms SomeName')
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('"Ms SomeName" was blocked',16,1)
END
END
Now it has to be said – it is generally bad practice to break something to fix something.
Luckily, it turned out that within a few minutes an email popped-up to say a job had failed. So I immediately removed the trigger and re-ran the job.
I then spent the next few hours trawling through some procedures and views to a base table that contained … the incorrect data. Cake time! 🙂
Database Consultant 