Why does the CONVERT string for VARBINARY in SQL Server only convert the first character?

Question

I use NLog to enter my application and as part of this, we register the client number, which is a string in C #, and varbinary (32) in the database. I am using the following SQL code for this particular parameter. The rest of the SQL statement works just 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 stored in the actual table is only the first character of the string of the original client number. I checked double and triple to make sure that I do not truncate the row before it is sent to the database. Any help would be greatly appreciated.

Answer 1

The reason is that when you paste, you convert the Unicode string (nvarchar (xx)) to varbinary. Then, when you select, you convert to varchar (xx). If you convert to nvarchar (xx), it will work fine.

For example:

• The insert "this is a test" because varbinary (30) results in 0x7468697320697320612074657374.

• N'this insert is a test ', since varbinary (30) results in 0x74006800690073002000690073002000610020007400650073007400.

Therefore, when you convert back, if you specify varchar (30), the first 00 will crop 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 it does

 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