February 12, 2008 at 9:46 am
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[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 14, 2008 at 3:38 pm
Jason,
Check this out!
Someone provides the ability to deal with a parameter list rather than a single parameter.
September 13, 2008 at 10:51 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
October 17, 2008 at 7:30 am
Hi Jason,
Can you please send me the updated procedures and instructions at shasta247@hotmail.com
Thanks!
October 17, 2008 at 7:31 am
Hi Jason,
Can you please send the updated instructions and procedures to shasta247@hotmail.com
Thanks,
October 17, 2008 at 2:16 pm
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
June 18, 2009 at 2:59 am
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
June 18, 2009 at 10:09 am
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[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 18, 2009 at 10:35 am
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
October 21, 2009 at 5:12 pm
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
November 2, 2009 at 9:34 am
Thanks for the great proc.
April 6, 2010 at 10:13 am
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?
April 6, 2010 at 11:08 am
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[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 6, 2010 at 11:11 am
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!
April 28, 2010 at 4:15 pm
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
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply