In my SSRS #42 post, I blogged about how you can kick off a timed subscription at the end of your ETL process.

Before you go off to implement this for your reports, I want to blog an issue with the SQL procedure AddEvent, which I used to trigger the timed subscription in the SSRS report.

Apparently this procedure is one of the many “undocumented” procedures from Microsoft.

As my blogging habit, I’ll copy some of the email communications (revised).

Issue

From: Li, Sherry
Sent: Wednesday, April 06, 2011 7:58 AM
To: ARCHITECTURE
Subject: Link to Timed Subscription Failed

Dear Architecture Team ,

I have a timed subscription on the dev reporting server. Here is the link to the subscription.

http://companyURL/Reports/Pages/Report.aspx?ItemPath=reportPath%2freportName&SelectedTabId=SubscriptionsTab

A link to the report is included in the subscription. However, this link (see below) never worked. Here is the error I got.

Would you help on this?

Thanks,

Response

From: The Architecture Team
Sent: Wednesday, April 13, 2011 11:01 AM
To: Sherry
Subject: RE: Link to Timed Subscription Failed

I looked at the AddEvent SP on MSDN and this came up:

http://social.msdn.microsoft.com/Forums/is/sqlreportingservices/thread/ddc439dc-0129-408f-876e-d0ae5dc86a70

“The stored procs are not designed for use by end user programs. They are not documented or supported. Have you tried using the SOAP endpoint instead?”

Note that the response from Microsoft is to not use those stored procedures. The recommendation is to use the SOAP methods instead. I know SSIS has a web services control to help you. That would be my recommendation.

Thank you,

Architecture Team

Root Problem (my guess so far)

The link in the automatic email looks like this:

http://myreportURL&rs%3aStoredParametersID=wqjmgv45iazjnbj1bsg&rs%3aParameterLanguage=en-US

Apparently, the StoredParameterID is generated by the AddEvent procedure, and a copy of the report with this StoredParameterID cannot be accessed (or never exist, or never saved) on the reporting server.

Recommendation (from the Architect)

From: The Architecture Team
Sent: Wednesday, April 13, 2011 12:11 PM
To: Sherry
Subject: RE: Link to Timed Subscription Failed

I don’t have any samples.

You can find information about using the Reporting Services web service here:

http://msdn.microsoft.com/en-us/library/ms152787%28v=SQL.90%29.aspx

and the SSIS Web Service control here:

http://msdn.microsoft.com/en-us/library/ms140114%28v=SQL.90%29.aspx

Thank you,

Architecture Team

No solution yet. I will need to continue my search. Stay tuned.