SQL Server Performance

Manually synchronize Replication subscriptions

Discussion in 'Performance Tuning for SQL Server Replication' started by magictech, Feb 11, 2005.

  1. magictech New Member

    I've been trying to manually synchronize Replication subscriptions using Backups and restore for a while now and I can't seems to get it right. I keep getting error messages when I run the output of the sp_scriptpublicationcustomprocs stored procedure on the subscriber database. Is there anyone out there who has successfully implement replication using this method? I really need help.

    Basically, I started practicing on the Northwind database using the procedure outline in one of Microsoft#%92s Q article. But each time I does it, I gets several error messages running the output of the sp_scriptpublicationcustomprocs stored procedure. I've also tried with several other databases and still get the same types of error messages. Is this a known problem or are there people out there implementing replication using this procedure.

    Any input would be greatly appreciated.


    Regards


  2. satya Moderator

    What kind of errors you're getting when executing the SP?

    Have you followed this KBAhttp://support.microsoft.com/kb/320499/EN-US/ for manual sync.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. magictech New Member

    Thanks for responding to my posting. Yes, this is the same KBA that I follow while testing this procedure. Below is the error message that I'm getting when using Northwind database. I've tried this method on several other databases and still gets the same type of error messages. Any help will be greatly appreciated.


    Server: Msg 137, Level 15, State 2, Procedure sp_MSins_Employees, Line 11
    Must declare the variable '@c15'.
    Server: Msg 137, Level 15, State 2, Procedure sp_MSupd_Employees, Line 25
    Must declare the variable '@c18'.
    Server: Msg 156, Level 15, State 1, Procedure sp_MSupd_Employees, Line 30
    Incorrect syntax near the keyword 'end'.
    Server: Msg 137, Level 15, State 1, Procedure sp_MSupd_Employees, Line 50
    Must declare the variable '@c18'.
    Server: Msg 156, Level 15, State 1, Procedure sp_MSupd_Employees, Line 55
    Incorrect syntax near the keyword 'end'.


    Regards
  4. satya Moderator

    Have you checked those specified variables were declared and defined with certain values?
    I believe when it is executed by SQL Server those will be re-defined from other SPs and results.

    And check this KBAhttp://support.microsoft.com/kb/299903/EN-US/ for relevant information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. rajeev_id New Member

    HOW TO: Manually Synchronize Replication Subscriptions by Using Backup or Restore
    http://support.microsoft.com/kb/320499/EN-US/)

    I have never tried Manually Synchronize i have gone through above link.
    I have one doubt if we are restoring the database at the subscriber then why do we need to Run sp_scriptpublicationcustomprocs sp which generates text for the stored procedures that are required at the subscriber because previously we have restored the backup.


    Rajeev Kumar Srivastava
    --ALWAYS BE POSITIVE!--
  6. magictech New Member

    Has anyone else test this method of synchronizing subscription using Backup and restore? I'm still getting the same error message. What I'm having problem understanding is why I keep getting error message using a stored procedure that#%92s written by Microsoft (sp_scriptpublicationcustomprocs). I#%92m pretty sure I#%92m following the other entire step in the KBA correctly. Can anyone else try to follow the procedure in KBAhttp://support.microsoft.com/kb/299903/EN-US/ and see if they get the same error message? Maybe I'm doing something wrong.

    Regards

Share This Page