Wednesday, 5 December 2007

Trigger TimedSubscription programmatically

Hello,

Subscriptions are a way to kickstart Reporting Service in delivering a report to somewhere instead of you asking for the report through a URL access request (portal or custom app). This is also callled push versus pull.

You can configere a timed event to trigger off those kind of subscriptions. But in some occasions you want some "custom" event to trigger of a subscription. You can not configure something like that through the configuration tools (reportmanger or SqlServer Management Studio).

Lukasz Pawlowski (http://blogs.msdn.com/lukaszp/archive/2005/10/07/478391.aspx) explained a way to tell Reporting Services to kick of a subscription with the SOAP API of Reporting Services. There is a method called FireEvent that can trigger the subscription. Lukasz suggests creating a dummy schedule that is will never run because it's configured in the passed. The report subscription you want to launch uses this dummy schedule. In a program you call the Firevent method on the reporting WebService .... and your report is delivered as you specifed it in your subscription.

I will show some screenshots how I did it, following Lukasz's tips.
So this is the report I want to be delivered on a file share in PDF.


First I make a dummy shared schedule. But one that will never fire a time event.




Now I create a new subscription on the report I showed before. It will use the shared schedule as Time Event generator.



In order to launch this subscription I must simulate a time event. The SOAP API of Reporting Services just gives me that.


This is a code snippet in VB.NET 2005 of how you could just do that.


It is important that you use the ID (ie. GUID) to specify the subscription. You can also look it up in the Subscription table of the reportserver database.


But before you go off and launch this code one thing is left to be configured (besides the folder share of course) . The account under which this program must execute will need "generate event" privilege on Reporting Service level. Because this is a single machine demo setup (november 07 CTP of Rosario). The user tfssetup is also as system admin on RS. So i just checked the checkbox for generating events on the RS syst. admin role .


The code snippet you just so will actually write an entry in the reportserver.Event table. The RS service will pick it up and act upon it. In our example it will write a pdf file into the file share folder (on the local disk ).



Have fun.
Best regards,
Alexander




7 comments:

simonwxx said...

just few questions regard to your article.

your 2nd, 3th and 4th snapshots, in sql server 2005 how to get into there? and for the VB code, i should create a .dll file with the code in it, and reference it from sql 2005, is that correct?

anowak said...

Hello Simon,

To see the reporting server dialogs , you must connect to the reporting service with the SQL server management Studio. But instead of connecting to a database engine you must explicitely choose the reporting service in server type combobox when you connect.

In order to trigger the event you must create a "consumer" program that will reference to the reporting service web service (for example http://YourRSServer/ReportServer/ReportService.asmx). This can be a winforms programm, an asp.net application, etc. You do this by making a web service reference in your visual studio. This will generate a proxy class to the web service in your project that you can instantiate and use. This code is NOT used from SQLserver 2005. It is not a CLR stored Procedure if that is what you're refering to.

Hope this helps!

Best regards,

Alexander

simonwxx said...

Hi Alex,

Thank you for your reply. Yes, I got the reporting service connect.But have question for "consumer" program.

My project requirement is, once a new batch info recorded into the report database, a batch report should be automatically generated and sent to the stakeholders. So do i have to keep the "consumer" program ON all the time?

Cheers

Regards

Simon

simonwxx said...

Hi Alex,

my client has got MS SQL standard version and Reporting Service 2005 installed on their PC which i can remotely access to develop and run. I've got VS 2003 edition on my pc which i can develop a winform or asp.net "consumer" program, do you think this gonna work?

Thanks a lot!!! :)

Regards

Simon

anowak said...

Hello,

Some ideas you could investigate
Listener : program watching changes on certain tables and notifying interested parties

Report trigger program (ie. interested party) : see example in the post.

You could combine everything into one custom application.

Technologies to investigate
SqlDependency class (ADO.NET 2.0)
SQLServer Notification Services
Biztalk & SQLAdapter

Best regards,

Alexander

lala said...

Thanks for your post and welcome to check: here
.

AnAr Marketing said...

Thanks for sharing it. Anar .NET Solutions