How to call routines in .NET dll from sql server?

[davidw]

In our application, we need read xml from our SPs, now we are using


EXEC @resultcode = sp_OACreate 'MSXML2.XMLHTTP', @objRef OUT
....

to access the remote url, it worked fine but recently it always causes my
sql server crash, and message is

SQL Server is aborting. Fatal exception c0000005 caught.

someone said it is better to use MSXML2.ServerXMLHTTP, I tried that but
could not get result from remote site, any idea on this?

I am thinking if there is a easier way to access .NET dll from SQL, so that
I can put my logic into my .NET dll, actual I know there is a way, I
remember someone said I can wrap .NET dll into a COM, and then use
sp_oacreate to call it. I am looking for a way to make it a extended stored
procedure, but I am not sure if it is the right way, I mean even the sql
embed extended stored procedure causes it crash so often, a user build
extended stored procedure may be worse. Any comment?


Thanks.

[Greg Obleshchuk]

David,
Any .NET assembly called from SQL Server is not a supported process. You
should not do this as it can create many more stability problems in SQL..
Please see this reference
Thanks for Gert in posting it
INF: Using Extended Stored Procedures or SP_OA Stored Procedures to Load CLR
in SQL Server Is Not Supported
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;322884[/url]


--
I hope this helps
regards
Greg O MCSD
SQL Scribe Documentation Builder
Document any SQL server database in minutes
Programmers love it, DBA dream of it
AGS SQL Scribe download a 30 day trial today
[url]http://www.ag-software.com/ags_scribe_index.asp[/url]

"davidw" <wyx1999@hotmail.com> wrote in message
news:%23xtv%23NOQDHA.2212@TK2MSFTNGP12.phx.gbl...

> In our application, we need read xml from our SPs, now we are using
>
>
> EXEC @resultcode = sp_OACreate 'MSXML2.XMLHTTP', @objRef OUT
> ....
>
> to access the remote url, it worked fine but recently it always causes my
> sql server crash, and message is
>
> SQL Server is aborting. Fatal exception c0000005 caught.
>
> someone said it is better to use MSXML2.ServerXMLHTTP, I tried that but
> could not get result from remote site, any idea on this?
>
> I am thinking if there is a easier way to access .NET dll from SQL, so

that

> I can put my logic into my .NET dll, actual I know there is a way, I
> remember someone said I can wrap .NET dll into a COM, and then use
> sp_oacreate to call it. I am looking for a way to make it a extended

stored

> procedure, but I am not sure if it is the right way, I mean even the sql
> embed extended stored procedure causes it crash so often, a user build
> extended stored procedure may be worse. Any comment?
>
>
> Thanks.
>
>

[davidw]

Really thanks!

You see, my question came out because the problem I had when uses xmlhttp to
retrieve remote web page contentn, do you have any idea why it causes sql
crash?




"Greg Obleshchuk" <greg@ag-software.com> wrote in message
news:OHh7oMPQDHA.1560@TK2MSFTNGP12.phx.gbl...

> David,
> Any .NET assembly called from SQL Server is not a supported process. You
> should not do this as it can create many more stability problems in SQL..
> Please see this reference
> Thanks for Gert in posting it
> INF: Using Extended Stored Procedures or SP_OA Stored Procedures to Load

CLR

> in SQL Server Is Not Supported
> [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;322884[/url]
>
>
> --
> I hope this helps
> regards
> Greg O MCSD
> SQL Scribe Documentation Builder
> Document any SQL server database in minutes
> Programmers love it, DBA dream of it
> AGS SQL Scribe download a 30 day trial today
> [url]http://www.ag-software.com/ags_scribe_index.asp[/url]
>
> "davidw" <wyx1999@hotmail.com> wrote in message
> news:%23xtv%23NOQDHA.2212@TK2MSFTNGP12.phx.gbl...

> > In our application, we need read xml from our SPs, now we are using
> >
> >
> > EXEC @resultcode = sp_OACreate 'MSXML2.XMLHTTP', @objRef OUT
> > ....
> >
> > to access the remote url, it worked fine but recently it always causes

my

> > sql server crash, and message is
> >
> > SQL Server is aborting. Fatal exception c0000005 caught.
> >
> > someone said it is better to use MSXML2.ServerXMLHTTP, I tried that but
> > could not get result from remote site, any idea on this?
> >
> > I am thinking if there is a easier way to access .NET dll from SQL, so

> that

> > I can put my logic into my .NET dll, actual I know there is a way, I
> > remember someone said I can wrap .NET dll into a COM, and then use
> > sp_oacreate to call it. I am looking for a way to make it a extended

> stored

> > procedure, but I am not sure if it is the right way, I mean even the sql
> > embed extended stored procedure causes it crash so often, a user build
> > extended stored procedure may be worse. Any comment?
> >
> >
> > Thanks.
> >
> >

>
>

[Michael Culley]

> do you have any idea why it causes sql

> crash?

You didn't read the article, did you? :-)

--
Michael Culley

[Greg Linwood]

Hi David.

The article makes the point fairly clear that sp_OACreate
only speaks COM & therefore can't even begin to DIRECTLY
interop with .Net .dlls (let alone TLS / fibre issues etc)
but this doesn't mean that it can't be done with a
suitable architecture / design pattern.

All that's really needed is a layer of indirection between
COM / .Net. eg - design your COM server (eg VB
ActiveX.exe / ATL) to do whatever it needs to to consume
the .Net component (various options) and than call the COM
server only from sp_OACreate.

As far as your origional qn re ServerHTTP is concerned,
there are various causes of your connectivity / no
response problem. One very common cause being that the
Windows account that the SQL Server service is configured
to execute within might not actually have permissions to
make any network calls at all (eg its running under
LocalSystem). Are you checking the sp_OAGetErrorInfo after
the sp_OACreate call? Is anything written in the Windows
Event Log or the SQL Server Error Logs?

You might also need to configure proxycfg to get
ServerHTTP running properly in your environment. How you
go about this will depend on various requirements (is the
target http dir a local / remote site, are you using
kerberos, do you need delegation etc). There's an article
on this which can be found at:
[url]http://support.microsoft.com/default.aspx?scid=kb;EN-[/url]
US;289481

Plese post back here if you've got more qns / need any
more help.

HTH

Regards,
Greg Linwood
SQL Server MVP

>-----Original Message-----
>Really thanks!
>
>You see, my question came out because the problem I had

when uses xmlhttp to

>retrieve remote web page contentn, do you have any idea

why it causes sql

>crash?
>
>
>
>
>"Greg Obleshchuk" <greg@ag-software.com> wrote in message
>news:OHh7oMPQDHA.1560@TK2MSFTNGP12.phx.gbl...

>> David,
>> Any .NET assembly called from SQL Server is not a

supported process. You

>> should not do this as it can create many more stability

problems in SQL..

>> Please see this reference
>> Thanks for Gert in posting it
>> INF: Using Extended Stored Procedures or SP_OA Stored

Procedures to Load

>CLR

>> in SQL Server Is Not Supported
>> [url]http://support.microsoft.com/default.aspx?scid=kb;en-[/url]

us;322884

>>
>>
>> --
>> I hope this helps
>> regards
>> Greg O MCSD
>> SQL Scribe Documentation Builder
>> Document any SQL server database in minutes
>> Programmers love it, DBA dream of it
>> AGS SQL Scribe download a 30 day trial today
>> [url]http://www.ag-software.com/ags_scribe_index.asp[/url]
>>
>> "davidw" <wyx1999@hotmail.com> wrote in message
>> news:%23xtv%23NOQDHA.2212@TK2MSFTNGP12.phx.gbl...

>> > In our application, we need read xml from our SPs,

now we are using

>> >
>> >
>> > EXEC @resultcode = sp_OACreate 'MSXML2.XMLHTTP',

@objRef OUT

>> > ....
>> >
>> > to access the remote url, it worked fine but recently

it always causes

>my

>> > sql server crash, and message is
>> >
>> > SQL Server is aborting. Fatal exception c0000005

caught.

>> >
>> > someone said it is better to use

MSXML2.ServerXMLHTTP, I tried that but

>> > could not get result from remote site, any idea on

this?

>> >
>> > I am thinking if there is a easier way to access .NET

dll from SQL, so

>> that

>> > I can put my logic into my .NET dll, actual I know

there is a way, I

>> > remember someone said I can wrap .NET dll into a COM,

and then use

>> > sp_oacreate to call it. I am looking for a way to

make it a extended

>> stored

>> > procedure, but I am not sure if it is the right way,

I mean even the sql

>> > embed extended stored procedure causes it crash so

often, a user build

>> > extended stored procedure may be worse. Any comment?
>> >
>> >
>> > Thanks.
>> >
>> >

>>
>>

>
>
>.
>

[davidw]

Thank you a lot, that is a very detail response.

As david browne said in another reply, my queston isn;t about calling CLR
from SQL, I just said if it works I can go that way, and I know it doesn;t
work now.

So, my SQL is calling msxml2.xmlhttp, a COM now, it worked fine for more
than one year, but it keeps on crash my SQL recently, so I searched for
answer, someone said (david browne said too) xmlhttp isn;t safe in SQL, I
should use MSXML2.ServerXMLHTTP, but with same code xmlhttp works, but
ServerXMLHTTP doesn;t, I think that is what I try to find out., the code is
this:

declare @objRef int --Object reference
declare @resultcode int

EXEC @resultcode = sp_OACreate 'MSXML2.ServerXMLHTTP', @objRef OUT,4

IF @resultcode = 0
BEGIN
EXEC @resultcode = sp_OAMethod @objRef, 'Open', NULL,@GetMethod,
@GetURL, False
EXEC @resultcode = sp_OAMethod @objRef, 'Send',null
EXEC @resultcode = sp_OAGetProperty @objRef,
'responseText',@responseText output
END

EXEC sp_OADestroy @objRef


Thanks.




"Greg Linwood" <g_linwood@hotmail.com> wrote in message
news:074701c3410d$c741bba0$a301280a@phx.gbl...

> Hi David.
>
> The article makes the point fairly clear that sp_OACreate
> only speaks COM & therefore can't even begin to DIRECTLY
> interop with .Net .dlls (let alone TLS / fibre issues etc)
> but this doesn't mean that it can't be done with a
> suitable architecture / design pattern.
>
> All that's really needed is a layer of indirection between
> COM / .Net. eg - design your COM server (eg VB
> ActiveX.exe / ATL) to do whatever it needs to to consume
> the .Net component (various options) and than call the COM
> server only from sp_OACreate.
>
> As far as your origional qn re ServerHTTP is concerned,
> there are various causes of your connectivity / no
> response problem. One very common cause being that the
> Windows account that the SQL Server service is configured
> to execute within might not actually have permissions to
> make any network calls at all (eg its running under
> LocalSystem). Are you checking the sp_OAGetErrorInfo after
> the sp_OACreate call? Is anything written in the Windows
> Event Log or the SQL Server Error Logs?
>
> You might also need to configure proxycfg to get
> ServerHTTP running properly in your environment. How you
> go about this will depend on various requirements (is the
> target http dir a local / remote site, are you using
> kerberos, do you need delegation etc). There's an article
> on this which can be found at:
> [url]http://support.microsoft.com/default.aspx?scid=kb;EN-[/url]
> US;289481
>
> Plese post back here if you've got more qns / need any
> more help.
>
> HTH
>
> Regards,
> Greg Linwood
> SQL Server MVP
>

> >-----Original Message-----
> >Really thanks!
> >
> >You see, my question came out because the problem I had

> when uses xmlhttp to

> >retrieve remote web page contentn, do you have any idea

> why it causes sql

> >crash?
> >
> >
> >
> >
> >"Greg Obleshchuk" <greg@ag-software.com> wrote in message
> >news:OHh7oMPQDHA.1560@TK2MSFTNGP12.phx.gbl...

> >> David,
> >> Any .NET assembly called from SQL Server is not a

> supported process. You

> >> should not do this as it can create many more stability

> problems in SQL..

> >> Please see this reference
> >> Thanks for Gert in posting it
> >> INF: Using Extended Stored Procedures or SP_OA Stored

> Procedures to Load

> >CLR

> >> in SQL Server Is Not Supported
> >> [url]http://support.microsoft.com/default.aspx?scid=kb;en-[/url]

> us;322884

> >>
> >>
> >> --
> >> I hope this helps
> >> regards
> >> Greg O MCSD
> >> SQL Scribe Documentation Builder
> >> Document any SQL server database in minutes
> >> Programmers love it, DBA dream of it
> >> AGS SQL Scribe download a 30 day trial today
> >> [url]http://www.ag-software.com/ags_scribe_index.asp[/url]
> >>
> >> "davidw" <wyx1999@hotmail.com> wrote in message
> >> news:%23xtv%23NOQDHA.2212@TK2MSFTNGP12.phx.gbl...
> >> > In our application, we need read xml from our SPs,

> now we are using

> >> >
> >> >
> >> > EXEC @resultcode = sp_OACreate 'MSXML2.XMLHTTP',

> @objRef OUT

> >> > ....
> >> >
> >> > to access the remote url, it worked fine but recently

> it always causes

> >my

> >> > sql server crash, and message is
> >> >
> >> > SQL Server is aborting. Fatal exception c0000005

> caught.

> >> >
> >> > someone said it is better to use

> MSXML2.ServerXMLHTTP, I tried that but

> >> > could not get result from remote site, any idea on

> this?

> >> >
> >> > I am thinking if there is a easier way to access .NET

> dll from SQL, so

> >> that
> >> > I can put my logic into my .NET dll, actual I know

> there is a way, I

> >> > remember someone said I can wrap .NET dll into a COM,

> and then use

> >> > sp_oacreate to call it. I am looking for a way to

> make it a extended

> >> stored
> >> > procedure, but I am not sure if it is the right way,

> I mean even the sql

> >> > embed extended stored procedure causes it crash so

> often, a user build

> >> > extended stored procedure may be worse. Any comment?
> >> >
> >> >
> >> > Thanks.
> >> >
> >> >
> >>
> >>

> >
> >
> >.
> >

[Ben Peterson]

I highly recommend exploring the CLR integration features built into SQL server.

I've used it in the past to add regular expression support to SQL via C#, and it was powerful and fast, even under heavy load.

Inside the CLR assembly that's installed on your server, you can wrap the calls to your service and add plenty of exception handling to make sure it behaves.