Professional Web Applications Themes

Using web services inside TSQL - Microsoft SQL / MS SQL Server

If you want to call a web service from SQL Server, you need to use the sp_OA* procedures to call the "interop assemblies" for the .NET web service. But remember that this is **never** recommended, since you might have transaction issues and the procedure might wait indefinitely for something to happen. Better to call the web service from a client application. -- HTH, SriSamp Please reply to the whole group only! [url]http://www32.brinkster.com/srisamp[/url] "DaFI" <dafigmx.de> wrote in message news:0d6901c346d0$3b9f3fa0$a101280aphx.gbl... > How can I use a web service within a stored > procedure (MS SQL Server 2000, TSQL)? > > Thanks in ...

  1. #1

    Default Re: Using web services inside TSQL

    If you want to call a web service from SQL Server, you need to use the
    sp_OA* procedures to call the "interop assemblies" for the .NET web service.
    But remember that this is **never** recommended, since you might have
    transaction issues and the procedure might wait indefinitely for something
    to happen. Better to call the web service from a client application.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    [url]http://www32.brinkster.com/srisamp[/url]

    "DaFI" <dafigmx.de> wrote in message
    news:0d6901c346d0$3b9f3fa0$a101280aphx.gbl...
    > How can I use a web service within a stored
    > procedure (MS SQL Server 2000, TSQL)?
    >
    > Thanks in advance.
    > Daniel
    >

    SriSamp Guest

  2. #2

    Default Re: Using web services inside TSQL

    Here's the KB describing why it isn't supported or recommended to call CLR code from SQL Server:

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


    --
    Tibor Karaszi, SQL Server MVP
    Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


    "SriSamp" <ssampathsct.co.in> wrote in message news:uvYHsZtRDHA.2852tk2msftngp13.phx.gbl...
    > If you want to call a web service from SQL Server, you need to use the
    > sp_OA* procedures to call the "interop assemblies" for the .NET web service.
    > But remember that this is **never** recommended, since you might have
    > transaction issues and the procedure might wait indefinitely for something
    > to happen. Better to call the web service from a client application.
    > --
    > HTH,
    > SriSamp
    > Please reply to the whole group only!
    > [url]http://www32.brinkster.com/srisamp[/url]
    >
    > "DaFI" <dafigmx.de> wrote in message
    > news:0d6901c346d0$3b9f3fa0$a101280aphx.gbl...
    > > How can I use a web service within a stored
    > > procedure (MS SQL Server 2000, TSQL)?
    > >
    > > Thanks in advance.
    > > Daniel
    > >
    >
    >

    Tibor Karaszi Guest

  3. #3

    Default Re: Using web services inside TSQL


    "SriSamp" <ssampathsct.co.in> wrote in message
    news:uvYHsZtRDHA.2852tk2msftngp13.phx.gbl...
    > If you want to call a web service from SQL Server, you need to use the
    > sp_OA* procedures to call the "interop assemblies" for the .NET web
    service.
    > But remember that this is **never** recommended, since you might have
    > transaction issues and the procedure might wait indefinitely for something
    > to happen. Better to call the web service from a client application.
    > --
    No this method is not supported. You cannot invoke dotnet code directly
    through COM interop and sp_OA*. See:

    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]

    If you want to hit a webservice, you should use the sp_oa* stored procedures
    to invoke MSXML2.XMLServerHTTP, and communicate with the webservice that
    way.

    Here's an example:

    David




    In VB implement a web service called HelloWorld as follows

    Public Class HelloWorld
    Inherits System.Web.Services.WebService

    Public Sub New()
    MyBase.New()
    End Sub

    <WebMethod()> Public Function Hello(ByVal Name As String) As String
    Return "Hello " & Name
    End Function


    End Class

    Make sure MSXML pr version 3 or greater is installed on your SQLServer
    server (it's installed with IE6 so if you have that, you're good to go).

    In sql create the following stored procedure.

    create procedure http_get( sUrl varchar(200), response varchar(8000) out)
    As

    Declare
    obj int
    ,hr int
    ,status int
    ,msg varchar(255)


    exec hr = sp_OACreate 'MSXML2.ServerXMLHttp', obj OUT
    if hr < 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp failed', 16,
    1) return end

    exec hr = sp_OAMethod obj, 'Open', NULL, 'GET', sUrl, false
    if hr <0 begin set msg = 'sp_OAMethod Open failed' goto eh end

    exec hr = sp_OAMethod obj, 'send'
    if hr <0 begin set msg = 'sp_OAMethod Send failed' goto eh end


    exec hr = sp_OAGetProperty obj, 'status', status OUT
    if hr <0 begin set msg = 'sp_OAMethod read status failed' goto eh end

    if status <> 200 begin set msg = 'sp_OAMethod http status ' +
    str(status) goto eh end

    exec hr = sp_OAGetProperty obj, 'responseText', response OUT
    if hr <0 begin set msg = 'sp_OAMethod read response failed' goto eh
    end

    exec hr = sp_OADestroy obj

    return
    eh:
    exec hr = sp_OADestroy obj
    Raiserror(msg, 16, 1)
    return



    go

    then run this


    declare resp varchar(8000)
    exec http_get
    'http://[webserver]/[virtualDirectory]/HelloWorld.asmx/Hello?Name=david',
    resp out

    print resp


    David Browne Guest

  4. #4

    Default Re: Using web services inside TSQL

    Sorry for the wrong post. I remember an earlier post along the same lines
    that recommended COM wrapper at:
    [url]http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=eQSaFSNNDHA.452%40TK2MSFTNGP11.phx.gbl&r num=60&prev=/groups%3Fq%3DSriSamp%26start%3D50%26hl%3Den%26lr%3 D%26ie%3DUTF-8%26oe%3DUTF-8%26scoring%3Dd%26selm%3DeQSaFSNNDHA.452%2540TK2MS FTNGP11.phx.gbl%26rnum%3D60[/url]

    In this post Greg suggests an alternate method, which is useful too.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    [url]http://www32.brinkster.com/srisamp[/url]

    "David Browne" <davidbaxterbrowne no potted [email]meathotmail.com[/email]> wrote in
    message news:eKfxv7tRDHA.1556TK2MSFTNGP11.phx.gbl...
    >
    > "SriSamp" <ssampathsct.co.in> wrote in message
    > news:uvYHsZtRDHA.2852tk2msftngp13.phx.gbl...
    > > If you want to call a web service from SQL Server, you need to use the
    > > sp_OA* procedures to call the "interop assemblies" for the .NET web
    > service.
    > > But remember that this is **never** recommended, since you might have
    > > transaction issues and the procedure might wait indefinitely for
    something
    > > to happen. Better to call the web service from a client application.
    > > --
    >
    > No this method is not supported. You cannot invoke dotnet code directly
    > through COM interop and sp_OA*. See:
    >
    > 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]
    >
    > If you want to hit a webservice, you should use the sp_oa* stored
    procedures
    > to invoke MSXML2.XMLServerHTTP, and communicate with the webservice that
    > way.
    >
    > Here's an example:
    >
    > David
    >
    >
    >
    >
    > In VB implement a web service called HelloWorld as follows
    >
    > Public Class HelloWorld
    > Inherits System.Web.Services.WebService
    >
    > Public Sub New()
    > MyBase.New()
    > End Sub
    >
    > <WebMethod()> Public Function Hello(ByVal Name As String) As String
    > Return "Hello " & Name
    > End Function
    >
    >
    > End Class
    >
    > Make sure MSXML pr version 3 or greater is installed on your SQLServer
    > server (it's installed with IE6 so if you have that, you're good to go).
    >
    > In sql create the following stored procedure.
    >
    > create procedure http_get( sUrl varchar(200), response varchar(8000)
    out)
    > As
    >
    > Declare
    > obj int
    > ,hr int
    > ,status int
    > ,msg varchar(255)
    >
    >
    > exec hr = sp_OACreate 'MSXML2.ServerXMLHttp', obj OUT
    > if hr < 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp failed',
    16,
    > 1) return end
    >
    > exec hr = sp_OAMethod obj, 'Open', NULL, 'GET', sUrl, false
    > if hr <0 begin set msg = 'sp_OAMethod Open failed' goto eh end
    >
    > exec hr = sp_OAMethod obj, 'send'
    > if hr <0 begin set msg = 'sp_OAMethod Send failed' goto eh end
    >
    >
    > exec hr = sp_OAGetProperty obj, 'status', status OUT
    > if hr <0 begin set msg = 'sp_OAMethod read status failed' goto eh
    end
    >
    > if status <> 200 begin set msg = 'sp_OAMethod http status ' +
    > str(status) goto eh end
    >
    > exec hr = sp_OAGetProperty obj, 'responseText', response OUT
    > if hr <0 begin set msg = 'sp_OAMethod read response failed' goto eh
    > end
    >
    > exec hr = sp_OADestroy obj
    >
    > return
    > eh:
    > exec hr = sp_OADestroy obj
    > Raiserror(msg, 16, 1)
    > return
    >
    >
    >
    > go
    >
    > then run this
    >
    >
    > declare resp varchar(8000)
    > exec http_get
    >
    'http://[webserver]/[virtualDirectory]/HelloWorld.asmx/Hello?Name=david',
    > resp out
    >
    > print resp
    >
    >

    SriSamp Guest

  5. #5

    Default Re: Using web services inside TSQL

    Hi ll,

    thanks for your help. It works great with serverside executed script.
    But now another question to the same problem.
    Has anybody a sample that uses SOAP as transport, because
    i want to exchange complex datastructures between the SQL-server and the
    web-server.


    Thanks in advance.

    Kind regards,

    Daniel



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    DaFi Guest

  6. #6

    Default Re: Using web services inside TSQL

    Try using SQLXML, since it allows you to expose SQL Server stored procedures
    and functions as web services (which use SOAP).
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "DaFi gmx.de>" <dafi<NO_SPAM> wrote in message
    news:un$phx.gbl... 


    SriSamp Guest

Similar Threads

  1. How you convert this cfquery to TSQL?
    By roofusthedoofus in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: February 23rd, 01:25 PM
  2. How u convert this cfquery to TSQL?
    By roofusthedoofus in forum Coldfusion Database Access
    Replies: 2
    Last Post: February 22nd, 09:13 PM
  3. Is it possible to do this using TSQL instead of cursor ??
    By Ricky in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 05:50 PM
  4. IIF/Format equivalent in TSQL
    By andi in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 1st, 05:11 PM
  5. Please improve this TSQL algorithm ..
    By Krist Lioe in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 08:18 AM

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