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
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: )
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.