SQL Server, .NET, Python and Other Programming Troubleshooting Articles

System.IO.FileLoadException: could not load file or assembly…

by

Hi friends, in this article, we will be discussing about, how to resolve the below error, when working with CLR assemblies in SQL Server:

An error occurred in the Microsoft .NET Framework while trying to load assembly id… The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: could not load file or assembly …

 

How to Resolve the Issue

In order to resolve the issues, there is a series of steps that you can try.

 

Step 1: Check if CLR is Enabled in the SQL Server Instance

The first thing to check, which is quite obvious, is whether CLR is enabled or not, in the specific SQL Server instance.

In order to check this, you just need to run in SSMS the below T-SQL statement:

sp_configure 'clr enabled';
GO

In the “run_value” is set to 1, it means that CLR is indeed enabled.

If it is not enabled and you have the approval to indeed enable it, you can do so, using the below T-SQL statements:

sp_configure 'clr enabled',1;
GO
RECONFIGURE WITH OVERRIDE;
GO

 

Step 2: Check the CLR Assembly File Path References

The next step, is to check the CLR assemblies that are loaded into the SQL Server instance.

To do so, you need to run the following SELECT T-SQL statement:

SELECT * FROM sys.assembly_files;
GO

Based on the query results, you should make sure that the assemblies listed, are indeed located in the referenced file path.

If a reference assembly file (dll) is missing, then that’s the problem and you need to register again the assembly (step 4a).

 

Step 3: Check the Loaded Assemblies

The next step, is to check the CLR assemblies that are loaded into the SQL Server instance.

To do so, you need to run the following SELECT T-SQL statement:

SELECT * FROM sys.dm_clr_loaded_assemblies;
GO

If the above query does not return anything, then bingo, you just found the problem!

So, in this case, it means that your CLR assembly is not properly registered and you need to refresh it (step 4b).

 

Step 4a: Registering the CLR Assembly Again in SQL Server

So, if the issue was identified in Step 2, then it means you need to re-register the specific CLR assembly in SQL Server.

To this, you can run the following T-SQL statement:

--Recommendation: Only use CLR Assemblies with SAFE permissions
CREATE ASSEMBLY [ASSEMBLY_NAME]
FROM 'path to assembly .dll file' 
WITH PERMISSION_SET = [SET THE PERMISSION SET HERE: SAFE, UNSAFE OR EXTERNAL]

 

Step 4a: Refreshing the CLR Assembly in SQL Server

So, if the issue was identified in Step 3, then it means you need to refresh the specific CLR assembly in SQL Server.

To this, you can run the following T-SQL statement:

ALTER ASSEMBLY [ASSEMBLY_NAME]
FROM 'path to .dll file';
GO

 

 

Learn more about SQL Server Development – Enroll to our Course!

Enroll to our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link) and sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

 

Featured Online Courses:

 

Read Also:

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 3,870