Trying to insert data into a VARBINARY(max) column manually using mssql:
BEGININSERT INTO [SomeTable]([varbinary])Values (0x255044462D312.....Long VARBINARY(max).....CCB6ED46F9)END
However, when it's inserted a extra heading 0 will be added(0x0255...) and the trailing 9 will be removed(...46F_) When I later try to use this the contents will be corrupt.
Can't figure out why this is happening.
3条答案
按热度按时间j5fpnvbx1#
Solved,
apparently msssql keeps some internal checksum and adds it to the varbinary so if you copy paste it from the table and try to insert it again sql will be confused and try to "correct" this internal checksum. A workaround is to generate a script from the db where you export the table data. The output in this generated script won't contain the internal checksums and you can use this in updates and inserts etc.
hts6caw32#
Try explicit converting:
Last parameter stays for converting type. Check out Binary Styles section here: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
a7qyws3x3#
@Grigoryants saved my bacon with your answer and if the varbinary string is a result of an encryption - to view the original string you can use something like
OPEN SYMMETRIC KEY key_name DECRYPTION BY CERTIFICATE cert_name
SELECT CONVERT(nvarchar(256), DecryptByKey(CONVERT(varbinary(8000), '00D32FD0BD90 ... 21F1C30',2))) GO
Just remember to first remove the 0x at the beginning