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


Data Driven Subscriptions


Data Driven Subscriptions

Author
Message
i_amhers
i_amhers
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 108
You should save Jason some time by scrolling up 2 posts.

If, however, you are unable to scroll here is the link:

http://www.sqlservercentral.com/articles/Development/2824/


-Thanks again Jason for the original post


Jeremy
DeafEater
DeafEater
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 260
Jason your post on manipulating SSRS subscriptions says you've incorporated various of the suggestions made by other contributors and a later post of yours suggests that the amended version is now posted with the article. However as far as I can see, the code in the article is still the original so is the amended version with variable numbers of parameters available from anywhere else?

Thanks for sharing as I was beginning to think it was just me that wanted to do this sort of thing. We need to email suppliers with a nicely formatted PDF of unacknowledged purchase order lines and your solution will be perfect for iterating through the list of affected suppliers and ensuring each one is notified with only the PO lines associated with them.

Regards

Robert Cowan
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6838 Visits: 4115
All,

This is a link to the most recent version. I have not returned to work on this process since it's creation and there may be many ways to improve upon it.

I submit this solution for you to build upon and improve at your leasure.

Enjoy!

http://www.sqlservercentral.com/articles/Development/2824/

______________________________________________________________________

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
DeafEater
DeafEater
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 260
Thanks Jason.

The improvement I've started on is rather than use pipe delimited placeholders in the various ExtensionSettings and Parameter values is to convert those two XML fields into table variables (two fields called 'Name' and 'Value'). Then it becomes much easier to update the values of such 'Name' keys as already exist and inserting the ones that don't. Then I can convert the two table variables back to XML and update the ExtensionSettings and Parameters fields with the respective values.

The idea behind doing it this way is that the stored subscription record can be a perfectly valid one that will run without alteration if needs be and the caller of the stored procedure can keep as many default values as it wants and supply only the amended or additional ones.

If it works out OK I'll post it as another variation on a theme.

Regards

Robert Cowan
jdubious
jdubious
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 247
Has anyone has success using this (excellent) sproc and sending to a group? We have 1 report, 1 parameter (userid), and several users getting the same report queried for their userid. The users receiving the report changes constantly. I'm trying to use a cursor (which prints out the result set correctly) to retrieve userid and email but the reports don't send. No errors and the job indicates that is has run once for each record in the query set. If I run the sproc for a single userid, it works fine, so email, job, etc are configured correctly. Any suggestions?

J.
*****
DECLARE curRevcon INSENSITIVE CURSOR
FOR SELECT CONTACTID, EMAIL FROM Table
DECLARE @CONTACTID VarChar(100)
DECLARE @EMAIL VarChar(100)


OPEN curRevcon
FETCH NEXT FROM curRevcon INTO @CONTACTID, @EMAIL
WHILE @@Fetch_Status = 0
BEGIN

EXEC data_driven_subscription
@scheduleID = '317F5B31-B453-4037-B85A-42907A9956A4',
@emailTO = @EMAIL,
@emailCC = '',
@emailBCC = '',
@emailReplyTO = 'me@me.com',
@emailBODY = 'Testing - did you get this?',
@param1 = @CONTACTID

FETCH NEXT FROM curRevcon INTO @CONTACTID, @EMAIL
END
CLOSE curRevcon

DEALLOCATE curRevcon

GO
Prince Ali
Prince Ali
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 400
Thanks for the great proc.
george.greiner
george.greiner
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 184
Does this also give me the ability to process a batch subscription. For instance if there are 10 finished files within the last 24 hours that need to go out in 10 separate reports (1 report per record) in 10 separate emails will this get the job done?
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6838 Visits: 4115
george.greiner (4/6/2010)
Does this also give me the ability to process a batch subscription. For instance if there are 10 finished files within the last 24 hours that need to go out in 10 separate reports (1 report per record) in 10 separate emails will this get the job done?


Sure. Wrap the call to this procedure in another procedure that loops through the list of reports that need to go out.

______________________________________________________________________

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
george.greiner
george.greiner
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 184
Jason Selburg (4/6/2010)
george.greiner (4/6/2010)
Does this also give me the ability to process a batch subscription. For instance if there are 10 finished files within the last 24 hours that need to go out in 10 separate reports (1 report per record) in 10 separate emails will this get the job done?


Sure. Wrap the call to this procedure in another procedure that loops through the list of reports that need to go out.


Okay cool I will attempt this today. I am new to SSRS and am doing what I can to do this and am glad this will be the way to get this done! Thanks for the quick reply and good work!
whuili
whuili
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
There is an app that does data-driven subscriptions for SQL Server Standard 2005 here: http://www.imageteq.com/Support/IMAGETEQSolutions/tabid/112/Default.aspx
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