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 Wednesday, October 25, 2006 6:19 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857
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
Posted Thursday, December 21, 2006 8:45 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857
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
Posted Wednesday, January 31, 2007 6:04 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857
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
Posted Wednesday, October 3, 2007 11:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, February 11, 2008 6:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 9, 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
Posted Tuesday, February 12, 2008 9:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857
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
Posted Thursday, August 14, 2008 3:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 14, 2014 11:34 AM
Points: 1, Visits: 83
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
Posted Saturday, September 13, 2008 10:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:12 PM
Points: 68, Visits: 85
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
Posted Friday, October 17, 2008 7:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 12:00 PM
Points: 13, Visits: 450
Hi Jason,

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

Thanks!
Post #587619
Posted Friday, October 17, 2008 7:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 12:00 PM
Points: 13, Visits: 450
Hi Jason,

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

Thanks,
Post #587624
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse