This issue is related to another error already fixed by Microsoft. A lot of people will not match this article to there own problem because they had not the same scenario. Microsoft describe an issue if you migrate from WSS 2.0 to WSS 3.0. The same problem is going on if you have only MOSS 2007 (WSS 3.0) and you change the url. For example you have a WSS 3.0 and transfer your site collection to another server and rename the site collection name. Even if you have correct „Alternate Access Mappings“ it won’t work correctly. This happens only to libraries which exists before you migrated. If you create a task and set the „Alert Me“ notification you will receive an correct EMail from MOSS 2007. Now you change the Task Owner („Assigned To“). You will not receive any information about. No task change and no alert notification. You will find no log entry or something else.
This happens because the ImmedSubscriptions table and the SchedSubscriptions table have wrong entries within the database. To fix your problem you can download following sourcecode . If you just want to view the source click here.
When you downloaded go on command line and type following command:
After compiling V3UpdateAlerts.exe can be run with the URL of the Site Collection „to fix“ the Alerts for.
What’s going on when you run this fix. The source code will „fix“ the SiteUrl column by changing the AlertFrequency. Internally this will cause a move between the ImmedSubscription and SchedSubscriptions table. During this move the SiteUrl column will be automaticall fixed.
Now you will ask why I post this here because there is already one fix on the Microsoft homepage. Go on and read again and you will see I describe a different scenario as Microsoft did. It is just the same issue.
This error is related to following issue: E-mail notifications for alerts are not sent when content in a migrated list or in a migrated document library changes after you perform a database migration to upgrade to Windows SharePoint Services 3.0 at the Microsoft support site KB936759