how to send query from SQL Server using post method

Hello Everyone
i am new to SQL Server. i have a problem. i have to send a query string
to a server using post method from a stored procedure.
i got a stored procedure in some group for sending Http Post Request
from Stored Procedure. but i dont know how to pass parameters and Http
Header Information.
i have to send 4 parameters (Control name are)
1. Name (character string)
2. msisdn (Numeric string like Mobile No.)
3. toAddress (Numaric String like Mobile No.)
4. message (character string)
Plz can anyone help me.
the HTTP_post stored procedure which i found at some group is as
follows:
****************************************
**************************
CREATE procedure HTTP_POST( @.sUrl varchar(200), @.response varchar(8000)
out)
As
Declare
@.obj int
,@.hr int
,@.status int
,@.msg varchar(255)
exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @.obj OUT
if @.hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
failed', 16,1) return end
exec @.hr = sp_OAMethod @.obj, 'open', NULL, 'POST', @.sUrl, false
if @.hr <>0 begin set @.msg = 'sp_OAMethod Open failed' goto eh end
exec @.hr = sp_OAMethod @.obj, 'setRequestHeader', NULL,
'Content-Type', 'application/x-www-form-urlencoded'
if @.hr <>0 begin set @.msg = 'sp_OAMethod setRequestHeader failed'
goto eh end
exec @.hr = sp_OAMethod @.obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
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
****************************************
**************************Hi,
I think you need to modify this bit to add the parameters in...
exec @.hr = sp_OAMethod @.obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
change to...
declare @.parms varchar(500)
set @.parms = 'Var1=' + @.yourparm + '&Var2=' + @.yourparm2
exec @.hr = sp_OAMethod @.obj, 'send', NULL, @.parms
if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<vineet.jsl@.gmail.com> wrote in message
news:1130749924.513462.309240@.f14g2000cwb.googlegroups.com...
> Hello Everyone
> i am new to SQL Server. i have a problem. i have to send a query string
> to a server using post method from a stored procedure.
> i got a stored procedure in some group for sending Http Post Request
> from Stored Procedure. but i dont know how to pass parameters and Http
> Header Information.
> i have to send 4 parameters (Control name are)
> 1. Name (character string)
> 2. msisdn (Numeric string like Mobile No.)
> 3. toAddress (Numaric String like Mobile No.)
> 4. message (character string)
> Plz can anyone help me.
> the HTTP_post stored procedure which i found at some group is as
> follows:
> ****************************************
**************************
> CREATE procedure HTTP_POST( @.sUrl varchar(200), @.response varchar(8000)
> out)
> As
>
> Declare
> @.obj int
> ,@.hr int
> ,@.status int
> ,@.msg varchar(255)
>
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @.obj OUT
> if @.hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
> failed', 16,1) return end
>
> exec @.hr = sp_OAMethod @.obj, 'open', NULL, 'POST', @.sUrl, false
> if @.hr <>0 begin set @.msg = 'sp_OAMethod Open failed' goto eh end
>
> exec @.hr = sp_OAMethod @.obj, 'setRequestHeader', NULL,
> 'Content-Type', 'application/x-www-form-urlencoded'
> if @.hr <>0 begin set @.msg = 'sp_OAMethod setRequestHeader failed'
> goto eh end
>
> exec @.hr = sp_OAMethod @.obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
> 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
> ****************************************
**************************
>|||First, very very thank you for replying and giving me suggestion.
second, again i have another problem just like the first one.
now i have to send http post request to an URL but this time i have to
send data in XML Format
the data is
<ERECHARGE>
<TRANS_ID>Vendor's Transaction ID </TRANS_ID>
<SOURCE_ID> Vendor's Name </SOURCE_ID>
<MDN> Vendor's MDN </MDN>
<DEALER_ID> Vendor's Dealer ID </DEALER_ID>
<REQUEST>
<REQUEST_TYPE>ERECHARGE</REQUEST_TYPE>
<ATTRIBUTES>
<ATTRIBUTE>
<NAME>TARGET_MDN</NAME>
<VALUE>MDN where recharge amount to be transferred </VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>AMOUNT</NAME>
<VALUE>Recharge Amount</VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>PIN</NAME>
<VALUE>Unique PIN given to distributor on registration</VALUE>
</ATTRIBUTE>
</ATTRIBUTES>
</REQUEST>
</ERECHARGE>
i dont know what approach i shud take.
Any help will be appreciate|||First, very very thank you for replying and giving me suggestion.
second, again i have another problem just like the first one.
now i have to send http post request to an URL from stored procedure
but this time i have to send data in XML Format
the data is
<ERECHARGE>
<TRANS_ID>Vendor's Transaction ID </TRANS_ID>
<SOURCE_ID> Vendor's Name </SOURCE_ID>
<MDN> Vendor's MDN </MDN>
<DEALER_ID> Vendor's Dealer ID </DEALER_ID>
<REQUEST>
<REQUEST_TYPE>ERECHARGE</REQUEST_TYPE>
<ATTRIBUTES>
<ATTRIBUTE>
<NAME>TARGET_MDN</NAME>
<VALUE>MDN where recharge amount to be transferred </VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>AMOUNT</NAME>
<VALUE>Recharge Amount</VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>PIN</NAME>
<VALUE>Unique PIN given to distributor on registration</VALUE>
</ATTRIBUTE>
</ATTRIBUTES>
</REQUEST>
</ERECHARGE>
i dont know what approach i shud take.
Any help will be appreciate