VB.NET, etc)? All of the examples I've found focus on C++.
Cheers, JonNo, not in managed code. There's a KB describing that it is not supported fo
r SQL Server to host
managed code. Below is my "canned" answer:
It is not supported for extended stored procedures or sp_OA procedures to ca
ll .NET code in CLR;
hosted within SQL Server's address space..
See:
http://support.microsoft.com/defaul...kb;en-us;322884
Also, below is with permission from David Browne, explaining how you can hav
e 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/defaul...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 w
, 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
>|||You rock!
Cheers, Jon
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23yum2SQGFHA.1140@.TK2MSFTNGP10.phx.gbl...
> 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/defaul...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/defaul...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 w
, 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...
>|||> You rock!
In this case, I'd say that it is David who rock. :-)
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:%23kLhBsQGFHA.3376@.TK2MSFTNGP12.phx.gbl...
> You rock!
> Cheers, Jon
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23yum2SQGFHA.1140@.TK2MSFTNGP10.phx.gbl...
>sql
