People will probably have noticed, that when you perform a name change of your URL to connect to your MOSS 2007, your alerts just stop working. At least the Alert you created before the name change. All new alerts work just as perfect as they should do.
So, where's the problem with this? Very simple.
MOSS 2007, unfortunately used hard links in his database when it comes to Alerts. And from the moment when there is no more Alternate Access Mapping anymore for a certain URL the created Alerts don't function anymore.
How can you fix it? Well, it kinda would be a dumb blogpost it I couldn't provide an answer to this.
The point is that you'll be obliged to go into your SQL Server databases yourself, so before you do, always be sure you have a good database backup. I'll say it again: BACKUP YOUR DATABASE BEFORE PROCEDING!!
All the Alerts for a certain content database are located in the table "ImmedSubscriptions". The column that contains the URL is "SiteURL", so we have to change this...
Run this T-SQL Statement on your Content Databases:
SET SiteURL = 'newURL'
where SiteURL = 'oldURL'
This gets your Alerts working again...
One more thing to do now. When you get an alert, a link to the appropiate location is included, these are also hard links, this time in the column "Properties".
To update this:
SET Properties = Replace(Cast(Properties AS NVARCHAR(Max)), 'oldURL', 'newURL')
The cast "as nvarchar" is necessary, since ntext cannot be modified through the standard Replace function of SQL Server.
Of course, change the 'oldURL' and 'newURL' variables in the above commands...
Your alerts are back!!
EDIT: I had this post waiting to be posted for a long time now, and Peter Depoorter pointed out to me by mail today, that Microsoft has beat me by 2 weeks. Stsadm has a new command since the release of the Microsoft SharePoint Administration Toolkit.