sharepoint alerts: how to repair them after a web app move

this past weekend we had a sharepoint farm where we needed to detach the databases and reattach them to a different web application.

this can break a lot of things, dr watson has a good list of some of them.

i'm going to focus on alerts and how to fix them (yep they break)

first of all there is a nice chunk of sample code to help fix this but its incomplete:

https://support.microsoft.com/default.aspx?scid=kb;en-us;936759

here's the piece of code we care about:

try{ alert.Update(); //Change the Alert frequency back to the initial state. alert.AlertFrequency = afPrevious; alert.Update();}catch (Exception ex){ Console.WriteLine(" -> Error changing Alert. {0}", ex.Message);} // inner try

basically all they're doing is changing a value in the alert, updating it, and then putting the value back to what it was.

what this essentially does is update a hard coded value called siteurl in the alerts table.

this works, kinda

the alerts will be sent out but the contents of the alerts will be referencing old links

that's bad news.

so how do we fix it?

well lets start with the content database tables that deal with alerts:

immedsubscriptions	contains immediate alerts
schedsubscriptions	contains all other alerts (daily, weekly, etc)

ok so lets have a look there

select top 20 * from immedsubscriptionsselect top 20 * from schedsubscriptions

hmmm the column called properties seems to have some hard coded urls...

(click to enlarge)

pretty strange since there is another field in that table called siteurl

(fyi updating the siteurl in these tables is what the microsoft supplied sample code fixes)...

so why not just use that siteurl as the url in properties instead of hard coding it? 

no clue but they probably had a good reason.

anyways now we have to see whats in that bag... the property bag.

string oldurl = "https://myoldwebapp"; string newurl = "https://mynewwebapp"; SPPropertyBag spprop = alert.Properties;if (spprop["siteurl"] != null) //fix hard coded properties{ //Console.WriteLine("pre alert site url: " + spprop["siteurl"].ToString()); if(spprop["siteurl"].Contains(oldurl)) //found old link reference/ { spprop["siteurl"] = spprop["siteurl"].Replace(oldurl, newurl); //Console.WriteLine("post alert site url: " + spprop["siteurl"].ToString()); Console.WriteLine("Updated siteurl in properties"); countsiteurlprop++; spprop.Update(); }}if (spprop["mobileurl"] != null){ //Console.WriteLine("alert mobile url: " + spprop["mobileurl"].ToString()); if (spprop["mobileurl"].Contains(oldurl)) //found old link reference/ { spprop["mobileurl"] = spprop["mobileurl"].Replace(oldurl, newurl); Console.WriteLine("Updated mobileurl in properties"); countmobileurlprop++; spprop.Update(); }}

how did i know to look there? (i'm sleeping with the property bag lady)

just kidding, it took a bit of tinkering considering that the properties field is not directly writable from an alerts object:

SPAlert Properties

Properties Gets the properties of the alert.

https://msdn2.microsoft.com/en-us/library/microsoft.sharepoint.spalert_properties.aspx

if you want to set the alert, you must use the property bag.

this concludes how to fix alerts after a webapp move. i hope you like.

Comments

• Anonymous
  June 18, 2007
  PingBack from http://blogs.msdn.com/mikewat/archive/2007/06/18/moving-databases-between-web-apps.aspx

• Anonymous
  June 18, 2007
  Ping

• Anonymous
  March 10, 2009
  Migration from SharePoint 2003 to 2007

• Anonymous
  March 10, 2009
  Migration from SharePoint 2003 to 2007

• Anonymous
  October 18, 2011
  I've created a product that can migrate alerts automagically. It currently works, and you can find out more about it here. sharepointalert.blogspot.com

• Anonymous
  December 05, 2011
  UPDATE dbo.ImmedSubscriptions SET SiteUrl = 'http://new-url.com'; UPDATE dbo.SchedSubscriptions SET SiteUrl = 'http://new-url.com'; Unsupported, yes. Effective, yes.

• Anonymous
  February 15, 2012
  Jordan - What about the Properties column? I did this ( UPDATE [SchedSubscriptions] SET [Properties] =  REPLACE ([Properties] , 'http://OldUrl', 'http://NewUrl') WHERE [Properties] LIKE '%http://OldUrl%' ) but they do not seem to be working yet.