Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Data Driven Subscriptions Updated Expand / Collapse
Author
Message
Posted Thursday, June 28, 2007 9:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:51 PM
Points: 2,717, Visits: 3,843
Yes and NO! There are all sorts of unique identifiers in the RS database and I really don't think you can just copy them over.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #377450
Posted Friday, November 2, 2007 10:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 10:34 AM
Points: 4, Visits: 97
I happened upon this article while attempting to create a method of creating data driven subscriptions in 2005 Standard. You can find the parameters list for your report in the dbo.Catalog table in the ReportServer database. The list is in XML format, so you'll have to do your own parsing. Also, I recommend using the stored procedure "dbo.GetReportParametersForExecution" as it will save you time recreating the wheel. The table dbo.Subscriptions stores the parameters in xml format and contains a name value pair that will be passed to the reporting engine.
Post #418065
Posted Wednesday, December 5, 2007 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:20 AM
Points: 2, Visits: 113
Jason: Thanks for this very interesting article. I thought you might be interested in some improvements that I've made to this process. Using your SQL as inspiration, I've ran with this idea and created a fairly generic stored procedure that will run data driven subscriptions for delivery to file share as well as email. In addition, this code will handle a variable number of parameters to be customized.

I put my sql and a writeup in a blog post here:

http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html

feel free to let me know if you find this useful.

Regards,
M Spilich
Post #429885
Posted Friday, March 7, 2008 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 7, 2010 10:57 AM
Points: 2, Visits: 19
Hi Jason,

If you want to send an email to multiple "TO" recipeints what is the separator I should be using?

Thanks,
Parag
Post #466009
Posted Friday, March 7, 2008 10:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:51 PM
Points: 2,717, Visits: 3,843
I believe it is a semi colon ;

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #466028
Posted Friday, March 7, 2008 12:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 7, 2010 10:57 AM
Points: 2, Visits: 19
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 =''
Post #466063
Posted Monday, May 12, 2008 2:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 2, 2009 12:59 PM
Points: 2, Visits: 34
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
Post #499148
Posted Tuesday, May 13, 2008 6:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 10:16 AM
Points: 24, Visits: 115
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
Post #499491
Posted Tuesday, May 13, 2008 7:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:51 PM
Points: 2,717, Visits: 3,843
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #499561
Posted Wednesday, May 14, 2008 1:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 10:16 AM
Points: 24, Visits: 115
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
Post #500206
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse