how to call webservice from tsql?

Hello!

From an stored proc I want to call an existing web-service.

How to do that?

Must it be done using CLR-procs or is there some way in TSQL?

Greetings

Bjorn

Are you really need this...

CLR is one of the best way to invoke your web service. But i never tested it.

If you use sql server 2000 then the MSXML2.XMLHTTP object & sp_OACreate sp is one of the solution for this requirement.

|||

Here the sample code,

Declare @.Object as Int;

Declare @.ResponseText as Varchar(8000);

Code Snippet

Exec sp_OACreate 'MSXML2.XMLHTTP', @.Object OUT;

Exec sp_OAMethod @.Object, 'open', NULL, 'get',

'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', --Your Web Service Url (invoked)

'false'

Exec sp_OAMethod @.Object, 'send'

Exec sp_OAMethod @.Object, 'responseText', @.ResponseText OUTPUT

Select @.ResponseText

Exec sp_OADestroy @.Object

|||

Hi!

Sorry not writing, that I'm using SS2005...

Makes this some difference?

/Bjorn

|||

The Query (given below) can be used in both versions...

For SQL Server 2005 you have to enable the "OLE Automation" option.

Start-> Programs - > Microsoft SQL Server 2005 -> Configuration Tools ->SQL Server Surface Area Configuration

Click the link Surface Area Configuration for Features

Expand the DB -> Database Engine

Select OLE Automation & Check the checkbox Enable OLE Automation..

After the settings completed execute the code...

|||

Hi!

I have to use post, but how do I get it to work?

I was told I should look at this:

POST /xyzproxy/alarminsert.asmx HTTP/1.1
Host: 192.168.1.111
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://tempuri.org/TestWebService"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<TestWebService xmlns="http://tempuri.org/" />
</soap:Body>
</soap:Envelope>

How do I send a request like that using TSQL?

/Bjorn