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

---------------------------------------------------------------------------------------------------