SQLServerCentral / Discuss Content Posted by Jason Selburg / Article Discussions / Article Discussions by Author / Data Driven Subscriptions / Latest Posts

RE: Data Driven Subscriptions

michael.e.donnel — Wed, 28 Nov 2012 12:51:06 GMT

Very nice article. I have also had a similar request for our user community. The biggest issue we face is the implementation of a user interface that is simplistic enough for the uers of our reports. We have around 20 varied reports and each have up to eight optional parameters to choose from. One thing I have noticed is that if you have an error on the parameters its not fun to try and clear it up so the subscription will run again. I can run, as a test, a single subscription a dozen times without changing the parameters and can count on it failing at least 20% of the time due to a parameter issue. It's extremely frustrating.If you post any further tweaks to the code I would love to see them.Once again this is the best article I have found on this particular subject.

RE: Data Driven Subscriptions

Jason Selburg — Fri, 12 Oct 2012 11:50:35 GMT

[quote][b]aditya.anita.moitra (10/12/2012)[/b][hr]Jason,I am in the same situation to implement Data Driven Subscription to a File Share while we are using SQL Server 2008 R2 Standard Edition. I'm really interested in taking a look at your improvements. Please send me the instructions for the modified stored procedure.Regards,Aditya Moitra[/quote]The newest version is available here ---> http://www.sqlservercentral.com/articles/2824/The "file share" naming method is described here ---> http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/69546/Hope this helps.

RE: Data Driven Subscriptions

aditya.anita.moitra — Fri, 12 Oct 2012 11:39:45 GMT

Jason,I am in the same situation to implement Data Driven Subscription to a File Share while we are using SQL Server 2008 R2 Standard Edition. I'm really interested in taking a look at your improvements. Please send me the instructions for the modified stored procedure.Regards,Aditya Moitra

RE: Data Driven Subscriptions

aef_21 — Wed, 18 May 2011 08:23:13 GMT

nevermind I got it to work

RE: Data Driven Subscriptions

aef_21 — Wed, 18 May 2011 08:20:49 GMT

Does this work with sql 2005 also?

RE: Data Driven Subscriptions

Jason Selburg — Mon, 16 May 2011 11:12:55 GMT

