Distribution Agent Fails with error Msg 0, Level 20, State 0, Line 0
Any Cox | Microsoft Premier Field Engineer
We recently encounter the a problem where Replication Distribution Agent failed with error below while trying to replicate the “execution” of a stored procedure.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Executing the “execution” instead of the “results” of a stored procedure is a great way to improve Transactional Replication performance. Most problem occur when the stored procedure fails on the Publisher. These are easier to track down as SQL DBA/developers are normally doing some watching/logging the SP publisher execution as part of a maintenance or SQL Agent job.
However, you can see the error above if the Distribution Agent fails to replicate the SP and XACT_ABORT is set to OFF. Unfortunately text of the message is not very helpful as SQL Distribution Agent only displays error raised by SQL Server. From the SQL BOL on Replication and XACT_ABORT
When replicating stored procedure execution, the setting for the session executing the stored procedure should specify XACT_ABORT ON. If XACT_ABORT is set to OFF, and an error occurs during execution of the procedure at the Publisher, the same error will occur at the Subscriber, causing the Distribution Agent to fail. Specifying XACT_ABORT ON ensures that any errors encountered during execution at the Publisher cause the entire execution to be rolled back, avoiding the Distribution Agent failure. For more information about setting XACT_ABORT, see SET XACT_ABORT (Transact-SQL).
You can run Profiler Trace, set History VerboseLevel 2, or include –OUTPUT parameter to the Distribution agent to very if your Distribution Agent is failing while trying to execute a user-defined stored procedure. If it is, modify the SP to include XACT_ABORT ON, then run Profile Trace to understand root cause for the SP execution failure. You could also use –SkipErrors if you want the Distribution Agent to ignore the error and continue to replication transactions.
