Alerts don't work after URL change

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!!

Now,

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:

USE [DATABASE]
UPDATE ImmedSubscriptions
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:

USE [DATABASE]
UPDATE ImmedSubscriptions
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!!

Tom

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.

Published maandag 12 mei 2008 14:07 by Tom Vandaele

Comments

# re: Alerts don't work after URL change

maandag 12 mei 2008 18:20 by Bart Bultinck

impressive research... 2 thumbs up !

# WSS 3.0 & MOSS: Recopilatorio de enlaces interesantes (XVIII)!

vrijdag 20 juni 2008 0:07 by Blog del CIIN

Tal y como os adelantaba en el último post , aquí tenéis la enésima entrega...bueno más bien la 18ª sobre

# WSS 3.0 & MOSS: Recopilatorio de enlaces interesantes (XVIII)! « Pasi??n por la tecnolog??a…

Pingback from  WSS 3.0 & MOSS: Recopilatorio de enlaces interesantes (XVIII)! « Pasi??n por la tecnolog??a…

# More sharepoint alert weirdness! Alert links don’t work after url change. « ….

Pingback from  More sharepoint alert weirdness! Alert links don’t work after url change. «  ….

# SharePoint Task Notifications Not Working After URL Change « Tim Lefler's IT & Security Blog

Pingback from  SharePoint Task Notifications Not Working After URL Change « Tim Lefler's IT & Security Blog

Powered by Community Server (Commercial Edition), by Telligent Systems