PRB: ANSI Warnings May Incorrectly Show Replication Agents As Failed (304535)

SYMPTOMS

The Replication Monitor reports that an agent has failed if the replication process encounters ANSI_WARNINGS. The error message that the agent reports is dependent upon the ANSI warning that occurs; therefore, the error message varies. If an error message occurs, you may find that data has actually synchronized successfully. This behavior may occur if:

An insert trigger is defined on a table that is part of a merge publication. When the trigger executes, the following warning message occurs:
8153 Warning: Null value is eliminated by an aggregate or other SET operation.
The Merge Agent fails while the data itself does merge and synchronize correctly.

WORKAROUND

To work around this behavior, use any of these methods:

Investigate and avoid a situation in which an ANSI warning might occur.
Set ANSI_WARNINGS to OFF on the trigger, or find a way to set ANSI_WARNINGS to OFF on the client that calls the trigger.

SQL Server 2000 and prior SQL Server versions do not have an option that allows you to set ANSI warnings OFF for the replication agents (that is, the Snapshot Agent, Distribution Agent and Merge Agent). Therefore, the only way other than to eliminate the cause of the warning is to suppress the warning. To suppress the warning, add a SET ANSI_WARNINGS OFF statement in the trigger definition. Note that suppressing the warning may cause unexpected results. Therefore, you need to thoroughly investigate any possible results before you set ANSI_WARNINGS to OFF.

MORE INFORMATION

If ANSI_WARNINGS is set to true, error or warning messages occur for conditions like a "divide by zero" error or if null values appear in aggregate functions. If ANSI_WARNINGS is set to false, no warnings occur. Conditions like "divide by zero" or null values that appear in aggregate functions will not return a warning. By default, ANSI_WARNINGS is set to false.

Session-level settings (set by using the SET statement) override the default database setting for ANSI warnings. By default, when ODBC and OLE DB clients connect to SQL Server, they issue a SET statement that sets ANSI warnings to true for the current session. See the "SET" topic in SQL Server Books Online for more information about the current session level handling of specific information.

Example Of The Problem and How to Workaround the Problem

To reproduce the problem, use these steps:

Use this code and create a table on a publisher:

CREATE TABLE [dbo].[test_warning] ( 	[int1] [int] NULL , )  
--insert null values into the table using  
insert into test_warning values (NULL)

Define the following trigger on the table you created in step 1. As you can see, the trigger does nothing but run an arithmetic function on a null value:
```
CREATE TRIGGER test_warning_insert ON dbo.test_warning
FOR INSERT AS
declare @i int
set @i = (select min(int1) from test_warning)
					
```
Create a merge publication on the table.
Create a subscription for the publication you created in step 3.
From the subscriber, run the following statement to insert values into the subscriber table:
```
insert into test_warning values (1, newid())
						
```
When the Merge Agent starts synchronization, it shows the following error message and detailed warning to indicate a failed merge process:
The process could not deliver insert(s) at the Subscriber
Warning: Null value is eliminated by an aggregate or other SET operation.

When the merge process runs, the newly inserted row on the subscriber is sent as an insert to the publisher. The insert on the published table causes the INSERT trigger to fire. One of the statements in the trigger performs a min(column) on a column that allows nulls. Because ANSI_WARNINGS are set ON, the min operation on a column that contains nulls raises a warning and cause the failed status of the agent.

To work around this specific scenario, set ANSI_WARNINGS to false as in the example that follows to prevent the warning when the trigger code executes: