MSSQL load assembly error

6
votes

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?

1
sql-server.net-assembly
Are you calling the assembly across databases or just from the database where it lives? – Alex K.
I am calling it across databases. – ironcurtain
Perhaps this then: blogs.msdn.microsoft.com/psssql/2010/03/23/… – Alex K.
@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
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.

1 Answers

7
votes

I used link provided by Alex K. Link

In my case running this command solved the problem:

EXEC sp_changedbowner 'sa'