I recently had an Sql Server 2014 r2 server I needed to migrate to Sql Server 2019 r2 with a set of CLR assemblies on it. My plan is to detach each database and reattach them on the new box. The assemblies are transferred along with the database, but they won’t run. I went through the following steps to run them.
Step 1. Click on Programmability, Assemblies under the Database in Sql Server Management Studio.
Step 2: Right click and choose Script Assembly as Create to New Query Editor Window
you’ll get something that looks like this
CREATE ASSEMBLY [Microsoft.SqlServer.Types]
from XXXXXX
with permission_set=unsafe
Step #3, run the query select * from sys.assemblies you’ll get something that looks like this
| name | principal_id | assembly_id | clr_name |
| Microsoft.SqlServer.Types | 4 | 1 | microsoft.sqlserver.types, version=15.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil |
Step #4 You have to make the assemblies trusted. Run the following code. Substitute the XXXXX where indicated and the clr_name from above
–run from create assembly after from copied to @asmbin
USE master;
GO
DECLARE @clrName nvarchar(4000) = ‘microsoft.sqlserver.types, version=15.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil’
DECLARE @asmBin varbinary(max) = XXXXXXXX
DECLARE @hash varbinary(64);
SELECT @hash = HASHBYTES(‘SHA2_512’, @asmBin);
EXEC sys.sp_add_trusted_assembly @hash = @hash,
@description = @clrName;
go
Step #5. Here’s the query to list the trusted assemblies (I put XXXX for hash, you can take that out)
SELECT ‘XXXX’ as hash, description, create_date
FROM sys.trusted_assemblies
run as
SELECT hash, description, create_date
FROM sys.trusted_assemblies
with these results
| hash | description | create_date |
| XXXX | microsoft.sqlserver.types, version=15.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil | 1/2/2023 |
