Search code examples
sqlsql-serversql-server-2008varbinary

How to set varbinary column in SQL server

I am trying to update column of type VARBINARY(MAX) by following SQL:

UPDATE dbo.__MigrationHistory 
SET Model = 0x1F8B080000000000046
WHERE MigrationId = '201510051415248_Test2'

And I am expecting that column to be set to 0x1F8B080000000000046 but it set to 0x01F8B080000000000046

How I can set Model to desired value: 0x1F8B080000000000046?

Thanks

Update 1.

0x1F8B080000000000046 is just a random value that I managed to replicate issue with. I didn't put original value, because I it consists of 43679 symbols. And MOST confusing thing is that same value is already in database and I am copying that value from another row and pasting it into my update query, however it will work if update it from select e.g.:

UPDATE dbo.__MigrationHistory
SET Model = (
        SELECT Model
        FROM dbo.__MigrationHistory
        WHERE MigrationId = '201510041415248_Test1'
        ) -- is equal to 0x1F8B080000000000046
WHERE MigrationId = '201510051415248_Test2'

But obviously I cannot use that approach on database where there is no that value initially.

Solution

  • A varbinary isn't displayed with an odd number of digits. The value contains a specific number of bytes, and each byte is displayed as two digits.

    (You can write a binary literal with an odd number of digits, for example 0x123, but then it means the same things as 0x0123.)

    As you copy a value that has 43679 digits, it's not a correct value from the database. Most likely it's because it gets concatenated, either when it is displayed or when you copy it.