Post reply

Replication failure with repeated synchronisation then unable to find stored procedure

  • June 19, 2014 at 4:59 am

    #287842

    I have set up replication between SQL Server 2008R2 (publisher), 2012 (distributor) and 2008 (subscriber).

    This is a publication with a single article.

    The sync_object is a view, the source_object is a table, the destination table is a copy of the sync_object, and the replication stored procedures are created manually.

    The source table has a field in it which is split into 2 for some of the rows in the destination table.

    The snapshot is created as expected, the subscription then starts to subscribe. It completes and says it has delivered the snapshot, with the correct number of rows, it immediately restarts the bulk copy again.

    This happens a number of times until the underlying distribution job fails with an error stating that it can't find a stored procedure 'myprocnameins_msrepl_ccs', I can't find out where the extra ins_msrepl_ccs is being added.

    sp_helparticle shows all commands as I originally specified.

    I have tried running sp_changearticle to change the insert command to be the one I originally specified without success.

    I have added in the stored procedure that was missing and this worked but replication then failed with insert errors due to duplicated rows.

    I have two of these publications using almost identical code the other one is working, the only difference is that the publication puts a filter on the table and the subscriber is on a different server.

    Any comments, suggestions or ideas would be appreciated.

  • June 19, 2014 at 8:12 am

    #1723271

    Hi, if I remember correctly the _ccs procs are created to apply any data changes made to the source object during the snapshot creation (i.e. it's called after the snapshot completes), I wonder if it's not being created as replication procs are set to be created manually.

    I'm afraid I can't remember the structure of these procs, although I think they insert or update based on target row existence, which might be where the issue of duplicate rows comes from (you're inserted instead of updating).

    Hope that helps a bit 🙂

    Gaz

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply