Stored procedure execution not behaving

• Mark-190692

  Old Hand

  Points: 355

  More actions

  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.

• Robert Davis

  One Orange Chip

  Points: 28027

  More actions

  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]

• Mark-190692

  Old Hand

  Points: 355

  More actions

  June 14, 2016 at 2:59 pm

  #1885046

  Ahhh ok yes I am, thanks very much.