SQL Server Performance

Replication Sp s Renamed

Discussion in 'SQL Server 2008 Replication' started by hrishikesh_nk, Feb 17, 2011.

  1. hrishikesh_nk New Member

    Hii I am new to this forum
    Im using SQL Server 2008 Enterprise Edtion.Recently I faced a problem on our production databases .All my transactional replication sp(stored proc ) for insert,update /delete ie sp_msins_dbotablename got renamed to sp_msins_tablename ie without dbo.I checked the Logs but couldnt figure out the problem.But for the time being ,as distribution agent was giving error (could not find the procedure sp_msins...) my replication was blocked so temporary to continue my replication i updated the Sysarticles table where i updated all insert and update sp to sp_msins_tablename/sp_msupd_tablename .I know this is not the correct solution. I would like to know causes for such a problem and corrective Actions.
  2. satya Moderator

    Welcome to the forums.
    The action you specified is an unsupported way and not recommended on a production instance.
    In order to clear and avoid any issues its better to remove replication adn start afresh.
  3. hrishikesh_nk New Member

    Dear Satya Sir,
    Thanks for the reply but the problem is that i have more than 100+ table s in replication in different publication.Setting replication via scripting may be easy but what can be the reason for the change of SP name from sp_msins_dbotablename to sp_msins_tablename.
  4. satya Moderator

    By all means you will have lesser problems if you can remove and reinstate replication than using unsupported stored procedures.
  5. pyale New Member

    The addition of "dbo" into the SP name was a "feature" if you used the SSMS GUI to create replication under SQL2005. Basically, this was a bug, because there was no reason for it, and if you set up replication manually using scripts you'd never deliberately name the stored procedures this way.

    I'm not sure how you lost the "dbo" portion of the name in your case, but is it possible that you restored the subscribing database into a 2008 server as part of a migration from 2005 and then scripted out the replicated stored procs using the 2008 SSMS GUI? My guess is that this would have scriped the stored procs WITHOUT the "dbo" in the name (i.e. it did it properly!), but the distribution database still held the old SP names in the sysarticles table. Something like that perhaps!

    Either way, I'd say your current solution was the correct one at first, since this is a production database and needed a practical and rapid fix, which you gave it. You should probably review all your existing subscriptions and publications though and look to see where there are instances where the expected replicated procedures contain "dbo" in the name. Try scripting these procedures out and see if that name is maintained accurately. If not, you'll probably need to drop/recreate your subscriptions where this looks like it could be a problem.

    Always use scripts to set up replication - the GUI often does things by default that you don't expect, or cannot control, and can lead to future problems that are difficult to handle.

Share This Page