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 Expand / Collapse
Author
Message
Posted Friday, October 17, 2008 2:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 11:34 AM
Points: 38, Visits: 47
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
Post #587988
Posted Thursday, June 18, 2009 2:59 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:35 AM
Points: 141, Visits: 242
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
Post #737290
Posted Thursday, June 18, 2009 10:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 2,717, Visits: 3,843
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
Post #737728
Posted Thursday, June 18, 2009 10:35 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:35 AM
Points: 141, Visits: 242
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
Post #737761
Posted Wednesday, October 21, 2009 5:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 10:40 AM
Points: 6, Visits: 241
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


Post #806880
Posted Monday, November 2, 2009 9:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 12:48 PM
Points: 37, Visits: 399
Thanks for the great proc.
Post #812447
Posted Tuesday, April 6, 2010 10:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 8:44 AM
Points: 69, 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?
Post #897686
Posted Tuesday, April 6, 2010 11:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 2,717, Visits: 3,843
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
Post #897737
Posted Tuesday, April 6, 2010 11:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 8:44 AM
Points: 69, 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!
Post #897742
Posted Wednesday, April 28, 2010 4:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 28, 2010 4:10 PM
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
Post #912414
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse