why is CONVERT string to VARBINARY in SQL Server only converting first character?

Question

I am using NLog to log in my application and as part of that we are logging the customer number, which is a string in C#, and a varbinary(32) in the database. I am using the following SQL code for this specific parameter. The rest of the SQL statement works fine:

CONVERT(varbinary(32), @CustNumber)

and the following NLog parameter:

<parameter name="@CustNumber" layout="${event-context:item=CustNumber}" />

and the following code in C# to add the Nlog parameter:

myEvent.Properties.Add("CustNumber", custNumber);

For some reason the value being stored in the actual table is only the first character of the original Customer Number string. I have double and triple checked to make sure I am not truncating the string before it is sent to the database. Any help would be greatly appreciated.

Solution

The reason is that when you insert you're converting a Unicode (nvarchar(xx)) string to varbinary. Then when you select you're converting to varchar(xx). If you convert to nvarchar(xx) it will work fine.

For example:

• inserting 'this is a test' as varbinary(30) results in 0x7468697320697320612074657374.

• inserting N'this is a test' as varbinary(30) results in 0x74006800690073002000690073002000610020007400650073007400.

So when you convert back, if you specify varchar(30) the first 00 will truncate the string.

This works fine for me:

delete from Table_2

insert Table_2 (Test) values( CONVERT(varbinary(30), N'this is a test') ) 
select * from Table_2
select CONVERT(nvarchar(30), test) from Table_2

and so does this

delete from Table_2

insert Table_2 (Test) values( CONVERT(varbinary(30), 'this is a test') )
select * from Table_2
select CONVERT(varchar(30), test) from Table_2

Comments

[Phil]

@ClaytonHunt Well maybe it's the other way around, but it's almost certainly a varchar/nvarchar mismatch.

[ClaytonHunt]

If I am not doing the CONVERT wrong, I think the issue lies in NLog somewhere

[ClaytonHunt]

I stand corrected, you sir are correct. It's magic. Thanks Don't suppose you know how to get NLog to use VARCHAR?

[Phil]

@ClaytonHunt: No sorry, I have never used NLog.