SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


recreating replication without reinitializing missing sp_MSsetupnosyncsubwithlsnatdist_helper


recreating replication without reinitializing missing sp_MSsetupnosyncsubwithlsnatdist_helper

Author
Message
Kevin Brown-392626
Kevin Brown-392626
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2547 Visits: 425
Hi,

We have a database replicated with some customization that makes re-initializing a prospect we would rather avoid. We have also reached a point where we need to move our distributor to a different server.

The GUI to set up replication has an option to not initialize, so we decided to try that option.

We are working in a test environment that had an up to date replication scenario matching production. We took the application offline and allowed replication to catch up completely before ensuring no connections remained to the publication database. dropped the subscribers and publishers and then rebuilt the publishers to the new distribution database. We then tried to create the subscription. Everything looked good until we finished the wizard at which point it spun for a short while before returning an error indicating that it couldn't find the procedure 'Dev_distribution.dbo.sp_MSsetupnosyncsubwithlsnatdist_helper'

After a couple hours of trying to figure out why the system couldn't access the stored procedure I thought I saw in the database (there is one without the _helper) I realized the procedure doesn't exist anywhere on the distributor. I went through all of the servers that were part of replication and managed to find a copy of the system proc in the publisher's master database but not on any of the other servers.

I'm guessing I could manually create the proc in the location the wizard is expecting it, but I'm thinking that I'm probably missing something more fundamental that would be easier then copying MS supplied procs around.

Anyone have any ideas what could be misconfigured, or is there a more basic bug that I haven't found any documentation on in my searching?

Thanks,
Kevin
Kevin Brown-392626
Kevin Brown-392626
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2547 Visits: 425
Hi,

I found the solution. It turns out that our publisher was running the most recent SP2 update for SQL 2008 R2 but the distributor and subscriber were only running 2008 R2 SP2. when I installed the cumulative update package the procedures were created in the master database and I was able to create the subscriptions.

Thanks, and hopefully this helps if someone else runs into the same issue.
anupinder.rai
anupinder.rai
SSC Eights!
SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)

Group: General Forum Members
Points: 811 Visits: 417
I faced same issue when Publisher was on SQL Server 2012 SP1 and distributor was on SQL server 2008 SP3 and is resolved by updating distributor to SQL Server 2012 SP1.
Kevin Brown-392626
Kevin Brown-392626
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2547 Visits: 425
From what I saw it looks like Microsoft updated all supported versions of SQL server at the same time, so the real secret was just getting to either a service pack that was released after the change was made or to a cumulative update that was created after the change was made. I believe I saw a couple occurrences where you could have stayed at a lower service pack and picked up the most recent cumulative update if necessary (as long as the service pack was still supported) or probably simply installed the correct hot fix.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search