How to configure replication triggers to SET NOCOUNT ON?

Hopefully simple question:

How to configure replication so that replication triggers issue a SET NOCOUNT ON?

Also: are there any negative consequences of adding a SET NOCOUNT ON to replication triggers?

I'm not a DBA so my experience with replication is pretty much non-existent. Hopefully, there's a simple configuration option that I can apply when setting up replication for a database. If not, is it going to be feasible for me to write a script that runs over the replication triggers/procs to add this SET statement (any pointers on inbuilt procs that would help here would be real useful)?

Thanks in advance
Christian

Are you talking about the system replication procedures added to tables by replication?

If so I would not advise altering those procedures. They do set nocount on at a certain stage and do use this function so they are probibly not going to work if you start messing with them

Martin

|||Thanks Martin for the reply.

Yes, I was refering to system replication *triggers* added to the tables.

I need to dig into this as an sql Update statement (issued via ADO) against a single row in a table with replication triggers is returning a rowcount greater than 1, which must be as a result of the trigger.

I need for these single row updates to return a row count of 1 as this is playing havoc with an Object-Relational Mapper that I'm using. This ORM is relying on a row count of 1 as a way of determining whether a single row is affected by an update, and is throwing an exception for tables with replication triggers.

Christian
|||

I dont think this is coming from the replication triggers. They do set no count on before they do any inserting to the replication tables.

Are sure you its not something else?

declare @.is_mergeagent bit, @.at_publisher bit, @.retcode int

set rowcount 0

set transaction isolation level read committed

exec @.retcode = sys.sp_MSisreplmergeagent @.is_mergeagent output, @.at_publisher output

if @.@.error <> 0 or @.retcode <> 0

begin

rollback tran

return

end

declare @.article_rows_updated int

-- Should use @.@.rowcount below but there is a bug because of which sometimes in the presence of

-- other triggers on the table, the @.@.rowcount cannot be relied on.

select @.article_rows_updated = count(*) from inserted

if @.article_rows_updated=0

return

declare @.contents_rows_updated int, @.updateerror int, @.rowguid uniqueidentifier

, @.bm varbinary(500), @.missingbm varbinary(500), @.lineage varbinary(311), @.cv varbinary(49), @.partchangebm varbinary(500), @.joinchangebm varbinary(500), @.logicalrelationchangebm varbinary(500)

, @.tablenick int, @.partchange int, @.joinchange int, @.logicalrelationchange int, @.oldmaxversion int

, @.partgen bigint, @.newgen bigint, @.child_newgen bigint, @.child_oldmaxversion int, @.child_metadatarows_updated int

, @.logical_record_parent_oldmaxversion int, @.logical_record_lineage varbinary(311), @.logical_record_parent_regular_lineage varbinary(311), @.logical_record_parent_gencur bigint, @.logical_record_parent_rowguid uniqueidentifier

, @.replnick binary(6), @.num_parent_rows int, @.parent_row_inserted bit

declare @.dt datetime

declare @.nickbin varbinary(8)

declare @.error int

set nocount on

set @.tablenick = 758116

select @.replnick = 0xaccaad5dd04a

select @.nickbin = @.replnick + 0xFF

select @.oldmaxversion = maxversion_at_cleanup from dbo.sysmergearticles where nickname = @.tablenick

select @.dt = getdate()

......

|||Right, I'm going to have to look more in to this. Maybe the different replication models produce different trigger code?

Thanks
Christian|||

That was a merge trigger. What replication are you using?

Martin

|||Martin,

I need to get some time to really identify what's happening here. Once I have investigated further, I will post back to this thread, and hopefully be a little more informed when making my replies.

Thanks
Christian