Search code examples
sql-server-2005merge-replicationresolver

Replication Custom resolver changes empty strings to NULLs

We have an C# application which posts to a database which is replicated to another database (using merge-replication) and has one custom resolver which is a stored procedure.

This was working fine under SQL Server 2000 , but when testing under SQL Server 2005 the custom resolver is attempting to change any empty varchar columns to be nulls (and failing cos this particular column does not allow nulls).

Note that these varchar fields are not the ones which cause the conflict as they are current empty on both databases and are not being changed and the stored procedure does not change them (all it is doing is attempting to set the value of another money column).

Has anyone come across this problem, or has example of a stored procedure which will leave empty strings as they are?

The actual stored procedure is fairly simply and and re-calculates the customer balance in the event of a conflict.

ALTER procedure [dbo].[ReCalculateCustomerBalance]
    @tableowner sysname,
    @tablename sysname,
    @rowguid varchar(36),
    @subscriber sysname,
    @subscriber_db sysname,
    @log_conflict INT OUTPUT,
    @conflict_message nvarchar(512) OUTPUT
AS
    set nocount on
DECLARE
    @CustomerID  bigint,
    @SysBalance money,
    @CurBalance money,
    @SQL_TEXT nvarchar(2000)

    Select @CustomerID = customer.id from customer where rowguid=  @rowguid

    Select @SysBalance = Sum(SystemTotal), @CurBalance = Sum(CurrencyTotal)  From CustomerTransaction Where CustomerTransaction.CustomerID = @CustomerID

    Update Customer Set SystemBalance = IsNull(@SysBalance, 0), CurrencyBalance = IsNull(@CurBalance, 0) Where id = @CustomerID

    Select * From Customer Where rowguid= @rowguid

    Select @log_conflict =0
    Select @conflict_message ='successful'
    Return(0)
Solution

  • You have a few options here, each are a bit of a workaround from what my research seems to show is an issue with SQL Server.

    1- Alter this statement: Select * From Customer Where rowguid= @rowguid to explicitly mention each of the columns, and use an "isNull" for the offending fields

    2- Alter the column in the table to add a default constraint for ''. What this will do, is if you attempt to insert a 'null', it will replace it with the empty string

    3- Add a 'before insert' trigger which will alter the data before the insert, to not contain a 'null' anymore

    PS: Are you positive that the replication system has that column marked as "required"? I think if it is not required, it will insert 'null' if no data exists.