Cannot update varchar column with special characters

sql-server, sql-server-2005, t-sql

Solution

Well, the special character you are using:

╡

Is not supported in ASCII. See:

DECLARE @x VARCHAR(32) = 'EC801/╡USB/Exch Acc/JP/PE bag';
SELECT @x;

Result:

EC801/¦USB/Exch Acc/JP/PE bag

So the update is working fine, technically, it's just not doing what you want, because in order to fit into the ASCII space, it has to substitute your character for one that is valid.

In order to support that character, you'll have to use Unicode for your column (and maybe a specific collation, I'm not sure). This works fine:

DECLARE @x NVARCHAR(32) = N'EC801/╡USB/Exch Acc/JP/PE bag';
SELECT @x;

Result:

EC801/╡USB/Exch Acc/JP/PE bag

It will be important to specify the `N` prefix on string literals that contain such characters...

Problem

Originally i thought that this issue is related to C# `TransactionScope` or `Dapper.NET`. But since i have tested the sql in SSMS and the issue remains i assume that it's a pure sql issue. This is the (simplified) update which should update a `varchar(40)` column. I don't get any errors and row-count is 1. The old `SparePartDescription` is `EC801/¦USB/Exch Acc/JP/PE bag`: ``` declare @rowCount int UPDATE [tabSparePart] SET [SparePartDescription] = 'EC801/╡USB/Exch Acc/JP/PE bag' WHERE ([idSparePart] = 13912) set @rowCount = @@ROWCOUNT select @rowCount ``` So the only difference are these special characters: `╡` and `¦`. Maybe you have an idea why i cannot update this column.

Original source