SQL Assembly Errors with Unsafe

0

I have the following error message:

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 66007. 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 'powerstatregression, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)
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)

I run this code and the last lines cause the problem:

ALTER DATABASE dasolPSDev SET TRUSTWORTHY ON;
GO

CREATE ASSEMBLY PowerStatRegression
AUTHORIZATION [dbo]
from 'C:\SqlDlls\PowerStatRegression.dll'
WITH PERMISSION_SET = UNSAFE
GO

CREATE PROCEDURE [dbo].[SpCreatePowerStatCorr]
    @strUID [nvarchar](4000),
    @seqId [int],
    @flagProgress [int]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PowerStatRegression].[StoredProcedures].[SpCreatePowerStatCorr]
GO

CREATE PROCEDURE [dbo].[SpCreatePowerStatCorrEx]
    @strUID [nvarchar](4000),
    @seqId [int],
    @flagProgress [int]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PowerStatRegression].[StoredProcedures].[SpCreatePowerStatCorrEx]
GO


CREATE PROCEDURE [dbo].SpCheckCollinearity
    @strUID [nvarchar](4000),
    @seqId [int],
    @flagProgress [int]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PowerStatRegression].[StoredProcedures].[SpCheckCollinearity]
GO


CREATE PROCEDURE [dbo].[AboutPowerStatCorrelation]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PowerStatRegression].[StoredProcedures].[AboutPowerStatCorrelation]
GO

--  To Test
dbo.AboutPowerStatCorrelation
GO

grant execute on SpCreatePowerStatCorr to db_executor
GO
sql
sql-server
sql-server-2005
asked on Stack Overflow Aug 15, 2012 by cdub • edited Aug 15, 2012 by Jens Bj√∂rnhager

2 Answers

0

@chris what other assemblies are you referencing in your project? There may be some other assemblies you need to add to sql server prior to your custom assembly which is what is causing that error. For instance if you are referencing something like Newtonsoft for JSON parsing you would need to add that assembly prior to yours.

answered on Stack Overflow Aug 15, 2012 by scarpacci
0

I just had this error - or at least, a very similar one - when attempting to load a dll that had been built targeting x86 (32 bit), but the Sql Server was x64. The error was only shown when attempting to run the function I registered... it did not complain when registering it!

Changing the build to target 'Any CPU' and re-registering the assembly worked for me.

answered on Stack Overflow Oct 7, 2013 by Nij

User contributions licensed under CC BY-SA 3.0