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.....

Displaying Report Generated User at the Report Footer only with User Name

Displaying Report Generating user name without Domian or System name:

Hope we guys know the usual way to display Report Generating User, using the Expression:

="Report Generated by : " & User!UserID

which will display as:

Eg.  Report Generated by : MSSOL\ssrsuser1 

where,

MSSOL is Domain/System Name

ssrsuser1 is Domain User

if we want to display only the User Name excluding Domain name/ System name

The following Expression would really helps:

="Report Generated by : " & User!UserID.Substring(User!UserID.LastIndexOf("\")+1)

which will display as:

Eg. Report Generated by : ssrsuser1

Thats it... no system name/Domain name.. only UserName 

SSRS Expression.... Always Rocks!!!!!...

EnJoY!!!!...

CoOoOlLlL.....

Expand Collapse all columns in SSRS reports by one single option

To expand and collapse all columns in your report by selecting a single option:

here by selecting a value to a parameter 

1. create one Report parameter

 parameter name: showall

which has non-queried values as follows:
label             values
~~~              ~~~~~
Yes               Expanded
No                Collapsed

 Initial value is: 
Non-Queried : Expanded

2. set visibility property of the row select and write:

=iif(Parameters!ShowAll.Label ="No",true,false) 
                    or
=iif(Parameters!ShowAll.Value = "Collapsed", True, False)  

set toggleItem to a textbox (eg: textbox1)

3. set (textbox1) InitialToggleItem property select and write:

=iif(Parameters!ShowAll.Label ="Yes",true,false) 
             or
=iif(Parameters!ShowAll.Value ="Expanded",true,false)  

RESULT:

----------------------------

ShowAll? Yes

----------------------------

ShowAll? No

  Rocks!!!!
now u can make all the report data to expand and collapse in single option...

also refer to my post in msdn forum.

http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/cba00cd6-7c39-4233-a06b-b00ff1dd7672/

EnJoY!!!!...

CoOoOlLlL.....