SQL Server Performance

replication's stored procedures not created correctly in the susbcriber db

Discussion in 'SQL Server 2005 Replication' started by WingSzeto, Dec 4, 2007.

  1. WingSzeto Member

    We are using SQL 2k5 standard with sp2 and using transactional replication with push method. Publisher db and distributor db are on the same SQL server machine. Recently I encountered a puzzling problem. Here is what I did. I needed to create a clustered index on an existing published table wbich its primary key is not a clustered index. I did that by using the transact SQL in query analyzer and the script is at the end of the email. The script went fine but the three stored procedures (insert, update, delete) were mapping incorrectly in the publishing article properties and were also created incorrectly in the subscriber db.
    Here is what happened after the snap shot was created for this change and data are snychronized. In the article properties of the published db, the 'tblEmpl_ID_modules_handbook' article showed three stored procedures as follows:
    sp_MSins_dbotblEmpl_ID_modules
    sp_MSupd_dbotblEmpl_ID_modules
    sp_MSdel_dbotblEmpl_ID_modules
    Noticed that it cut off the last section of the table name '_handbook'. The problem is that we also have a published table name called tblEmpl_ID_modules which as expected has the same three stored procedure names. This is a big problem. Futhermore, when I go to the subscriber db and check the stored procedures for these two tables. I found the expected 6 sp's and each set of three has the right procedure names, sp_MSxxxx_dbotblEmpl_ID_modules and sp_MSxxxxdbotblEmpl_ID_modules_handbook'. However, the three sp's for the tblEmpl_ID_modules tables now has the SQL statements for tblEmpl_ID_modules_handbook' not tblEmpl_ID_modules. This means when an update occurs on the tblEmpl_ID_modules in the published db, this same update transaction will be replicated on the tblEmpl_ID_modules_handbook table instead of intended tblEmpl_ID_modules in the subscriber database. It is unreal. Since the two table structures are different, the replication process generated errors when it tried to execute the update statement. That's how I found this problem.
    The sp stored procedures I used to do the above are from SQL 2000. I don't know if this have something to do with this problem or not. Again please look at the below script and let me know. If I do the above publishing in SQL server Management Studio, I don't encounter this problem. If there are better sp_ stored procedures to do drop and add articles, please advice. Thank in advance for any help on this.Wingman
    exec
    sp_dropsubscription @publication = 'DBname_publisher',
    @article = 'tblEmpl_ID_modules_handbook',
    @subscriber = 'SubscriberDBname',
    @destination_db = 'DBname_subscriber'exec
    sp_droparticle @publication = 'DBname_publisher',
    @article = 'tblEmpl_ID_modules_handbook'BEGIN
    TRANSACTIONGOALTER TABLE dbo.tblEmpl_ID_modules_handbook
    DROP CONSTRAINT PK_tblEmpl_ID_modules_handbookGOALTER
    TABLE dbo.tblEmpl_ID_modules_handbook ADD CONSTRAINTPK_tblEmpl_ID_modules_handbook PRIMARY KEY CLUSTERED
    (EmployeeNum) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GOCOMMIT
    Exec
    sp_addarticle @publication = 'DBname_publisher',@article
    = 'tblEmpl_ID_modules_handbook',@source_table = 'tblEmpl_ID_modules_handbook' exec sp_addsubscription @publication='DBname_publisher', @article
    = 'all',@subscriber= 'SubscriberDBname', @destination_db
    = 'DBname_subscriber'
  2. satya Moderator

    The schema changes allowed on the publisher is creating a new index on a published table, will this be replicated or an index would need to be created on the Subscriber database - allowed but not replicated.

Share This Page