Sorry, I couldn't succinctly put this into the title...
I have fields in SQL Server 2008 tables for a website that are set to varchar not null default '' [empty string]. Naturally none of them have null values in but there are plenty of empty ones for superfluous address lines and whatnot.
This all works fine and does exactly what I need. However, we have another system that is synchronising and downloading the data as it is needed, the nature of this is not relevant at the moment, but it is having problems with these empty fields coming through with that swanky black diamond character with a question mark in, like when someone has the wrong encoding. Strangely there is a mixture of empty fields and ones with the character in on the same column, so some rows the empty column will be blank, on others the odd character.
I looked at two records in the SQL data which show differently and they both have empty values (ad1=''), neither is null, but when you take the datalength the one that comes through OK is 1, but the other is 0. The ascii on the one with a single datalength is 32 for a space.
Can anyone shed some light on why this is happening, and what I can do to make sure that the data is consistent?
Ta