locked
sql an error was raised during trigger execution sp_MSsetartprocs RRS feed

  • Question

  • when attempting to create a merge replication, the snapshot is failing to create. how do I get it to complete? sql 2008. I have deleted and recreated the replication but it did not help

    Error messages:
    Message: An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back. 
    Command Text: sp_MSsetartprocs
    Parameters: @publication = broadcasts2
    @article = schedule_lto_backup

    Stack:    at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
       at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
       at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
       at Microsoft.SqlServer.Replication.Snapshot.CreateArticleProceduresWorkItem.MakeArticleProceduresTransaction(SqlConnection connection)
       at Microsoft.SqlServer.Replication.RetryableSqlServerTransactionManager.ExecuteTransaction(Boolean bLeaveTransactionOpen)
       at Microsoft.SqlServer.Replication.Snapshot.CreateArticleProceduresWorkItem.CreateArticleProcedures(SqlConnection connection)
       at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
       at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 3616)
    Get help: http://help/3616
    Server TV-SQL1, Level 16, State 2, Procedure MSmerge_ins_sp_4C30C61EEECA40ED9403C5EC19B94537, Line 114
    An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back. (Source: MSSQLServer, Error number: 3616)
    Get help: http://help/3616


    ???



    • Edited by berli Monday, December 19, 2016 2:39 PM
    Tuesday, December 13, 2016 8:50 AM

Answers

  • this is not a good answer, but after trying everything I could think of, including dropping and recreating the databases, this seems to work consistently, though I do not understand why it works and it seem like a rather stupid solution.

    create a merge replication with 1 table and run the snapshot agent. then add about 20 articles at a time each time running the snapshot agent. eventually you will have all the tables in the merge replication and then you can add subscribers. 

    if at any point the snapshot still fail, remove the tables added and try again


    ???

    • Marked as answer by berli Monday, December 19, 2016 2:43 PM
    Monday, December 19, 2016 2:42 PM
  • several days of hunting and I found the problem. Redgate's DLM which we were testing, caused a deadlock whenever we tried to create the snapshot. the victim was the snapshot rather than the DLM . naturally no message about this effect was received.

    i did find it on a profiler trace.


    ???

    • Marked as answer by berli Tuesday, December 20, 2016 1:37 PM
    Tuesday, December 20, 2016 1:37 PM

All replies

  • Hi berli,

    We will meet the error message 'An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back' when we try to make any modifications after a transaction that needs to be rolled back. You can use XACT_STATE to check if the transaction can be committed, for more information, please refer to this article. Then you can find what has blocked this transaction.

    Besides, this issue can occur in the following scenarios:

     1. Which release of SQL Server 2008 did you use, have you upgraded it to the latest release? This issue can be caused by some problem of the previous release of SQL Server 2008.
     2. The problem of registry, please use Windows Registry Checker Tool to repair it.

    If you have any other questions, please let me know.

    Best Regards,
    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 14, 2016 6:41 AM
  • the error is occurring when I try and create a snapshot for merge replication. I cannot edit the system sp and start adding a "set xact" command. any transaction is automatically created by the sytem when trying to create the snapshot.

    i am using Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64)   Apr  3 2015 14:50:02   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1

    for the scanreg commands as described by the article all I get is unrecognized command.


    ???

    Wednesday, December 14, 2016 7:41 AM
  • this is not a good answer, but after trying everything I could think of, including dropping and recreating the databases, this seems to work consistently, though I do not understand why it works and it seem like a rather stupid solution.

    create a merge replication with 1 table and run the snapshot agent. then add about 20 articles at a time each time running the snapshot agent. eventually you will have all the tables in the merge replication and then you can add subscribers. 

    if at any point the snapshot still fail, remove the tables added and try again


    ???

    • Marked as answer by berli Monday, December 19, 2016 2:43 PM
    Monday, December 19, 2016 2:42 PM
  • several days of hunting and I found the problem. Redgate's DLM which we were testing, caused a deadlock whenever we tried to create the snapshot. the victim was the snapshot rather than the DLM . naturally no message about this effect was received.

    i did find it on a profiler trace.


    ???

    • Marked as answer by berli Tuesday, December 20, 2016 1:37 PM
    Tuesday, December 20, 2016 1:37 PM