Trying to convert from c# string to SQL binary -


i building c# application needs work binary(16) unique identifiers in our sql server db. currently, application storing these string.

i hoping able pass unique id string varchar parameter , convert within sql statement, getting strange results.

    convert(binary(16), '0x6ec5cae61df38840b8efec2d5a158b3a', 1)     --desired: 0x6ec5cae61df38840b8efec2d5a158b3a     --actual: 0x307836454335434145363144463338383430423845464543324435413135 

is there way convert in way? solution found in various places, haven't been able work. have tried other recommendations:

    convert(binary(16), '0x6ec5cae61df38840b8efec2d5a158b3a', [1-3])     convert(varbinary(16), '0x6ec5cae61df38840b8efec2d5a158b3a', [1-3])     convert(binary(16), '6ec5cae61df38840b8efec2d5a158b3a', [1-3])     convert(varbinary(16), '6ec5cae61df38840b8efec2d5a158b3a', [1-3])     cast('' xml).value('sql:variable("@variable")', 'binary(16)') 

to no avail.

i can store these differently within application if preferable, i'm unsure datatype use? knowledge, there no binary equivalent in c#?

thanks in advance.

i wasn't able uniqueidentifier example work, solution coworker pointed out seems work.

    declare @id varchar(max)     declare @idnew binary(16)     set @id = '0x6ec5cae61df38840b8efec2d5a158b3a'     set @idnew = cast('' xml).value('xs:hexbinary(substring(sql:variable("@id"), 3) )', 'varbinary(max)')      select @idnew 

i understand how xquery works, seems converting correctly.

with this, able pass c# string @id variable, convert, , use @idnew within sql statement.

edit:

credit/more info:

http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx


Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -