Link to home
Start Free TrialLog in
Avatar of Russ Suter
Russ Suter

asked on

HASHBYTES fails on SQL Server 2012 but not 2016

I'm using the following query to get information about CLR assemblies attached to a SQL Server database.
SELECT
	[assemblies].[name],
	[assemblies].[principal_id],
	[database_principals].[name] AS [principal_name],
	[assemblies].[assembly_id],
	[permission_set_desc],
	[is_visible],
	[assembly_files].[name] AS [assembly_file_name],
	ASSEMBLYPROPERTY([sys].[assemblies].[name], 'SimpleName') AS [simple_name],
	[clr_name],
	[content],
	HASHBYTES(N'SHA2_512', [assembly_files].[content]) AS [SHA512HASH]
FROM
	[sys].[assemblies]
INNER JOIN
	[sys].[database_principals] ON
	[sys].[database_principals].[principal_id] = [sys].[assemblies].[principal_id]
INNER JOIN
	[sys].[assembly_files] ON
	[sys].[assembly_files].[assembly_id] = [sys].[assemblies].[assembly_id]
WHERE
	[is_user_defined] = 1

Open in new window

I have two identical databases (structurally, though the data they contain is different). One is attached to a SQL Server 2012 instance. The other is attached to a SQL Server 2016 instance. Here is the full @@Version information for each.
Microsoft SQL Server 2012 (SP1) - 11.0.3156.0 (X64)   May  4 2015 18:48:09   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) 

Open in new window

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)   Mar 18 2018 09:11:49   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

Open in new window

I'm hoping that there's some kind of known bug with using HASHBYTES on a SQL Server 2012 instance but I can't find any documentation to that effect. Can anyone give me a clue as to why it fails on the 2012 instance but works fine on the 2016 instance?
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Russ Suter
Russ Suter

ASKER

As defined by Microsoft Docs it's a VARBINARY(MAX)
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-assembly-files-transact-sql?view=sql-server-2017

It would appear that is my problem then. I'll switch to calculating the hash using a .NET library instead. Thanks.