Scenario: A data change event in a table in a relational database - like sailing of a ship in my case - should trigger the delivery of a SSRS report in Sharepoint integrated mode as an e-mail attachment
to a certain list of recipients.
Start: some t-sql code for the trigger of the table where the data change occurs.
End : data or data-driven subscription to your report.
But how can we link the 'loose ends' together ?
Hope the following guide and pieces of code may solve your problem:
1) The template for your trigger code it's perhaps the most trivial part and may look like :
CREATE TRIGGER <trigger_name>
ON <your table name>
AFTER UPDATE
AS
IF UPDATE(<your column name>)
BEGIN
SET NOCOUNT ON;
DECLARE <variables>
SET <varibles> from Inserted, Deleted , etc.)
< Your way to extract, transform and separate from her the parameters that you will use in the query for your data-driven subscription>
--- code that fires the Manifest subscription.
END
2) Create a shared schedule that will never fire, that is that executes once in the past.
3) Create a subscription to your report. I used a data-driven subscription. The query of the subscription has to provide the values for your report parameters.
Put down your subscription id.
You have to fire now a TimedSubscription event for your Reporting Services instance and you can accomplish it in two different ways :
4) By calling a remote stored procedure on the database server that hosts the ReportServer db :
exec <remote db server>.ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData=<your subscription id from pct. 3>
Remember when configuring the linked server that RPC and RPC out options are set to 'TRUE', your remote login account has permissions to execute the stored procedure from above
and that you solve potential collation conflict in your subscription query.
5) Write a .Net application that calls the FireEvent method on the report server SOAP API. In the FireEvent method set the first variable EventType to “TimedSubscription” and the second one to your Subscription ID.
Your app. may be a console application, so your final trigger statement may be: xp_cmdshell <your exe .net application>.
Make sure you grant the account that runs this process permission to “Generate events” System task.
6) The idea in your .net app. is adding a web reference to the 'right' Reporting Services web service. If SSRS 2008 is in Sharepoint Integrated mode, than your web service url may look like:
http://<web site>/_vti_bin/ReportServer/ReportService2006.asmx
Some 'basic' code to start with in your C# main function :
static void Main(string[] args)
{
<your web service reference>.ReportingService2006 rs = new <your web service reference>.ReportingService2006();
rs.Url = "http://intranet.dk.dfds.root/_vti_bin/ReportServer/ReportService2006.asmx";
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
string site = <your site>;
// Get the subscriptions
<your web reference>.Subscription[] subs = rs.ListMySubscriptions(site);
// rs.ListAllSubscriptions(site);
try
{
if (subs != null)
{
// Fire the first subscription in the list
rs.FireEvent("TimedSubscription",
subs[0].SubscriptionID, site);
Console.WriteLine("Event fired.");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
I implemented the first option, as described at 4).
If you have other alternatives to solve this problem, than do not hesitate to contact me.
