Post reply

Stored procedure execution not behaving

  • June 14, 2016 at 7:00 am

    #319471

    Hi,

    I've been trying to configure a simple publication to replicate stored procedure execution as per this post[/url] but no matter what I try every time I execute the SP on the Publisher db the updates get pushed row by row to the subscriber rather than calling the expected SP (ie I see a number of exec [sp_MSins_dboTestArticle] commands when profiling the subscriber database rather than a single call to the addheaprows SP).

    I've followed all the instructions on multiple websites including MSDN (setting the SP article properties, calling it in a Serializable transaction) and simply can't get this to work. The publication is set up as Transactional on SQL Server 2014 with a single Pull Subscriber (I've also tried push).

    Any help would be greatly appreciated, could there be something obvious I'm overlooking?

    Thanks.

  • June 14, 2016 at 9:01 am

    #1884925

    Are you using Change Data Capture? CDC forces it to do record level replication.

    https://blogs.msdn.microsoft.com/repltalk/2013/04/28/cannot-replicate-stored-procedure-execution-when-cdc-is-enabled/

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • June 14, 2016 at 2:59 pm

    #1885046

    Ahhh ok yes I am, thanks very much.

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

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