Using web services inside TSQL

[SriSamp]

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" <dafi@gmx.de> wrote in message
news:0d6901c346d0$3b9f3fa0$a101280a@phx.gbl...

> How can I use a web service within a stored
> procedure (MS SQL Server 2000, TSQL)?
>
> Thanks in advance.
> Daniel
>

[Tibor Karaszi]

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" <ssampath@sct.co.in> wrote in message news:uvYHsZtRDHA.2852@tk2msftngp13.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" <dafi@gmx.de> wrote in message
> news:0d6901c346d0$3b9f3fa0$a101280a@phx.gbl...

> > How can I use a web service within a stored
> > procedure (MS SQL Server 2000, TSQL)?
> >
> > Thanks in advance.
> > Daniel
> >

>
>

[David Browne]

"SriSamp" <ssampath@sct.co.in> wrote in message
news:uvYHsZtRDHA.2852@tk2msftngp13.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 parser 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]

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]meat@hotmail.com[/email]> wrote in
message news:eKfxv7tRDHA.1556@TK2MSFTNGP11.phx.gbl...

>
> "SriSamp" <ssampath@sct.co.in> wrote in message
> news:uvYHsZtRDHA.2852@tk2msftngp13.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 parser 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
>
>