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?
Best Answer
Just do
You are converting the
varchar
representation tovarbinary(max)
which is not correct.SELECT CONVERT(VARBINARY(MAX), '0xAAFF')
returns0x307841414646
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.