HASHBYTES function

Question

The below script gives the following error message:

"Msg 8152, Level 16, State 10, Line 10 String or binary data would be truncated."

 select convert(varbinary(max),HASHBYTES('md2',(select [Secteur]
  ,[Hub]
  ,[Group]
  ,[Enterprise]
  ,[Manager]
  ,[BM]
   from [Hubs] for XML auto)))

I mention that the maximum length of input rows is 123 bytes.

Any ideas why this message? (I use SQL Server 2014) Many thanks

official documentation says,maximum return value is varbinary (maximum 8000 bytes) — TheGameiswar
– TheGameiswar, Commented Aug 6, 2017 at 9:54
Or more pertinently it also says "For SQL Server 2014 and earlier, allowed input values are limited to 8000 bytes." — Martin Smith
– Martin Smith, Commented Aug 6, 2017 at 12:38
You have neither an ORDER BY nor a WHERE in your query. That means you're converting the entire table to XML, with a row ordering that's not deterministic. That's probably not what you want as an input for a hash. If all you want is a mechanism to detect if the data has changed and you're not married to an MD2 hash over XML, consider using CHECKSUM_AGG. — Jeroen Mostert
– Jeroen Mostert, Commented Aug 6, 2017 at 13:12

Tim · Accepted Answer · 2017-08-06 13:04:42Z

1

HASHBYTES is limited to 8000 character input in SQL 2014 and older. It is not limited in SQL 2016+. I suspect your query when converted to XML is more than 8000 bytes.

You can use the undocumented fn_repl_hash_binary to get an MD5 hash (but not an MD2) in sql 2014 for long input data, but I don't recommend it. Not only is it undocumented, it does not work in Azure SQL at all.

I believe you will need to use a CLR function to calculate an MD2 of large binary data in SQL 2014.

answered Aug 6, 2017 at 13:04

Tim

6,0681 gold badge16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

HASHBYTES function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related