Data Driven Subscriptions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jason Selburg » Data Driven Subscriptions

40 posts, Page 2 of 4

««1 2 3 4 »»»

Data Driven Subscriptions

Rate Topic

Display Mode

Topic Options

Author

Message

Jason Selburg

Posted Wednesday, October 25, 2006 6:19 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582, Visits: 3,552

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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Post #318161

Jason Selburg

Posted Thursday, December 21, 2006 8:45 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582, Visits: 3,552

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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Post #332401

Jason Selburg

Posted Wednesday, January 31, 2007 6:04 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582, Visits: 3,552

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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Post #341504

rlopez

Posted Wednesday, October 03, 2007 11:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, January 18, 2009 3:11 PM
Points: 1, Visits: 10

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

Post #406344

Shalu Goel

Posted Monday, February 11, 2008 6:45 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 09, 2010 6:03 PM
Points: 1, Visits: 30

Hi there,

I'm interested in the new and improved data driven subscriptions solution. Is it still available?

Thanks,

Shalu

Post #454176

Jason Selburg

Posted Tuesday, February 12, 2008 9:46 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582, Visits: 3,552

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 #454510

dennis lee-452735

Posted Thursday, August 14, 2008 3:38 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 4:57 PM
Points: 1, Visits: 82

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

Post #553166

i_amhers

Posted Saturday, September 13, 2008 10:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, October 26, 2012 1:10 PM
Points: 4, Visits: 31

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

Post #569048

Scott Schommer

Posted Friday, October 17, 2008 7:30 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:12 AM
Points: 13, Visits: 381

Hi Jason,

Can you please send me the updated procedures and instructions at shasta247@hotmail.com

Thanks!

Post #587619

Scott Schommer

Posted Friday, October 17, 2008 7:31 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:12 AM
Points: 13, Visits: 381

Hi Jason,

Can you please send the updated instructions and procedures to shasta247@hotmail.com

Thanks,

Post #587624

« Prev Topic | Next Topic »

40 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions