Data Driven Subscriptions
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp
______________________________________________________________________
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. Selburg -
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.
-
Excellent article !! I can't wait to try it out.
Sunil
-
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.Named
ConfigFile = 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.
Regards,Yelena Varsha
-
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!
______________________________________________________________________
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. Selburg -
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.
--------------------
Colt 45 - the original point and click interface
-
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.
______________________________________________________________________
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. Selburg -
Jason,
I'm definitely interested in taking a look at your improvements, especially the improved method of waiting for subscriptions to finish.
-
Jason,
Works great except for the waiting for the report to finish code. Can you post this update please.
-
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
______________________________________________________________________
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. Selburg -
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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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'
)
as
DECLARE
@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
---------------------------------------------------------------------------------------------------
______________________________________________________________________
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. Selburg -
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
______________________________________________________________________
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. Selburg -
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.asp
Just remember to vote when the article comes out in the newsletter *grin*
Thanks
______________________________________________________________________
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. Selburg -
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
-
Hi there,
I'm interested in the new and improved data driven subscriptions solution. Is it still available?
Thanks,
Shalu
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply