November 8, 2010 at 2:28 am
Please forgive me for cross-posting this. I had originally posted this question 5 days ago in the SS2K5 Replication topic, but got not one single response. Since it's not realy about replication itself, but more about 'acting like' the merge agent, it think this topic might get me some more response.
The merge replication agent can do inserts, updates and deletes that violate foreign key checks and rules and perform the actions without firing triggers or assigning new identity values as long as these are defined with 'NOT FOR REPLICATION'. For my current project I am assigned the task to build a process that does similar things as the Merge Replication Agent does: it inserts, updates and deletes records in our SQL 2005 database from a stream of changes delivered to it. My problem is that the actions on this stream may arrive out-of-order. For example, an insert statement in the stream may reference a record that will only be created by a statement that is later in the stream. In fact, they are delivered out-of-order to my process because it is SQL server's Merge replication that delivers them out-of-order to my source process. So, the merge agent managed to get them into my source database in that order, I now need to get the same changes into my target database in the same order too.
On my quest to an answer I've found that any code that needs to know whether or not it is executed by the replication agent generally calls function sessionproperty() with the undocumented parameter 'replication_agent' to retrieve a bit value, where 1 means the code was executed by the Replication Agent and 0 is returned otherwise (contrary to Books Online which states that NULL is returned for all unlisted parameters).
select sessionproperty('replication_agent')
I've also found that the replication agent's connection always has context_info() set to 0x08 or 0x18. However, setting my own connection's context_info to either value does not make sessionproperty('replication_agent') return me the so wanted value of 1.
set context_info 0x08
select sessionproperty('replication_agent')
I am not even sure if getting sessionproperty('replication_agent') to return 1 does get me any closer towards being able to ignore all 'NOT FOR REPLICATION' code, but it is all I have to go after for now.
Can anyone please show me some code or give directions on how to perform the inserts, updates and deletes in the "replication agent"-mode?
July 6, 2017 at 9:55 am
For this to work, specify on the Connection dialog in SSMS, in the Additional Connection Parameters tab, a key Replication='true', then connect to your server and db. All foreign keys, constraints and triggers marked with 'Not for replication' will be skipped for every T-SQL you execute from the query window just opened.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply