Adding CLR assemblies after Server Migration

the CLR assemblies are brought across with the database. However they need to be added to the sys.trusted_assemblies to actually run

  1. Go to the database you have the assemblies in. It’s different for each database using CLR assemblies.

List them with sys.assemblies. You need the clr_name field

select * from sys.assemblies

List your CLR assembly files with

select * from sys.assembly_files

you need the hash field

Then run this script for each assembly

–run from create assembly after from copied to @asmbin
USE master;
GO
DECLARE @clrName nvarchar(4000) = ‘clr_name’ –this name is quoted
DECLARE @asmBin varbinary(max) = hash  –no quotes here paste the entire value as is.
DECLARE @hash varbinary(64);

SELECT @hash = HASHBYTES(‘SHA2_512’, @asmBin);

EXEC sys.sp_add_trusted_assembly @hash = @hash,
@description = @clrName;

 

go

Leave a Reply

Your email address will not be published. Required fields are marked *