using a COM library in a SQL CLR procedure

[mtmcclellan]

I've installed a COM object on my machine and a development server
(that is running SQL 2005). On my machine, a wrote a quick command
line app to test usage of the COM object and it works. I copied the
application to the server and it works there too. When I write a new
class library application using the COM object the same way, but in a
method that will be a SQL CLR procedure it fails. The assembly and
stored procedure are successfully created, but when I execute the
procedure, I get the error below. The assembly is created with
PERMISSION_SET = UNSAFE. Also, the .NET build process is creating 3
outputs, an Interop.COMObjectName.dll file, the project file (.exe
or .dll) and a .pdb file.

Any ideas about what is causing the problem, how to solve it, or where
to start looking for the solution?
Thanks.

Msg 10314, Level 16, State 11, Line 2
An error occurred in the Microsoft .NET Framework while trying to
load
assembly id 65681. 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
'nameparsemelissadata, 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)

 

[Nicholas Paldino [.NET/C# MVP]]

Can you move the code from the COM component to .NET code? The whole
point of using the CLR was to get around using extended stored procedures,
which SQL Server didn't have any control over when it came to threading,
memory, deadlocks, etc, etc. With the CLR, SQL Server has control over all
of these things.

If this is a COM object, you are better off using the OLE Automation
Stored Procedures to call the COM object (assuming it is an Automation
class, not just straight up COM).

But in the end, you are better off recoding the functionality in .NET
and then using that in SQL Server.

 

[mtmcclellan]

Can you move the code from the COM component to .NET code? The whole
point of using the CLR was to get around using extended stored procedures,
which SQL Server didn't have any control over when it came to threading,
memory, deadlocks, etc, etc. With the CLR, SQL Server has control over all
of these things.

If this is a COM object, you are better off using the OLE Automation
Stored Procedures to call the COM object (assuming it is an Automation
class, not just straight up COM).

But in the end, you are better off recoding the functionality in .NET
and then using that in SQL Server.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I've installed a COM object on my machine and a development server
(that is running SQL 2005). On my machine, a wrote a quick command
line app to test usage of the COM object and it works. I copied the
application to the server and it works there too. When I write a new
class library application using the COM object the same way, but in a
method that will be a SQL CLR procedure it fails. The assembly and
stored procedure are successfully created, but when I execute the
procedure, I get the error below. The assembly is created with
PERMISSION_SET = UNSAFE. Also, the .NET build process is creating 3
outputs, an Interop.COMObjectName.dll file, the project file (.exe
or .dll) and a .pdb file.

Any ideas about what is causing the problem, how to solve it, or where
to start looking for the solution?
Thanks.

Msg 10314, Level 16, State 11, Line 2
An error occurred in the Microsoft .NET Framework while trying to
load
assembly id 65681. 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
'nameparsemelissadata, 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)- Hide quoted text -

- Show quoted text -

Thanks for the ideas. One constraint is that this is a 3rd party COM
object; the source code is not available (and not for sale). It is
possible to use the OLE Automation (sp_OA*) procedures to reference
this COM object in SQL procedures, but it will be much slower than
wrapping the COM object in a C# project and using the C# methods with
CLR procedures. That's what I'm trying to do, and the CLR procedure
is what is generating the error above. I tried to describe above that
I've already tested wrapping the COM object in a C# project and
calling it from the command line.

I also have new info to add. The SQL 2005 instance I tested on is 64-
bit, running on Windows 64-bit. When I built both the command line
test program and the SQLCLR based program, I specified to build it for
a 32-bit intel processor (because the COM object is 32-bit). The
command line app worked but the SQLCLR app failed. I found a Windows
32-bit, SQL 2005 32-bit instance to test this on and the SQLCLR app
works! So it appears that there is something about SQL 2005 64-bit or
the combination of the way I built the SQLCLR app and the SQL 2005 64-
bit instance that causes the failure.

 

[Nicholas Paldino [.NET/C# MVP]]

Well, the fact that you are running a 32 bit binary in a 64 bit process
is something you can't fix. You will have to remote out to a 32-bit hosted
process, basically.

Also, I don't know why you think that calling the OLE stored procedures
will be ^much^ slower than using interop. Each carries an overhead.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Can you move the code from the COM component to .NET code? The whole
point of using the CLR was to get around using extended stored
procedures,
which SQL Server didn't have any control over when it came to threading,
memory, deadlocks, etc, etc. With the CLR, SQL Server has control over
all
of these things.

If this is a COM object, you are better off using the OLE Automation
Stored Procedures to call the COM object (assuming it is an Automation
class, not just straight up COM).

But in the end, you are better off recoding the functionality in .NET
and then using that in SQL Server.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I've installed a COM object on my machine and a development server
(that is running SQL 2005). On my machine, a wrote a quick command
line app to test usage of the COM object and it works. I copied the
application to the server and it works there too. When I write a new
class library application using the COM object the same way, but in a
method that will be a SQL CLR procedure it fails. The assembly and
stored procedure are successfully created, but when I execute the
procedure, I get the error below. The assembly is created with
PERMISSION_SET = UNSAFE. Also, the .NET build process is creating 3
outputs, an Interop.COMObjectName.dll file, the project file (.exe
or .dll) and a .pdb file.

Any ideas about what is causing the problem, how to solve it, or where
to start looking for the solution?
Thanks.

Msg 10314, Level 16, State 11, Line 2
An error occurred in the Microsoft .NET Framework while trying to
load
assembly id 65681. 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
'nameparsemelissadata, 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)- Hide
quoted text -

- Show quoted text -

Thanks for the ideas. One constraint is that this is a 3rd party COM
object; the source code is not available (and not for sale). It is
possible to use the OLE Automation (sp_OA*) procedures to reference
this COM object in SQL procedures, but it will be much slower than
wrapping the COM object in a C# project and using the C# methods with
CLR procedures. That's what I'm trying to do, and the CLR procedure
is what is generating the error above. I tried to describe above that
I've already tested wrapping the COM object in a C# project and
calling it from the command line.

I also have new info to add. The SQL 2005 instance I tested on is 64-
bit, running on Windows 64-bit. When I built both the command line
test program and the SQLCLR based program, I specified to build it for
a 32-bit intel processor (because the COM object is 32-bit). The
command line app worked but the SQLCLR app failed. I found a Windows
32-bit, SQL 2005 32-bit instance to test this on and the SQLCLR app
works! So it appears that there is something about SQL 2005 64-bit or
the combination of the way I built the SQLCLR app and the SQL 2005 64-
bit instance that causes the failure.