March 7, 2008 at 12:10 pm
Hmmm I tried that, but maybe I tried a comma. Once the subscription is built in reporting services, are the parms stored or do I have to declare them each time? Can you give me a sample set for your paramters? Which ones are required?
exec data_driven_subscription
@scheduleName = 'sales_call_sheet',
@emailTO = 'parag.shah@leeirnk.com',
@emailCC = '',
@emailBCC = '',
@emailReplyTO = '',
@emailBODY = '',
@parameterName = '',
@parameterValue = '',
@sub = '',
@renderFormat = 'PDF',
@exitCode = '',
@exitMessage =''
May 12, 2008 at 2:54 pm
I am running the Data Driven Subscription in a Cursor - for sending out unique instances of the report to individual locations. The problem I am running into is that I have to hard code in
WAITFOR DELAY '00:00:05'
Before the next fetch or it returns the default results (does not pass the new set of parameters).
Any thoughts on a delay solution that is not so arbitrary? I will eventually have a couple of hundred instances going out and if this "reset time" is variable I will have to make it a really long interval if there is no standard.
Thanks,
Bill
May 13, 2008 at 6:01 am
Hello,
This works perfectly for me when run once - but won't "re-run".
Every subsequent attempt results in an report status of 'Root element is missing'.
I've checked the Subscriptions and ReportSchedule table entries both before and after running and it all looks the same whether it's a first or subsequent run.
.... is there somewhere else I should be checking?
Helen
May 13, 2008 at 7:12 am
Helen Thorley (5/13/2008)
Hello,This works perfectly for me when run once - but won't "re-run".
Every subsequent attempt results in an report status of 'Root element is missing'.
I've checked the Subscriptions and ReportSchedule table entries both before and after running and it all looks the same whether it's a first or subsequent run.
.... is there somewhere else I should be checking?
Helen
Can you view the report through the web interface? I've had this happen when the parameters' format gets distorted. If you can see it through the web interface then I'd look very closely in the Subscriptions table.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 14, 2008 at 1:10 am
Hello,
Thank-you for the response.
The report looks fine via the web interface.
If I delete the subscription, recreate it and run the sproc it succeeds.
And the only differences I can find in the Subscriptions table, between test runs, is the last status
TestPoint LastStatus LastRunTime
----------------- ------------------------------ -----------------------
1: Before 1st Run New Subscription NULL
2: After Email Mail sent to helen.thorley 2008-05-13 14:56:49.380
3. After 2nd Run Root element is missing. 2008-05-13 14:57:29.863
I compared Subscriptions by saving it's contents at each of the test points above.
ARRGH!
Helen
September 13, 2008 at 11:37 am
Reseting Extension Settings:
Originally this SP was written to
1- make all declarations
2- get data to replace (this was dependent on the text being |TO| or something similar- static
3- replace data with new data
4- reset data back to static values to use SP again
New method-
1-make all declarations
2-reset data (just using an update)
3-get data
4-replace data
I think there is another method, which requires less code, and eliminates the wait method (something i needed to do, because i was going to use this on a trigger)- What I think you were originally looking for.
- so here it is
With the schedule in its original state -- ie |TO|, |CC|, |BC| (you just made it, or you have reset the values)..... you can copy the text that the ExtensionSettings field holds and then just run an update query on the start of the procedure-
The Pseudo code so it makes a little more sense:
Update Subscriptions
Set ExtensionSettings = ~OriginalValue~
Where (SubscriptionID = @subscriptionID)
// note* you could also use the UPDATETEXT method, instead of Update
If any one wants something clarified let me know.
Jeremy
December 9, 2008 at 2:07 pm
Does anyone have a solution to this error (exitMessage)?
"The subscription contains parameter values that are not valid."
When I run the subscription manually using the exact same values it runs fine. But when I run this using the script, it throws this error.
January 27, 2009 at 12:14 pm
Thank you for the useful sproc.
The only one small modification I've made:
SET @pVALUES = '< ParameterValues />' --// default if no parameters specified
There reason is if no parameters specified the column [parameters] in [Subscriptions] table needs to keep a valid XML (not blank).
Otherwise, SQL returns an error.
March 12, 2009 at 1:52 pm
Are you making an update to this script for SSRS 2008 Standard?
March 12, 2009 at 2:07 pm
I am still using SSRS 2005. And it works fine now. Thanks.
March 20, 2009 at 1:26 am
Jason,
I want to use a shared directory instead of emails to spread the report.
Do you think there is a way to come from your script to that in a short period?
Thanks & Regards
Patrick
March 23, 2009 at 9:29 am
You'll have to address the security issue. I haven't gone down that path, so good luck ... !!!!
and if you get it, post your solution here for all to see...
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 24, 2009 at 3:23 pm
I am able to run a file share-based subscription successfully. I put the credentials in when I originally created the subscription. The only problem I have now is that unless I specify a FIXED delay (like at least 15 seconds for one specific report), I don't get all of them created. Sometimes, it'll be every other one. Does anyone know what table or tables to refer to when the reporting engine is processing a report, and if so, prevent execution of another report. The Schedule table doesn't help. It appears records are saved almost immediately after a report is processing, whether the report has been saved or not. I do not want to resort to actually having to look at the directory to see if the .PDF has been saved, especially since it appears you can run two reports at once.
---edit---
I think it may be the Notifications and/or Event tables, but not sure.. These tables do seem to populate and truncate, though, as subscriptions are processed.
March 25, 2009 at 11:49 am
I believe I finally figured it out. I was able to queue up 7 reports successfully without any errors or 'default' reports being pushed out. It all lies in the delay mechanism. The one posted here did not work at all for fileshare subscriptions. I referred to http://blogs.msdn.com/psssql/archive/2009/02/02/why-aren-t-my-subscriptions-working.aspx, http://blogs.msdn.com/deanka/archive/2009/01/13/diagnosing-and-troubleshooting-subscriptions.aspx, and finally http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html. Basically you have two issues to contend with, one being that the job actually needs time to execute (usually about 1-3 seconds), and then you need time for the Event and Notifications tables to be processed. There are only two threads available, so slamming subscriptions one after another will not work and you end up with errors, uncustomized reports, and missing reports.
The delay mechanism I used is:
DECLARE @events int, @notifications int
--run the job
EXEC msdb..sp_start_job @job_name = @scheduleID
--give time for job to process and EVENT table to populate
WAITFOR DELAY ''00:00:02''
--make sure we limit how many subscriptions we run simultaneously
SELECT @events = COUNT(*) FROM ReportServer..Event WHERE (EventData = @SubscriptionID)
SELECT @notifications = COUNT(*) FROM ReportServer..Notifications WHERE (SubscriptionID = @SubscriptionID) --AND (ProcessHeartBeat IS NOT NULL)
WHILE (@events >= 1 OR @notifications >= 2)
BEGIN
--give some time for RS to pick up on pending subs in NOTIFICATIONS table
--i used 5 seconds since I know, on average, most reports I run take at least
--5 seconds (even though some may take a few minutes), so no need to check every second.
WAITFOR DELAY ''00:00:05''
SELECT @events = COUNT(*) FROM ReportServer..Event WHERE (EventData = @SubscriptionID)
SELECT @notifications = COUNT(*) FROM ReportServer..Notifications WHERE (SubscriptionID = @SubscriptionID) --AND (ProcessHeartBeat IS NOT NULL)
END
I am going to test this on a subscription with reports that consistently take 2-10 MINUTES a piece to run and see how it goes. But it definitely seems to work on smaller reports for the time being. I welcome your comments.
April 8, 2009 at 1:13 pm
HI jason:
We are using SSRS 2000 Standard edition. I have a request to subscribe one report to over thousands users. Is there any way to do that by using your store procedure? Obviously I do not want to type in
all email addresses manually...
thanks
Hui
Viewing 15 posts - 16 through 30 (of 90 total)
You must be logged in to reply to this topic. Login to reply