Following a recent upgrade of a WSS 2.0 to WSS 3.0 (using database migration due to moving to new hardware) it was discovered that alerts set in WSS 2.0 were not firing in WSS 3.0. It was strange because if you looked in Site Settings - User Alerts (under Site Administration column) it will look like the alerts migrated successfully.
It turns out, if you are using a different URL for the sites in 3.0 than 2.0, the alerts will not be correct. The alerts are stored in a table in the content database and one of the columns contains the the Site Url - this site Url is the Url from the 2.0 installation.
I think it is best practice to delete the alerts (which you have to do one by one) and then readd them (which you can add multiple users at one time) ... BTW, if you know of a way to delete multiple alerts for a list, please share.
Microsoft provides a suggestion by using code and updating the SiteUrl column in the ImmedSubscriptions table. More here
I reviewed the existing alerts in the table and then talked our DBA into updating the SiteUrl column in the ImmedSubscriptions table from myold.url.path.com to mynew.url.path.com.
Time to test, after making the change in production :)
I added an item to the list - the alerts all worked successfully. I am NOT guaranteeing this will work in every situation, but I took my chances and it worked for me.