Databases
StackOverflow2013
Postsdbo
POSystem Triggers for Replication and the NOCOUNT option
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSystem Triggers for Replication and the NOCOUNT option
    primarykey
    data
    text
    <p>When setting up replication in Sql Server 2008 the system creates triggers on every replicated table in order to facilitate the transfer of data to the subscribing databases.</p> <p>Would it be expected behaviour for system generated triggers to use <code>SET NOCOUNT ON</code> to prevent their actions affecting the value of the <code>@@ROWCOUNT</code> function?</p> <p><strong>Background</strong></p> <p>I am using NHibernate with a Sql Server 2008 backend database. The database has replication enabled which creates system generated triggers on the published tables to facilitate the data transfer to the subscribers.</p> <p>Without the replication everything works fine, but with the replication NHibernate’s rows affected verification checks fail. The number of rows that ADO.Net says it has affected appears to include the rows affected by the associated trigger in addition to the rows affected by the command itself.</p> <p>In the original question I mentioned the <code>@@ROWCOUNT</code> Sql Server function, but what I really mean is the “rows affected” value returned by the ADO.Net <code>ExecuteNonQuery</code> method. I suppose I was working on the assumption (possibly incorrectly) that that latter was fed by the former.</p> <p>As a temporary solution we have modified the replication triggers to add the <code>SET NOCOUNT ON</code> before any updates and to reverse this after any updates are complete. This solves our problem for the time being, but is not a viable permanent solution. According to advice we have received and common sense, editing the system triggers is not recommended.</p> <p>This does however suggest to me that we have identified the exact problem. The rows affected by the triggers are getting included in the final rows affected count for the current command. NHibernate expects only a known number of rows to be affected, it doesn’t provision for any unknown (as far as NHibernate is concerned) triggers adding to this count.</p> <p>We are currently in the process of investigating options that involve extending NHibernate to add capability to deal with or at least suppress this. Our resources include <a href="https://stackoverflow.com/questions/1354362/toomanyrowsaffectedexception-with-encrypted-triggers">this SO question</a>.</p> <p>I have also found <a href="http://www.sqldev.org/sql-server-replication/how-to-configure-replication-triggers-to-set-nocount-on-73762.shtml" rel="nofollow noreferrer">this post</a> that seems to suggest that system generated triggers would already set the NOCOUNT option to ON by default, negating the need to modify them. This is definitely not the case for us, so I was wondering why this might be. </p> <ol> <li>What is the default situation with regard to the system generated triggers?</li> <li>Is the behaviour configurable?</li> <li>Does it depend on the type of replication – merge, transactional?</li> </ol>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USAndy McCluggage
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSystem Triggers for Replication and the NOCOUNT option
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSystem Triggers for Replication and the NOCOUNT option
      1. USRafael Azevedo
      1. VTFavorite
    1. COAccording to Books Online, "The @@ROWCOUNT function is updated even when SET NOCOUNT is ON". So your question is unclear; are you having a specific problem?
      singulars
      1. POSystem Triggers for Replication and the NOCOUNT option
      1. USPondlife
    2. COYes I had a feeling it would be too brief. I have a tendency to waffle on in questions, but think I went the too far the other way this time. I'll elaborate...
      singulars
      1. POSystem Triggers for Replication and the NOCOUNT option
      1. USAndy McCluggage
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload