0

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)
sgmoore
  • 15,694
  • 5
  • 43
  • 67
  • Can you post the problem portion of your stored procedure? This doesn't sound familiar, though there might have been an issue with either 2000 or 2005 that would be highlighted if we saw your stored proc. – Erich Oct 05 '09 at 15:25
  • I've included the whole of the stored procedure as it is fairly short. However, the same error occurs if I remove the first two selects and replace the update with something like - Update Customer Set SystemBalance=0 where rowguid= @rowguid – sgmoore Oct 05 '09 at 15:50
  • What exactly is the error thrown? IS it a SQL error, or in C# somewhere? What field/s are the 'problem' fields? I don't see any case where this could return nulls through anything, except through the Select * statement. The only way I can figure that happens, is if the record doesn't exist. Could you try naming the columns in the select, or making you where be where id=@customerId? – Erich Oct 05 '09 at 15:55
  • The 'error message' is after the stored procedure is run as part of the replication process and says, for example, Cannot insert the value NULL into column 'PostCode'. The problem columns are any of type varchar which contain the empty string. There are all being replaced with nulls. If I change the table definition to allow nulls, then the replication will proceed without error, but changes the data to nulls which is incorrect. The record has to exist, (otherwise there will be no conflict) and the replication also works correctly if there are no conflicts (ie changes on one side only) – sgmoore Oct 05 '09 at 17:19
  • I don't see where the error comes from. PostCode? Not in this SP... – gbn Oct 10 '09 at 14:23
  • Postcode is another column in the customer table and if not referenced in the stored procedure, nor is it being updated. However whenever the replication process calls this stored procedure, then afterwards the value of this column is getting changed from the empty string to null. – sgmoore Oct 10 '09 at 16:31

1 Answers1

0

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.

Erich
  • 3,946
  • 1
  • 22
  • 21
  • Thanks for the suggestions. It may take me a while to work through them all. I tried a variant of 2 where I actually set the default constraint to the letter a (to make it easier to see when it was being applied). In that case the empty string was still replaced with null. I am not sure I understand what you mean by the column being required. It is definitely included in the replication. If that is not what you meant, how do I check the 'required' status? – sgmoore Oct 05 '09 at 20:08
  • I'm not sure, I noticed it while searching for SQL replication. Apparently, there is some sort of settings where you can say whether it is required or not. – Erich Oct 05 '09 at 20:49
  • Suggestion 1 did not make any difference. At the stage of the stored procedure being executed, there are no nulls anywhere. No 3 seems more promising. Although this being Microsoft Sql there is (as far as I know) no such thing as a 'before insert'. I will have to read up more on 'instead of' triggers (which I have not used before.) However temporarily allowing nulls and adding an after update trigger does allow me to put the empty strings back. Whether this works depends on whether there are any columns which allow nulls but treat them as meaning something different from the empty string. – sgmoore Oct 06 '09 at 09:38
  • I tried creating an 'instead of' trigger and everything seems to run correctly and just to confirm it when I deleted the trigger the replication errors starting re-occurring. It would have been nice to have a proper fix, but at least this is a workaround, so thank you very much for your help. – sgmoore Oct 12 '09 at 11:04