Load assembly from Stored Procedure

Hi,
Is it possible to load an assembly either from a byte array or from the disk at runtime from a CLR stored procedure.

I have created a stored procedure that needs to load different assemblies depending on a query and then invokes a method (called execute() passing a string query as a parameter).
From debugging the CLR stored procedure in .NET I have found that it is this line that creates an error:

Assembly myAssembly= Assembly.Load()

It doesnt matter if I attempt to load the assembly from it's byte array or from a file location, it always gives the following error:

Msg 6522, Level 16, State 1, Procedure runQuery, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'runQuery':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly)
at ClientInterface.RunInterface(String queryName, String parameters, String& returnValue)
It works perfectly if I run it using a mock up front end app it just seems to be sql server that doesnt like it.

Anybody have any ideas?
Assembly.Load() should work, BUT the assembly you are loading has to be catalogued in the database.

You are not allowed to load assemblies from anything but the database, that's why you are getting the error you see above: A .NET Framework error occurred... have been disabled by the host."

Niels
|||Thanks for replying Niel.

I will try loading the assemblies into the database and use Assembly.Load(assemblyName) to get it working.

N
sql

Load assembly from Stored Procedure

Hi,

Is it possible to load an assembly either from a byte array or from the disk at runtime from a CLR stored procedure.

I

have created a stored procedure that needs to load different assemblies

depending on a query and then invokes a method (called execute() passing a string query as a parameter).

From debugging the CLR stored procedure in .NET I have found that it is this line that creates an error:

Assembly myAssembly= Assembly.Load()

It

doesnt matter if I attempt to load the assembly from it's byte array or

from a file location, it always gives the following error:

Msg 6522, Level 16, State 1, Procedure runQuery, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'runQuery':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:

at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[]

rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark,

Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly)
at ClientInterface.RunInterface(String queryName, String parameters, String& returnValue)

It works perfectly if I run it using a mock up front end app it just seems to be sql server that doesnt like it.

Anybody have any ideas?

Assembly.Load() should work, BUT the assembly you are loading has to be catalogued in the database.

You are not allowed to load assemblies from anything but the database, that's why you are getting the error you see above: A .NET Framework error occurred... have been disabled by the host."

Niels|||Thanks for replying Niel.

I will try loading the assemblies into the database and use Assembly.Load(assemblyName) to get it working.

N

Load assemblies - CLRIntegration

All -
I have two assemblies (lets say AsmA and AsmB) loaded into SQL Server
(Create Assembly)
Both these assemblies have EXTERNAL_ACCESS permissions.
When the code inside AsmA is executed (within SQLServer context), it tries
to load a type in AsmB "dynamically"
(using CreateInstanceAndUnwrap) and it fails saying "Unable to locate AsmB".
This is the exact error
"Could not load file or assembly 'xxxxx' or one of its dependencies. The
system cannot find the file specified. at System.Reflection.Assembly.nLoad
"
Where does SQLServer put these 2 assemblies when they are created?
Are they stored in the same location?
If yes then LoadAssembly should ve succeeded.
Is it permitted to load a assembly dynamically (I am not getting a
HostProtected Exception so i presume it is permitted ) And remember i am
loading the assembly which is *already* created in SQLServer.
Please provide a solution for this.
Regardz
Grafix.> Where does SQLServer put these 2 assemblies when they are created?
> Are they stored in the same location?
> If yes then LoadAssembly should ve succeeded.
> Is it permitted to load a assembly dynamically (I am not getting a
> HostProtected Exception so i presume it is permitted ) And remember i am
> loading the assembly which is *already* created in SQLServer.
>
You are allowed to call Assembly.Load(). However the assemblies have to
be owned by the same owner, i.e. they are in the same appdomain. From
what you write, it sounds like the assemblies are in different app
domains, and remoting is not allowed inside SQLCLR.
Niels|||> You are allowed to call Assembly.Load().
Thats surprising. As a simple test, wihtin Clr context, i tried to do a
Assembly.Load and got a HostProtection exception. May be i have to change
ething to UNSAFE from EXTERNAL_ACCESS then.

> From what you write, it sounds like the assemblies are in different app
> domains, and remoting is not allowed inside SQLCLR.
The assemblies belong to the same owner [dbo].
AsmB infact has a compile time reference to AsmA.
But AsmA doesnt know the types in AsmB and does a dynamic create on it.
[Popularly known by the term "Plugin components"]
[No remoting and other complications.]
While creating thetype of AsmB using
"AppDomain.CurrentDomain.CreateInstanceAndUnwrap" it fails.
The stack trace shows CreateInstacneAndUnwrap calls Assembly.Load internally
and that says "FileNotFound".
One possible reason i could imagin is that since AsmB is "not already
loaded" into SQLServer's addressspace - the first time Assembly.Load is
called it searches for the assmbly in current folder(windows\system32 where
sqlserver runs from) and doesnt find it there and cries.
How can we make SQLServer know that when i say CreateInstanceAndUnwrap -
look into ur own database where u have stored the assembly than search
externally?
Any thoughts/
Regardz
Grafix.
"Niels Berglund" wrote:

> You are allowed to call Assembly.Load(). However the assemblies have to
> be owned by the same owner, i.e. they are in the same appdomain. From
> what you write, it sounds like the assemblies are in different app
> domains, and remoting is not allowed inside SQLCLR.
> Niels
>|||Grafix wrote:
> Thats surprising. As a simple test, wihtin Clr context, i tried to do a
> Assembly.Load and got a HostProtection exception. May be i have to change
> ething to UNSAFE from EXTERNAL_ACCESS then.
Hmm, I can do that without any problems. Just tested.

>
> The assemblies belong to the same owner [dbo].
> AsmB infact has a compile time reference to AsmA.
> But AsmA doesnt know the types in AsmB and does a dynamic create on it.
> [Popularly known by the term "Plugin components"]
> [No remoting and other complications.]
> While creating thetype of AsmB using
> "AppDomain.CurrentDomain.CreateInstanceAndUnwrap" it fails.
> The stack trace shows CreateInstacneAndUnwrap calls Assembly.Load internal
ly
> and that says "FileNotFound".
You need to supply the fully qualified assembly name, something like so:
select dbo.LoadAsm('asm2, version=0.0.0.0, culture=neutral,
publickeytoken=null, processorarchitecture=msil')

List registered assemblies

Hi,

It's possible to register an assembly in SqlServer 2005 using the

CREATE ASSEMBLY

syntax.

How can I get a list of assemblies that have been registered?

Thanks

Herbj?rn

Select * from sys.assemblies ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de