December 6, 2012 at 8:04 am
Hi all,
I'm currently using SQL Standard Edition so I know it does't allow me to do data driven subscriptions.
I've taken this script below to create a Stored Procedure
CREATE procedure dbo.data_driven_subscription
( @scheduleID uniqueidentifier,
@emailTO varchar (2000) = ' ',
@emailCC varchar (2000) = ' ',
@emailBCC varchar (2000) = ' ',
@emailReplyTO varchar (2000) = ' ',
@emailBODY varchar (8000) = ' ',
@param1 varchar (256) = ' '
)
as
DECLARE
@ptrval binary(16),
@PARAMptrval binary(16),
@TOpos int,
@CCpos int,
@BCCpos int,
@RTpos int,
@BODYpos int,
@PARAM1Pos int,
@length int,
@subscriptionID uniqueidentifier
-- set the subscription ID
SELECT @subscriptionID = SubscriptionID
FROM ReportSchedule WHERE ScheduleID = @scheduleID
-- set the text point for this records Email info
SELECT @ptrval = TEXTPTR(ExtensionSettings)
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
-- set the text point for this records Parameter info
SELECT @PARAMptrval = TEXTPTR(Parameters)
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
-- set the start position for the TO Address
SELECT @TOpos = patindex('%|TO|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
IF isnull(@TOpos, '') <> '' and @TOpos > 0 and len(@emailTo) > 0
-- change the TO address
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@TOpos
4
@emailTo
-- set the start position for the CC Address
SELECT @CCpos = patindex('%|CC|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
IF isnull(@CCpos, '') <> '' and @CCpos > 0 and len(@emailCC) > 0
-- change the TO address
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@CCpos
4
@emailCC
-- set the start position for the BCC Address
SELECT @BCCpos = patindex('%|BC|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
IF isnull(@BCCpos, '') <> '' and @BCCpos > 0 and len(@emailBCC) > 0
-- change the TO address
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@BCCpos
4
@emailBCC
-- set the start position for the REPLY TO Address
SELECT @RTpos = patindex('%|RT|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
IF isnull(@RTpos, '') <> '' and @RTpos > 0 and len(@emailReplyTO) > 0
-- change the REPLY TO address
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@RTpos
4
@emailReplyTO
-- set the start position for the BODY Text
SELECT @BODYpos = patindex('%|BD|%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
IF isnull(@BODYpos, '') <> '' and @BODYpos > 0 and len(@emailBODY) > 0
-- change the REPLY TO address
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@BODYpos
4
@emailBODY
-- set the start position for the Parameter 1
SELECT @PARAM1Pos = patindex('%|P1|%', Parameters) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
IF isnull(@PARAM1Pos, '') <> '' and @PARAM1Pos > 0 and len(@param1) > 0
-- change the Parameter 1 value
UPDATETEXT Subscriptions.Parameters
@PARAMptrval
@PARAM1Pos
4
@param1
-- run the job
exec msdb..sp_start_job @job_name = @scheduleID
-- this give the report server time to execute the job.
-- there is probably a better way to do this, so let me know if you know it ...
WAITFOR DELAY '00:00:10'
-- now change everything back so you can run this again
-- set the start position for the TO Address
SELECT @TOpos = patindex('%' + @emailTO + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
SELECT @length = len(@emailTO)
IF @length > 0
-- replace the addresses with the original |TO|
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@TOpos
@length
'|TO|'
-- set the start position for the TO Address
SELECT @CCpos = patindex('%' + @emailCC + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
SELECT @length = len(@emailCC)
IF @length > 0
-- replace the addresses with the original |CC|
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@CCpos
@length
'|CC|'
-- set the start position for the TO Address
SELECT @BCCpos = patindex('%' + @emailBCC + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
SELECT @length = len(@emailBCC)
IF @length > 0
-- replace the addresses with the original |BC|
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@BCCpos
@length
'|BC|'
-- set the start position for the REPLY TO Address
SELECT @RTpos = patindex('%' + @emailReplyTO + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
SELECT @length = len(@emailReplyTO)
IF @length > 0
-- replace the addresses with the original |RT|
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@RTpos
@length
'|RT|'
-- set the start position for the BODY Text
SELECT @BODYpos = patindex('%' + @emailBODY + '%', ExtensionSettings) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
SELECT @length = len(@emailBODY)
IF @length > 0
-- replace the addresses with the original |BD|
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
@BODYpos
@length
'|BD|'
-- set the start position for the Parameter
SELECT @PARAM1Pos = patindex('%' + @param1 + '%', Parameters) - 1
FROM Subscriptions WHERE SubscriptionID = @subscriptionID
SELECT @length = len(@param1)
IF @length > 0
-- replace the addresses with the original |P1|
UPDATETEXT Subscriptions.Parameters
@PARAMptrval
@PARAM1Pos
@length
'|P1|'
GO
--EXEC data_driven_subscription
-- @ScheduleID = 'BF435933-C03C-4B6C-B611-54D38FB280C3',
-- @EmailTo = 'somebody@somewhere.com',
-- @EmailCC = 'somebody@somewhere.com',
-- @EmailBCC = 'somebody@somewhere.com',
-- @EmailReplyTo = 'somebody@somewhere.com',
-- @EmailBody = 'Test 123',
-- @param1 = 'First Parameter';
The question I now have, is it possible for @EmailTo to populate with the multiple email addresses contained win the 'EmailAddress' column within my tablix?
Is it even possible in the Standard Edition?
Cheers.
December 6, 2012 at 8:29 am
Emailto just needs to be a ;Semicolon separated list of emails.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply