Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Basic info

  • Database: SQL Server Express 2008 R2
  • Client: SQL Server Management Studio

Backround (skip if not interested):

A project I'm maintaining uses an ORM, which apparently stored my enum values (which inherit from Byte) into binary serialized .Net objects stored in a varbinary(max) field. I only found out this was happening after a new requirement emerged dictating my code to run under medium trust. Since the .Net binary formatter needs full trust to be called, it started crashing on the enums.

To clean the mess up I need to create migration scripts that will convert these (varbinary(max)) values back to integer values. There are only a handful of different values so it shouldn't be a big problem (I thought).

The problem:

I am able to get string representations of the blobs when selecting:

SELECT BinValue FROM MyTable where Type = 'Object';

It returns a string '0x...(an array of hexadecimal values)'.

But when I try to select on the column using copy-and-paste making sure I have the exact binary equivalent:

SELECT ItemId FROM MyTable WHERE Value=convert(varbinary(max), '0x...')

it does not return any records.

So is it possible to solve this using a client (such as Management Studio)?

If so, what would be the correct syntax?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

Just do

SELECT ItemId
FROM   MyTable
WHERE  Value = 0xAAFF 

You are converting the varchar representation to varbinary(max) which is not correct.

SELECT CONVERT(VARBINARY(MAX), '0xAAFF') returns 0x307841414646 for me for example. It will give you a result based on the character codes in that string representation in your default collation's code page.

share|improve this answer
    
Thanks, that works. It didn't enter my head to try removing quotation marks, argh... –  Louis Somers Mar 2 '13 at 18:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.