SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Driven Subscriptions Updated


Data Driven Subscriptions Updated

Author
Message
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7108 Visits: 4115
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
Shane Gibson
Shane Gibson
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 115
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.
Matt Spilich
Matt Spilich
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 119
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
parag.shah-668802
parag.shah-668802
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7108 Visits: 4115
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
parag.shah-668802
parag.shah-668802
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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 =''
bill2059
bill2059
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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
HooRoo
HooRoo
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 116
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
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7108 Visits: 4115
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
HooRoo
HooRoo
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 116
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search