msg: 6528, Assembly 'XYZCLRDatabase' could not be found in the SQL catalog for database 'XYZ'

Question

I executed the following command to my database and it give me the message Commands completed successfully.

USE XYZ
GO
DECLARE @clrName nvarchar(4000) = 'XYZCLRDatabase, ...';
DECLARE @asmBin varbinary(max) = <bindary>;
DECLARE @hash varbinary(64);

SELECT @hash = HASHBYTES('SHA2_512', @asmBin);

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

It also shows the same record into the sys.trusted_assemblies table.

But it does not listed into the Assemblies folder...

XYZ database > Programmability > Assemblies

When I am trying to create the Stored Procedures with the following code I get the error.

USE XYZ
GO
CREATE PROCEDURE SPName @sqlXml XML, @flag1 bit, @flag2 bit, @id int null, @flag3 bit
AS
EXTERNAL NAME XYZCLRDatabase.StoredProcedures.MYClrSP

I get the following error message:

Msg 6528, Level 16, State 1, Procedure SPName, Line 1 [Batch Start Line 23]
Assembly 'XYZCLRDatabase' could not be found in the SQL catalog for database 'XYZ'.

what goes wrong....

Answer 1

You are missing the CREATE ASSEMBLY instruction. In summary, the steps to add an assembly in SQL Server are:

1. Make sure the SQL Server instance allows CLR: EXEC sp_configure 'clr enabled', 1; RECONFIGURE;

   This step (#1) should only be required once.

2. Call the sp_add_trusted_assembly stored procedure

3. Call the CREATE ASSEMBLY instruction