SQL Programming: Calling the web service with parameterized query string (using OLE automation)

 

Say, we have a webservice which sends SMS, which should be called by passing the needed parameters thru SQL query. 

 

We can get use of OLE Automation Stored Procedures within SQL Server.

List of Stored Procedure we can use to achieve this:
sp_OACreate - Creates an instance of an OLE object

sp_OAMethod
- Calls a method of an OLE object.

sp_OADestroy
- Destroys a created OLE object
sp_OASetProperty
- Sets a property of an OLE object to a new value
sp_OAGetProperty
- Gets a property value of an OLE object.

sp_OAStop
- Stops the server-wide OLE Automation stored procedure execution environment

 

These stored procedures were disabled by default, and should be initiated manually from surface area configuration tool (Refer MSDN).administrator privileges were required to execute this stored procedures

 

In SQL 2008,there is no surface area configuration tool. It is integrated within the Facets option.(refer mssqltips)

 

Set these options to True for better execution.

• ClrIntegrationEnabled
• OleAutomationEnabled
• XPCmdShellEnabled

 
Sample Stored Procedure Query:

 
declare @sUrl varchar(4000),@param1 varchar(100),@param2 varchar(100),
@obj int,@response varchar(800),@MobileNo varchar(50),@MovieName varchar(50), @SmsId int

 
select @RowCount =
count(*)
from tbl_SMSAlertMovie where status is null
while (@RowCount > 0)
begin

select
top 1 @SmsId =SmsId, MobileNo=MobileNo,@MovieName=MovieName from tbl_SMSAlertMovie where status is null

 

set @sUrl =
'http://192.168.17.17:8080/webservice.aspx?'

set @param1='&MobileNo='+@MobileNo

set @param2='&Movie='+@MovieName

set @sUrl= @sUrl+@param1+@param2

    exec
sp_OACreate
'WinHttp.WinHttpRequest.5.1', @obj OUT

    exec
sp_OAMethod
@obj,
'Open',
NULL,
'GET', @sUrl, false

    exec
sp_OAMethod
@obj,
'send'

    exec
sp_OAGetProperty
@obj,
'responseText', @response OUT

    exec
sp_OADestroy
@obj

--    select @response [response] à use if you need to store result

 

update dbo.tbl_ SMSAlertMovie

set Status='Y' where SmsId=@SmsId

 

select @RowCount =
count(*)from tbl_SMSAlertMovie where status is null

end

 
EnJoY!!!!...

CoOoOlLlL.....