SQL SERVER – DROP ASSEMBLY failed because ‘%ls’ is referenced by object ‘%ls’.

Feeds:: Posts; Comments

« SQL SERVER – Best way to check all database autogrowth settings

SQL Server – Clear Find and Replace window history »

SQL SERVER – DROP ASSEMBLY failed because ‘%ls’ is referenced by object ‘%ls’.

February 20, 2013 by Muhammad Imran

In this article we will discuss about an error message (DROP ASSEMBLY failed because ‘%ls’ is referenced by object ‘%ls’) related to Assembly.

Let’s discuss this in detail:
Message Number: 6590

Severity : 16

Error Message: DROP ASSEMBLY failed because ‘%ls’ is referenced by object ‘%ls’.

Error Generation:
I was working on CLR functions development and implementations in SQL, using Visual Studio 2005. After sometime, I had to go to SSMS to drop assembly to create a new one. So I went to SQL Server and wrote a query.

Drop assembly CLRExcelFV
--OUTPUT

Msg 6590, Level 16, State 1, Line 1
DROP ASSEMBLY failed because ‘CLRExcelFV’ is referenced by object ‘FV’.

Ooopps…… I am unable to drop it.

Resolution:

SELECT A.assembly_id,A.name as [Assembly Name],
B.object_id, C. name as [Object Name], C.type, C.type_desc
FROM Sys.Assemblies A
INNER JOIN SYS.ASSEMBLY_MODULES B oN a.assembly_id=B.assembly_id
INNER JOIN SYS.OBJECTS C ON B.object_id = C.object_id
--OUTPUT

Now, in the above result set, you have the assembly name along with its dependent’s objects names (CLR functions).
First lets drop CLR functions and then drop Assembly.

Drop Function FV
Drop Function PV
Drop Function Rate
Drop assembly CLRExcelFV
--OUTPUT

Conclusion :
You cannot drop assembly without dropping its object. Preferably, you should alter the assembly not to drop. But if you need to drop, first drop all its objects and then you can drop Assembly.