- 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).
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?