Subscriptions invalid after SP applied
Hello,
I finished applying SP 1 to sql2k5. Under the replication monitor all of the subscriptions now look like [].databasename when they were originally [subscriber1].databasename. Under Mgmt Studio I see the subscriptions correctly. When I try and replicate I get the message "The subscription to publication 'pub_databasename has expired or does not exist." I have tried to reinitialize the subscriptions but it doesn't work. Is there a way to solve this without have to drop the subscriptions and add them again as I do not want to lose the changes that were made on the subscriptions. We are using merge replication. Thanks.
John
[674 byte] By [
John123] at [2007-12-24]
Hi John, are you saying it worked fine in SQL 2005 RTM, or did you upgrade from SQL 2000?
We have seen this also. When we upgraded to SP1 and when we upgraded to the hotfix rollup. Some of the system tables still contain the subscriber name, so we have to use a combination of a query on those tables and replication monitor to be able to see the name of the subscriber.
Mike
Greg,
Sorry for the late response but I just got the alert from live stating there was a post :( We upgraded from RTM to SP1 and had the issue. We basically had to drop all subscriptions and add them in again. I am hoping we wont have this issue when SP2 comes out. We currently only have 10 subscriptions but we're looking to add about 40 more.
This is a serious issue, and not expected. Next time this happens please call CSS so they can open a support case for you and get this fixed. If the bug existed in SP1 replication system upgrade stored proc, it very well may exist in SP2 if it hasn't been changed at all.
Can you give me some details about your publications and subscriptions? Do you have publisher/distributor on one machine, or is this remote distributor? How many publications and what type? Can you script out your publication and post it here? Can you script out one of your subscriptions and post it here?
I have had this happen also. It was on a merge replication I had setup for testing. I created the publication on SQL 2005 Developer and when I upgraded to SP1 it now shows the subscriptions in the replication monitor as [.] and the status is marked error...
Can you tell me if you had SQL 2005 or SQL 2000 subscribers? If they were SQL 2000 subscribers, were they anonymous subscribers?
Greg,
We had both sql express and sql moble 2005 subscribers using iis as the proxy.
We also ran into this problem. We rebuilt the subscriptions from scratch to get things running again quickly. We then called PSS because we had 2 more networks to do. Their answer was "sorry, when you deleted the subscriptions, you deleted any data that would help us track it down."
When doing a second network, 3 of our subscriptions did the same thing. We then found that if you look in the distribution database in the msmerge_agents table (for merge subscriptions), those three subscriptions had "NULL" in the subscriber field. We manually updated it with the appropriate subscriber name and everything went back to working fine.
On our 3rd network, we checked that table before starting and everything looked fine and upgraded fine.
My suggestion is to take a backup of your distribution database and then restore it to a temporary database so you can compare your before and after.
Mike
Greg,
They are SQLEXPRESS subscribers on Windows XP SP2 and are not anonymous.
We did fix a bug in SQL 2005 SP2 similar to Mike's description above, where NULL was in the subscriber field. And as noted, after you back up the distribution database, you can try manually updating the columns in MSmerge_agents table to work around the issue. We found this with another customer who upgraded from 80 SP4 to SQL 2005 RTM several months ago, the manual update was a temporary workaround, but we fixed it for SP2.
We have also come across this issue applying both hotfix 2164 and 2175 on SQL 2005 to address a bug with replication and retention days. Is there any idea of when SP2 is to be released to the public?
SP2 should be out for consumption around 1 quarter of calendar year 2007
Thanks for posting this information it was helpful in fixing our merge replication after installing SP1, which changed the msmerge_agent table to Null like you said. There was no update in SP2 for this so be sure to have backups of the distribution DB ready if you upgrade to SP1 or SP2.
We encountered this after installing KB919611 values in column msmerge_agents.subscriber_name became null. Once we restored the values (took them from the subscribers), the replication returned to work (the good advice was given by Meir Dudai).
