Calling on a dll from sql server

I have created a massive dll for manipulating/reading records from my
company databases. In the past I have added refrences to the dll to
access dbs and then called on the sub procs...
Is there a way to use these .net dlls in sql server? Where to I add
refrences to them?
-Peter"Peter" <peter@.mclinn.com> wrote in message
news:dcde2a5a.0408170601.58a08a85@.posting.google.com...
> I have created a massive dll for manipulating/reading records from my
> company databases. In the past I have added refrences to the dll to
> access dbs and then called on the sub procs...
> Is there a way to use these .net dlls in sql server? Where to I add
> refrences to them?
>
You need to expose them to COM interop, host them in COM+ and invoke them
through the sp_oaXXX extended stored procedures.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=u%24dR%233nTEHA.3476%40tk2msftngp13.phx.gbl
David|||Peter/David,
unfortunately direct interoperability of SQL Server 2000 with the CLR is not
supported (http://support.microsoft.com/default.aspx?scid=kb;en-us;322884).
You could alternatively create an extended stored procedure dll in C or
Delphi. If you do create a COM dll, as well as spOA.. extended stored
procedure calls, you can call it using ActiveX scripts in DTS. This is a
scripting environment so you have to use late binding, but it still is a
more familiar environmnet for developers. It has the benefit of easier error
handling and debugging. On the downside I have found it slightly slower than
using the sp_OA..procedures. If you want to use the latter method, look in
BOL for an example that uses SQLDMO
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ac
data.chm::/ac_8_qd_14_2ktw.htm).
HTH,
Paul Ibison|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eGFSluIhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Peter/David,
> unfortunately direct interoperability of SQL Server 2000 with the CLR is
not
> supported
(http://support.microsoft.com/default.aspx?scid=kb;en-us;322884).
Which is why you need to host the component in COM+. When you install a COM
component as a COM+ server application, the DLL actually runs in the
dllhost.exe process. COM supplies an unmanaged proxy interface in your
local process and proxies the calls into the dllhost.process.
This gets aground the problem mentioned in the knoledge base since the
COM-callable wrapper and the .NET assembly are loaded only in the dllhost
process.
David|||David,
very interesting - it looks clever and I have never heard of such a solution
before. Does the unmanaged proxy interface support QueryInterface? What
about fibers? If both of these are supported then I agree that your solution
looks a generally viable one. However I have not seen a precedent on the MS
website advocating this method which makes me very wary (the only page I've
seen is the one I referred to). If you or anyone else has a MS reference to
this then please post it up.
Regards,
Paul Ibison