REAL REAL Experts needed for pretending to be replication_agent

Discussion in 'microsoft.public.sqlserver.replication' started by Nury SWORD, Sep 17, 2004.

  1. Nury SWORD

    Nury SWORD Guest

    Hi All,

    We know that, "NOT FOR REPLICATION" option makes it possible for
    replication agents ,let's say, to insert rows without being
    constrained by IDENTITY restriction or check constraints(And also it
    does not change SEED value in the table).

    That means, being a replication agent is like having a PASS CARD that
    allows them to get rid of constraint checks. (But disabling
    constraints is not a solution)

    But, during conflict resolution coding, I need to INSERT some data to
    some tables (related tables), but I am facing CONSTRAINTS that merge
    agent does not face.

    Is there any way of pretending to be like merge_agent for constraint
    overcoming? THIS IS THE CORE QUESTION.

    To be more specific, is there any way of setting sessionproperty for
    replication_agent? That is: when you execute the following query in a
    normal connection;

    select sessionproperty('replication_agent')

    you get "0".

    but for replication_agent that function returns "1". Microsoft guys
    somehow set connection property for agent, and how they do it is
    scritly undocumented.

    I tried setting context_info in sysprocesses table to "8" but it did
    not work. That SP also is not enough by itself
    :sp_MSsetcontext_replagent

    Please help, if any way of pretending to be merge agent.

    PS:I event wrote an application with ReplMerg.exe process name
    guessing that SQL may know agent from its process name.

    And also please don't suggest disabling constraints because it is not
    enough, e.g. replication_agent does not affect SEED value for
    inserts that it does..

    I hope some REAL expert will hear my SCREAM.
    Thanks alot in advance,
    Nury SWORD
    MCDBA - MCSD
    Toronto
     
    Nury SWORD, Sep 17, 2004
    #1
    1. Advertisements

  2. I suggest you contact PSS for an answer to your question.
     
    Hilary Cotter, Sep 17, 2004
    #2
    1. Advertisements

  3. Nury SWORD

    Nury Guest

    I finally found a merge replication guru guy. He is actually a
    contractor in Toronto and selling a special component which is totally
    able to pretend to be merge agent.

    It is simple to use but may be a little bit expensive for start-up
    companies. Since we desperately needed that feature the company just
    paid for it instead of digging for weeks over weeks.

    It gets SQL Server credentials as class properties and you call
    ExecuteSQL method, it executes it as if it is merge agent.

    For example I can execute the following SQL against my DB using that
    component:

    INSERT myTable (IdentityField, column1, column2) VALUES (5, 'test',
    test')

    and it works!!

    you do not need to say SET IDENTITY_INSERT ON/OFF or disable any
    constraints.

    If you need to contact that merge replication consultant just send me
    an email.

    Nury Sword

    MCSD - MCDBA
    Toronto
     
    Nury, Jan 27, 2005
    #3
  4. Nury SWORD

    Paul Ibison Guest

    Nury,

    as far as I understand, it is not possible to take the context of the merge
    agent. Are you sure that this is how the component works?

    Do you have to 'tell' the component what table you are working with? If so,
    it might just be doing some dynamic SQL:

    exec('set identity_insert tcompany on;insert into tcompany(id, companyname)
    values(34,''test'');set identity_insert tcompany off')

    Can you send me the details of this consultant as I'd like to ask him about
    it.

    Regards,
    Paul Ibison (SQL Server MVP)
     
    Paul Ibison, Jan 29, 2005
    #4
  5. Nury SWORD

    Raghu Guest

    Raghu, Oct 7, 2007
    #5
  6. copy this proc from another subscriber to the same publication. Note you
    really should do a reinitialization to completely fix this problem.

    --
    RelevantNoise.com - dedicated to mining blogs for business intelligence.

    Looking for a SQL Server replication book?
    http://www.nwsu.com/0974973602.html

    Looking for a FAQ on Indexing Services/SQL FTS
    http://www.indexserverfaq.com
     
    Hilary Cotter, Oct 8, 2007
    #6
  7. Nury SWORD

    Richard Rozema

    Joined:
    Jul 11, 2017
    Messages:
    1
    Likes Received:
    0
    I know this is a topic over a decade old, but if people are still looking for an answer: just add Replication=true to your connection parameters in your connection string and sessionproperty('replication_agent') will return 1, plus your session will ignore all constraints and triggers marked 'not for replication' and have all the other effects, just like the replication agents do. From SSMS you can get this effect from the connection dialog: Click the 'Options>>>' button and in the tab 'Additional Connection Parameters' add the text Replication=true, then log in as you do normally.
     
    Richard Rozema, Jul 11, 2017
    #7
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.