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.

Why are you logging a string into a varbinary and not a varchar/nvarchar? — Oded
– Oded, Commented May 9, 2012 at 19:12
Just a requirement of my company, I suppose it is a naive attempt at a security measure. My part is not to question why, just to scream at the computer. — ClaytonHunt
– ClaytonHunt, Commented May 9, 2012 at 19:13
Truncating to 1 usually means you are missing a length declaration in a parameter or variable definition. Not familiar with NLog but does that expect a length property? — Martin Smith
– Martin Smith, Commented May 9, 2012 at 19:14
If I am, I am completely at a loss as to where that would be. I am using a string on the C# side, no size declaration there. I am using a varbinary(32) in the convert. Definite size declaration there. I am using a varbinary(32) in the table. Definite size declaration there. I am using inline SQL - commandText in Nlog to run this query, but you don't define parameter lengths that I am aware of for Nlog. I'm going to double check that...Yeah, no size for nlog. — ClaytonHunt
– ClaytonHunt, Commented May 9, 2012 at 19:18
@ClaytonHunt - Looks like there is a size property that needs setting from the grammar here. You can use SQL Server Profiler to see the commands sent to the DB which might shed some light on things if that doesn't help... — Martin Smith
– Martin Smith, Commented May 9, 2012 at 19:22

Phil · Accepted Answer · 2012-05-09 19:40:54Z

18

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

answered May 9, 2012 at 19:40

Phil

43.2k9 gold badges102 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Phil Over a year ago

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

ClaytonHunt Over a year ago

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

ClaytonHunt Over a year ago

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

Phil Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related