MSSQL load assembly error

Question

I am receiving below error while running a function which uses an assembly in MSSSQL:

Msg 10314, Level 16, State 11, Line 36 An error occurred in the Microsoft .NET Framework while trying to load assembly id 65800. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. 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 'exporttoexcel, Version=0.76.0.0, Culture=neutral, PublicKeyToken=f73371c9a1809a3f' or one of its dependencies. Exception from HRESULT: 0x80FC80F1 System.IO.FileLoadException: at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) at System.Reflection.Assembly.Load(String assemblyString)

But after I recreate function and assembly, everything works fine for some period of time:

DROP FUNCTION [dbo].[fnExportToExcel] DROP ASSEMBLY ExcelExporter CREATE ASSEMBLY ExcelExporter AUTHORIZATION [DOMAIN\ACCOUNT] FROM 'ExportToExcel.dll' WITH PERMISSION_SET = UNSAFE CREATE FUNCTION [dbo].[fnExportToExcel](@Query [nvarchar](max), @FilePath [nvarchar](max), @FileName [nvarchar](max), @SheetNames [nvarchar](max)) RETURNS [nvarchar](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME ExcelExporter.[ExportToExcel_DLL.cls_ExcelExport].[ExportToExcel] GO

Any ideas what may cause that MSSQL is loosing the assembly reference?

Are you calling the assembly across databases or just from the database where it lives? — Alex K.
– Alex K., Commented Mar 29, 2017 at 16:37
Perhaps this then: blogs.msdn.microsoft.com/psssql/2010/03/23/… — Alex K.
– Alex K., Commented Mar 30, 2017 at 9:42
@AlexK. Thanks. It seems that that was the issue. The problem is that I had to change the owner to sa using EXEC sp_changedbowner 'sa' to make it work. I wonder why? — ironcurtain
– ironcurtain, Commented Apr 3, 2017 at 13:01
Not sure, perhaps because the sa sid is maintained by the server as a fixed value so will always be "correct". Why not post the fix you applied as an answer. — Alex K.
– Alex K., Commented Apr 3, 2017 at 13:04

ironcurtain · Accepted Answer · 2017-04-12 08:10:06Z

12

I used link provided by Alex K. Link

In my case running this command solved the problem:

EXEC sp_changedbowner 'sa'

answered Apr 12, 2017 at 8:10

ironcurtain

69010 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josh Over a year ago

This solved a similar issue for my local and live projects. Been knocking my head against the wall these past few days. Thanks for this post!

Collectives™ on Stack Overflow

MSSQL load assembly error

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related