This is the most recent code I have. It may need some tweaking for 2008, I've not tested it on '08.[url=http://www.sqlservercentral.com/articles/Development/2824/]http://www.sqlservercentral.com/articles/Development/2824/[/url]

RE: Data Driven Subscriptions

marlene.vanhavermaet — Mon, 16 May 2011 10:49:08 GMT

Jason,I'm definitely interested in the revised version. If you still have it available, please send it or tell me how I could find a link. Thank you so much.

RE: Data Driven Subscriptions

niclasericsson — Sun, 26 Sep 2010 08:11:07 GMT

Thanks Jason for sharing this clever way to overcome some of the short comings in SQL Standard Edition. I don't mean to spam this form but I like to present an alternative from [url=http://www.fuel9.com/home/]Fuel9 [/url]called [url=http://www.fuel9.com/home/content/SQL-Server-Reporting-Services]Boomerang[/url]. The Boomerang framework use the SSRS (2005 or 2008) API to render reports and supports things like "file share" output and multiple reporting parameters in addition to Email/Fax/FTP/Print Server output.You interact with the framework in a database interface that requires just basic SQL skills and it includes a service oriented method of managing deployed "subscriptions". The standard edition of Boomerang is free. /N

RE: Data Driven Subscriptions

vikas.dharia — Mon, 16 Aug 2010 11:49:03 GMT

The Solution does not work for SQL Server 2008 Standard , Please assist

RE: Data Driven Subscriptions

Travis Baseflug-281032 — Sun, 06 Jun 2010 17:46:51 GMT

Is there an update for this sproc?

RE: Data Driven Subscriptions

whuili — Wed, 28 Apr 2010 16:15:14 GMT

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

RE: Data Driven Subscriptions

george.greiner — Tue, 06 Apr 2010 11:11:35 GMT

[quote][b]Jason Selburg (4/6/2010)[/b][hr][quote][b]george.greiner (4/6/2010)[/b][hr]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?[/quote]Sure. Wrap the call to this procedure in another procedure that loops through the list of reports that need to go out.[/quote]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!

RE: Data Driven Subscriptions

Jason Selburg — Tue, 06 Apr 2010 11:08:11 GMT

[quote][b]george.greiner (4/6/2010)[/b][hr]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?[/quote]Sure. Wrap the call to this procedure in another procedure that loops through the list of reports that need to go out.

RE: Data Driven Subscriptions

george.greiner — Tue, 06 Apr 2010 10:13:04 GMT

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?

RE: Data Driven Subscriptions

Prince Ali — Mon, 02 Nov 2009 09:34:24 GMT

Thanks for the great proc.

RE: Data Driven Subscriptions

jdubious — Wed, 21 Oct 2009 17:12:10 GMT

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 CURSORFOR SELECT CONTACTID, EMAIL FROM TableDECLARE @CONTACTID VarChar(100)DECLARE @EMAIL VarChar(100)OPEN curRevconFETCH NEXT FROM curRevcon INTO @CONTACTID, @EMAILWHILE @@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

RE: Data Driven Subscriptions

DeafEater — Thu, 18 Jun 2009 10:35:37 GMT

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.RegardsRobert Cowan

RE: Data Driven Subscriptions

Jason Selburg — Thu, 18 Jun 2009 10:09:50 GMT

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![url]http://www.sqlservercentral.com/articles/Development/2824/[/url]

RE: Data Driven Subscriptions

DeafEater — Thu, 18 Jun 2009 02:59:56 GMT

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.RegardsRobert Cowan

RE: Data Driven Subscriptions

i_amhers — Fri, 17 Oct 2008 14:16:56 GMT

You should save Jason some time by scrolling up [b]2 posts[/b].If, however, you are unable to scroll here is the link:http://www.sqlservercentral.com/articles/Development/2824/ -Thanks again Jason for the original postJeremy

RE: Data Driven Subscriptions

Scott Schommer — Fri, 17 Oct 2008 07:31:37 GMT

Hi Jason,Can you please send the updated instructions and procedures to shasta247@hotmail.comThanks,

RE: Data Driven Subscriptions

Scott Schommer — Fri, 17 Oct 2008 07:30:31 GMT

Hi Jason, Can you please send me the updated procedures and instructions at shasta247@hotmail.comThanks!

RE: Data Driven Subscriptions

i_amhers — Sat, 13 Sep 2008 10:51:25 GMT

Reseting Extension Settings:Originally this SP was written to 1- make all declarations2- get data to replace (this was dependent on the text being |TO| or something similar- static3- replace data with new data4- reset data back to static values to use SP againNew method-1-make all declarations2-reset data (just using an update)3-get data4-replace dataI 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 isWith 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 SubscriptionsSet ExtensionSettings = ~OriginalValue~Where (SubscriptionID = @subscriptionID)// note* you could also use the UPDATETEXT method, instead of UpdateIf any one wants something clarified let me know.Jeremy

RE: Data Driven Subscriptions

dennis lee-452735 — Thu, 14 Aug 2008 15:38:10 GMT

Jason,Check this out!Someone provides the ability to deal with a parameter list rather than a single parameter. http://sqlblog.com/blogs/greg_low/archive/2008/08/13/data-driven-subscriptions-in-sql-server-2005-standard-edition.aspx

RE: Data Driven Subscriptions

Jason Selburg — Tue, 12 Feb 2008 09:46:04 GMT

[url]http://www.sqlservercentral.com/articles/Development/2824/[/url]

RE: Data Driven Subscriptions

Shalu Goel — Mon, 11 Feb 2008 18:45:17 GMT

Hi there,I'm interested in the new and improved data driven subscriptions solution. Is it still available?Thanks,Shalu

RE: Data Driven Subscriptions

rlopez — Wed, 03 Oct 2007 11:43:42 GMT

The subscription code is great I am interested in the updated code. I am having a problem however, where the subscription works a few times and then stops and I get the message below when I try to view the subscriptions in the Report Manager. Thank you.An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help For more information about this error navigate to the report server on the local server machine, or enable remote errors

RE: Data Driven Subscriptions

Jason Selburg — Wed, 31 Jan 2007 18:04:00 GMT

Hi all,The new article won't be published until early March. But until then you can go here http://www.sqlservercentral.com/columnists/jselburg/2824.aspJust remember to vote when the article comes out in the newsletter *grin*Thanks

RE: Data Driven Subscriptions

Jason Selburg — Thu, 21 Dec 2006 20:45:00 GMT

Heads Up!A new and greatly improved procedure/article is coming up (within a few days). I have found that this procedure also works with RS 2005 and the error-trapping has been greatly improved.One thing that is not addressed is the ability to pass multiple parameters. Quite honestly, there are too many ways to attack that problem and it really depends on your environment and needs. So you'll have to take that task on yourselves....Another feature not addressed is the "File Share" delivery method. The username and password encryption currently has me stumped, but I'll continue to work on it.Thanks,Jason

RE: Data Driven Subscriptions

Jason Selburg — Wed, 25 Oct 2006 18:19:00 GMT

Here is a revised version that works much more efficiently, and addresses various issues with the original version.---------------------------------------------------------------------------------------------------USE [ReportServer]GO/****** Object: StoredProcedure [dbo].[js_data_driven_subscription] Script Date: 09/29/2006 18:10:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE procedure [dbo].[data_driven_subscription] ( @scheduleID uniqueidentifier, @emailTO varchar (2000) = ' ', @emailCC varchar (2000) = ' ', @emailBCC varchar (2000) = ' ', @emailReplyTO varchar (2000) = ' ', @emailBODY varchar (8000) = ' ', @paramValue1 varchar (256) = ' ', @paramName1 varchar(8000), @sub varchar(1000), @renderFormat varchar(50) = 'PDF' )asDECLARE @ptrval binary(16), @PARAMptrval binary(16), @subscriptionID uniqueidentifier, @starttime datetime, @lastruntime datetime, @dValues varchar (8000), @pValues varchar (8000) set @starttime = DATEADD(second, -2, getdate()) set @emailTO = rtrim(@emailTO) set @emailCC = rtrim(@emailCC) set @emailBCC = rtrim(@emailBCC) set @emailReplyTO = rtrim(@emailReplyTO) set @emailBODY = rtrim(@emailBODY) set @paramValue1 = rtrim(@paramValue1) -- set the subscription ID SELECT @subscriptionID = SubscriptionID FROM ReportSchedule WHERE ScheduleID = @scheduleID set @dValues = '' set @pValues = '' if IsNull(@emailTO, '') <> '' set @dValues = @dValues + 'TO' + @emailTO + '' if IsNull(@emailCC, '') <> '' set @dValues = @dValues + 'CC' + @emailCC + '' if IsNull(@emailBCC, '') <> '' set @dValues = @dValues + 'BCC' + @emailBCC + '' if IsNull(@emailReplyTO, '') <> '' set @dValues = @dValues + 'ReplyTo' + @emailReplyTO + '' if IsNull(@emailBODY, '') <> '' set @dValues = @dValues + 'Comment' + @emailBODY + '' if IsNull(@sub, '') <> '' set @dValues = @dValues + 'Subject' + @sub + '' if IsNull(@dValues, '') <> '' set @dValues = '' + @dValues + 'IncludeReportTrue' if IsNull(@dValues, '') <> '' set @dValues = @dValues + 'RenderFormat' + @renderFormat + '' + 'IncludeLinkFalse' if IsNull(@paramName1, '') <> '' and IsNull(@paramValue1, '') <> '' set @pValues = '' + @paramName1 + '' + @paramValue1 + '' if IsNull(@dValues, '') <> '' and IsNull(@pValues, '') <> '' BEGIN update Subscriptions set extensionsettings = '' WHERE SubscriptionID = @SubscriptionID update Subscriptions set parameters = '' WHERE SubscriptionID = @SubscriptionID -- set the text point for this record SELECT @ptrval = TEXTPTR(ExtensionSettings) FROM Subscriptions WHERE SubscriptionID = @SubscriptionID UPDATETEXT Subscriptions.ExtensionSettings @ptrval null null @dValues -- set the text point for this record SELECT @PARAMptrval = TEXTPTR(Parameters) FROM Subscriptions WHERE SubscriptionID = @SubscriptionID UPDATETEXT Subscriptions.Parameters @PARAMptrval null null @pValues -- run the job exec msdb..sp_start_job @job_name = @scheduleID -- this give the report server time to execute the job SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID While (@starttime > @lastruntime) Begin print '...' print @lastruntime WAITFOR DELAY '00:00:03' SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID End END---------------------------------------------------------------------------------------------------

RE: Data Driven Subscriptions

Jason Selburg — Fri, 07 Jul 2006 06:35:00 GMT

To address the wait time for the subscription to complete, make the following changes to the stored proc. This solution was devised by hughthomas -> http://www.sqlservercentral.com/forums/userinfo.aspx?id=86254

Add the following lines to the end variable declaration section ...

@starttime datetime, @lastruntime datetime

set @starttime = DATEADD(second, -2, getdate())set @execTime = getdate()

Now replace the "WAITFOR DELAY '00:00:10' " with ...

-- this give the report server time to execute the job SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID While (@starttime > @lastruntime) Begin print '...' print @lastruntime WAITFOR DELAY '00:00:03' SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID End

RE: Data Driven Subscriptions

grant whellum — Thu, 06 Jul 2006 20:12:00 GMT

Jason,

Works great except for the waiting for the report to finish code. Can you post this update please.

RE: Data Driven Subscriptions

Drew Williams — Thu, 06 Jul 2006 08:12:00 GMT

Jason,I'm definitely interested in taking a look at your improvements, especially the improved method of waiting for subscriptions to finish.

RE: Data Driven Subscriptions

Jason Selburg — Fri, 02 Jun 2006 19:30:00 GMT

I’m happy to say that I’ve made some major improvements to the code using many of your suggestions.

Improvements:

A more efficient (and shorter) procedure that handles errors.
The procedure is commented better and should give you a MUCH better understanding of what is going on.
An improved method of “waiting” until the subscription completes before continuing.
The ability to handle multiple (almost unlimited) parameters.
*BIG* The ability to NAME your subscriptions and refer to them by name. (As it would get difficult to manage by schedule IDs)
More settings available on the subscription, like “include report/link, render format, etc…”

If you are interested, reply and I’ll send the updated procedures and instructions. There is a replacement for the main procedure, one to gather and format the parameters and a table to hold them. Once in place, it’s quite easy to use.

RE: Data Driven Subscriptions

philcart — Thu, 01 Jun 2006 21:34:00 GMT

Good article, always handy to see alternative methods.

I built a C# console app that renders the reports via the webservice. Once I got my head around the .Net stuff is was relatively easy to put together.

The app takes a stored procedure name as one of its parameters and this stored procedure returns a DataReader which is used to drive the report rendering.

One of the nice features of rendering via the webservice is that the same report can be rendered in any of the registered Reporting services formats and you can have 0-n parameters for the report.

RE: Data Driven Subscriptions

Jason Selburg — Thu, 01 Jun 2006 20:00:00 GMT

Thanks everyone for the comments.

I have one quick update, replace the text "IF @length > 0" with "IF isNull(@length, 0) > 0" in each place in the procedure.

Also, I am working on a more robust version of this approach and will post the new info.

Thanks!

RE: Data Driven Subscriptions

Yelena Varshal — Thu, 01 Jun 2006 09:28:00 GMT

I use VBscript with Named Arguments where I pass the configuration file name. This configuration files contains 4 lines: TOaddresses, SubjectText, BodyText and Attachments. We do have to have 1 line batch files for different configuration files because I am trying to keep it simple for the person who manages these files. It could be replaced with one batch if you know you DOS programming well. This batch is just scheduled in Windows Task Scheduler.

The calling batch:

cscript "MyScriptThatSendsEmails.vbs" /ConfigFile:"TextFileContainingEmailStrings.txt"

Code in the MyScriptThatSendsEmails.vbs has to contain the following lines in order to get the name of the configuration file.

Set colNamedArguments = WScript.Arguments.NamedConfigFile = colNamedArguments.Item("ConfigFile")

Then I use FileSystemObject to access lines in this file.

The report itself is sent as an attachment. The report has to be pre-generated by any report tool and placed in the same directory each time for the script to be able to find it.

RE: Data Driven Subscriptions

Sunil Shah — Thu, 01 Jun 2006 07:23:00 GMT

Excellent article !! I can't wait to try it out.

Sunil

RE: Data Driven Subscriptions

Hugh Thomas — Thu, 01 Jun 2006 07:21:00 GMT

Great article! I emailed Jason a suggestion for the delay, to maybe use the ModifiedDate and LastRunTime fields in the Subscriptions table to test if LastRun is greater than Modified, at which time you can move forward. I'm going to try that and see if it works.

Data Driven Subscriptions

Jason Selburg — Tue, 30 May 2006 10:07:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp