Professional Web Applications Themes

How to call routines in .NET dll from sql server? - Microsoft SQL / MS SQL Server

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 ...

  1. #1

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

    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 Guest

  2. #2

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

    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 Doentation Builder
    Doent 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" <wyx1999hotmail.com> wrote in message
    news:%23xtv%23NOQDHA.2212TK2MSFTNGP12.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.
    >
    >

    Greg Obleshchuk Guest

  3. #3

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

    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" <gregag-software.com> wrote in message
    news:OHh7oMPQDHA.1560TK2MSFTNGP12.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 Doentation Builder
    > Doent 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" <wyx1999hotmail.com> wrote in message
    > news:%23xtv%23NOQDHA.2212TK2MSFTNGP12.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 Guest

  4. #4

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

    > do you have any idea why it causes sql
    > crash?
    You didn't read the article, did you? :-)

    --
    Michael Culley


    Michael Culley Guest

  5. #5

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

    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" <gregag-software.com> wrote in message
    >news:OHh7oMPQDHA.1560TK2MSFTNGP12.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 Doentation Builder
    >> Doent 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" <wyx1999hotmail.com> wrote in message
    >> news:%23xtv%23NOQDHA.2212TK2MSFTNGP12.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.
    >> >
    >> >
    >>
    >>
    >
    >
    >.
    >
    Greg Linwood Guest

  6. #6

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

    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_linwoodhotmail.com> wrote in message
    news:074701c3410d$c741bba0$a301280aphx.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" <gregag-software.com> wrote in message
    > >news:OHh7oMPQDHA.1560TK2MSFTNGP12.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 Doentation Builder
    > >> Doent 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" <wyx1999hotmail.com> wrote in message
    > >> news:%23xtv%23NOQDHA.2212TK2MSFTNGP12.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 Guest

  7. #7

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

    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.
    Ben Peterson Guest

Similar Threads

  1. call server script
    By asu031 in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: March 1st, 04:24 PM
  2. Is it possible to call Java routines from FMS?
    By DanielKozimor in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: March 3rd, 04:14 PM
  3. Server.Execute call not executing
    By Brian Burgess in forum ASP
    Replies: 7
    Last Post: September 9th, 07:27 AM
  4. Internal Server Error (500) with web service call
    By JC in forum ASP.NET Web Services
    Replies: 0
    Last Post: August 20th, 02:16 PM
  5. Net::Server and system call
    By David in forum PERL Modules
    Replies: 0
    Last Post: August 15th, 05:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139