0
update dbo.blobs
set blob = (select convert (varbinary(max), 0x02000000FFFF02000F004342616467654974656D42616467650200000000000000000000000020030000F4010000FFFFFF00FFFFFF000000000000010000000100000002FFFEFF00FFFEFF00020000000000000000000000000000000000000000000000000000000000000000000000000000000000FFFF02000F004342616467654974656D50686F746F0200000003C70000004800000044020000A5010000C0C0C000000000000001000000010000000100000001FFFEFF00FFFEFF00020000000100000001000000010000000100000000000000))
where blob_date = '2022-02-22 10:03:21.713'

This query works if the blob is 20,000 char or less, but will not work for larger blobs (The majority in the database are 45k or more and I need to update them with 45-60k char blobs). The where statement goes black when the string gets too long, I've tried running it in SSMS and Azure Data Studio where I've increased the tokenization value past the char limit without success

Thom A
  • 88,727
  • 11
  • 45
  • 75
wakesnow564
  • 13
  • 4
  • 1
    If the only trouble is editor support, split it up and concatenate (with newlines mixed in): `declare @blob varbinary(max) = 0x02000000FFFF02000F + 0x00434261646765497465 + ...; update set blob = @blob`. Sufficiently huge statements may become a problem for the SQL parser itself; in that case you may have to split those up as well (`set @blob += ...`). Of course if you're doing this regularly I'd argue you need dedicated client tool support for streaming those blobs in, doing this from text-based editors is clunky. – Jeroen Mostert Feb 23 '22 at 16:21
  • You should really be passing these in from a client app via a parameter, not injecting them into a SQL query string – Charlieface Feb 23 '22 at 17:20

0 Answers0