Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / .NET Framework / New Users / August 2007

Tip: Looking for answers? Try searching our database.

using a COM library in a SQL CLR procedure

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
mtmcclellan@juno.com - 08 Aug 2007 15:35 GMT
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] - 08 Aug 2007 16:13 GMT
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.

Signature

         - Nicholas Paldino [.NET/C# MVP]
         - mvp@spam.guard.caspershouse.com

> 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
[quoted text clipped - 37 lines]
> forIntrospection)
>   at System.Reflection.Assembly.Load(String assemblyString)
mtmcclellan@juno.com - 08 Aug 2007 17:29 GMT
On Aug 8, 11:13 am, "Nicholas Paldino [.NET/C# MVP]"
<m...@spam.guard.caspershouse.com> wrote:
>     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,
[quoted text clipped - 60 lines]
>
> - 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] - 08 Aug 2007 17:36 GMT
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.

Signature

         - Nicholas Paldino [.NET/C# MVP]
         - mvp@spam.guard.caspershouse.com

> On Aug 8, 11:13 am, "Nicholas Paldino [.NET/C# MVP]"
> <m...@spam.guard.caspershouse.com> wrote:
[quoted text clipped - 85 lines]
> the combination of the way I built the SQLCLR app and the SQL 2005 64-
> bit instance that causes the failure.

Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.