Z
Z
zhaar2019-05-22 15:15:01
Hashing
zhaar, 2019-05-22 15:15:01

How to calculate the hash of a long string in MSSQL?

There is a label that is used to generate html files from the database via BCP. The sizes of the generated files vary from 2 to 15 KB. All html code for files is stored in one varchar(max) field. Those. 1 line = 1 uploaded file
Files are attached to another service as attachments, and the final result is a message consisting of a message header (there is access there) and a generated attachment (there is no longer access).
In order not to drive 20-30k lines each time, each time unloading the same values, the idea came up to form a hash from the contents of the attachment and write it in the message header.
At the stage of generating messages, check if the hash is needed in the message headers, compare it with the hash of the contents of the html file and upload only those records that are missing or have changed.
On the test data, which contained about 8k characters, there were no problems, but when the query came across a longer string, it caught the error
Msg 8152, Level 16, State 10, Line 9
String or binary data would be truncated.
The query itself that generated the hash is simple:
SELECT HASHBYTES('SHA1', html) FROM tbl1
Question - how to get around the 8k character limit when creating a hash?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
Konstantin Tsvetkov, 2019-05-22
@tsklab

For SQL Server 2017, this restriction has been lifted ( documentation ). For previous versions, you can use CHECKSUM or write the function yourself.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question