11

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.

ClaytonHunt
  • 533
  • 1
  • 7
  • 19
  • 1
    Why are you logging a string into a `varbinary` and not a `varchar`/`nvarchar`? – Oded May 09 '12 at 19:12
  • 1
    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 May 09 '12 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 May 09 '12 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 May 09 '12 at 19:18
  • @ClaytonHunt - Looks like there **is** a `size` property that needs setting [from the grammar here](http://nlog-project.org/wiki/Database_target). 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 May 09 '12 at 19:22
  • @Oded '@CustNumber' is not specifically defined, I assume that either NLog or ADO.NET are dynamically figuring that out based on the object that I store in the NLog collection – ClaytonHunt May 09 '12 at 19:23
  • @MartinSmith Too true, I actually just saw that. I am testing it now, but if you want to write that up as an answer I will certainly mark it. If it works that is... I think it will though – ClaytonHunt May 09 '12 at 19:26
  • The default length for varchar() is probably 1 (is this true in all databases?). Declare @CustNumber as varchar(32). – Gordon Linoff May 09 '12 at 19:32
  • @MartinSmith the size attribute in the NLog parameter doesn't seem to have any effect, I'm not sure if that is NLog or if I am doing it wrong. I am attempting to setup the SQL profiler now. – ClaytonHunt May 09 '12 at 19:45

1 Answers1

15

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
Phil
  • 42,255
  • 9
  • 100
  • 100