Re: Extended Stored Procedures developed in .NET?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/22/05 

Date: Tue, 22 Feb 2005 18:34:06 +0100

No, not in managed code. There's a KB describing that it is not supported for SQL Server to host
managed code. Below is my "canned" answer:

It is not supported for extended stored procedures or sp_OA procedures to call .NET code in CLR;
hosted within SQL Server's address space..

See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;322884

Also, below is with permission from David Browne, explaining how you can have SQL Server execute CLR
code executing in its own process:

"
Short answer: Don't do it.

Calling managed code inside a stored procedure is not supported.
http://support.microsoft.com/default.aspx?scid=kb;en-us;322884

At least not directly. You need some sort of unmanaged proxy to communicate
with your component running in another process.

For instance, http, or, drum roll, a COM+ Server Application.

This will cause COM+ to load an unmanaged proxy object in the SqlServer
process and will load the CLR into a COM+ surrogate process (dllhost.exe).
Which somebody here mentioned last week, and I just got around to testing.

It's all perfectly transparent to you, but you have to set up the COM+
server application.

Remember this is something different from .net remoting. With .NET remoting
you have a _managed_ proxy object in the local process, and so you load the
CLR in the local process as well as the remote process.

Anyway here's what I did:

I created this VB class

comTest.vb listing:

Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual),
ProgId("comTest.comTestClass")> _
Public Class comTest
  Public Function Hello() As String
    Return "hello"
  End Function
End Class

build comTest.dll and registered it with
  regasm /codebase comTest.dll /tlb:comTest.tlb

(complains that I haven't strong-named my assembly, which you should do.)

created an empty COM+ server application, set to run under a local
administrator account, and dragged comTest.dll into its components folder.

created an unmanaged host (vbscript will do), and invoked the component
using IDispach just like SQLServer.

test.vbs listing

  Set d = CreateObject("comTest.comTestClass")
  MsgBox d.Hello

Then I used the .net command line debugger cordbg.exe's 'pro' command to
list the processes hosting the CLR. And procexp.exe from
www.sysinternals.com to verify that the CLR's dll's were not loaded in my
unmanaged process. My unmanaged host did not load the CLR, although it
loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.

Then in sql I ran

declare @object int
declare @msg varchar(50)
declare @rc int
declare @hr int
declare @source varchar(1000)
declare @description varchar(1000)

exec @rc = sp_oacreate 'comTest.comTestClass', @object output
if @rc <> 0
begin
 EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
 print 'create failed ' + @description
 return
end

exec @rc = sp_oamethod @object, 'Hello', @msg output
if @rc <> 0
begin
 EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
 print 'method failed ' + @description
 return
end

print 'return: ' + @msg
exec @rc = sp_oadestroy @object
if @rc <> 0
begin
 EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
 print 'destroy failed ' + @description
 return
end

Ran fine, and still only one CLR loaded into dllhost.exe's process. So
think we can safely conclude that COM+ server applications do not violate
the prohibition against running managed code in SQLServer's process and
provide a convenient mechanism for interoperating with managed code from
TSQL.

David

" 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon Pope" <jon.pope@healthlinkinc.com> wrote in message 
news:u3nFUEQGFHA.2156@TK2MSFTNGP10.phx.gbl...
> Is it possible to create an extended sproc for SQL2K using .NET (ie C#, VB.NET, etc)?  All of the 
> examples I've found focus on C++.
>
> Cheers, Jon
>

Relevant Pages

  • Re: User defined types command parameter types
    ... > the server telling it what it wants done. ... > aren't going to need or want SQL CLR. ... >>avoided if Visual Studio was more tightly integrated with SQL server. ... >>like to see better integration of stored procedures and application code. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Extended Stored Procedures developed in .NET?
    ... > Calling managed code inside a stored procedure is not supported. ... > For instance, http, or, drum roll, a COM+ Server Application. ... > process and will load the CLR into a COM+ surrogate process. ... >> Cheers, Jon ...
    (microsoft.public.sqlserver.programming)
  • Re: User defined types command parameter types
    ... the server telling it what it wants done. ... SQLS 2005 hosting the CLR isn't going to make any difference in this ... >avoided if Visual Studio was more tightly integrated with SQL server. ... >like to see better integration of stored procedures and application code. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Extended Stored Procedures developed in .NET?
    ... >> No, not in managed code. ... There's a KB describing that it is not supported for SQL Server to host ... >> It is not supported for extended stored procedures or sp_OA procedures to call .NET code in CLR; ... >> declare @msg varchar ...
    (microsoft.public.sqlserver.programming)
  • Re: compiled Delphi.NET?
    ... I was at PDC 2003 and spoke with Jose Blakely, SQL Server Architect ... The presentation was on hosting the CLR, ... The crux of .NET is managed code, otherwise, what's the point? ...
    (borland.public.delphi.non-technical)