Friday, November 30, 2007

Using Data Driven Subscriptions in SqlServer 2005 Standard Edition

Hey, it's been a while since I've done anything worth posting up here, but this was too much fun not to share. I had a need to output reports out of SSRS using functionality like data driven subscriptions, but we're using standard edition of Sql Server, so the functionality is not available. I wondered if it was possible to get around this issue, and after some research I came across these two great articles by Jason Selberg:

Data Driven Subscriptions part 2
Data Driven Subscriptions

Jason clearly put a lot of thought into his code and I borrow from it heavily (imitation = flattery, no?) However these didn't work for me out of the box but I've made what I think are some improvements to that are worth sharing:

    A more efficient/stable method of updating the subscription settings: Jasons code concatenated strings together, while I'm converting the columns to XML and then update just the items in the xml that need to be changed. (2005 only)

    Handle a variable number/type of parameters for the report: Jasons code works for one parameter, this will work for any number of parameters

    Allow delivery to file share and export to excel



Prerequisites:
    This code will only work on Sql 2005 because of my use of the xml datatype.

    Have at least one report created and loaded into reporting services (ideally with more than one input parameter)

    Create one subscription for the report with a generic set of parameter inputs. Have it run once to verify that it works, and then set the schedule to be one time (sometime in the past) so it will never run again.

    Create a table (included in the comments of the sproc) and the worker function fn_split (Code at the bottom)

    Ultimately, one execution of this sproc will only push out one report, so you'll need to implement a small bit of code on your own to call this sproc over and over again for each custom report that is pushed out.


On to the code:



CREATE TABLE [dbo].[Custom_Subscription_History](
[nDex] [int] IDENTITY(1,1) NOT NULL,
[SubscriptionID] [uniqueidentifier] NULL,
[ScheduleName] [nvarchar](260) COLLATE Latin1_General_CI_AS_KS_WS NULL,
[parameterSettings] [varchar](8000) COLLATE Latin1_General_CI_AS_KS_WS NULL,
[deliverySettings] [varchar](8000) COLLATE Latin1_General_CI_AS_KS_WS NULL,
[dateExecuted] [datetime] NULL,
[executeStatus] [nvarchar] (260) NULL,
[dateCompleted] [datetime] NULL,
[executionTime] AS (datediff(second,[datecompleted],[dateexecuted])),
CONSTRAINT [PK_Subscription_History] PRIMARY KEY CLUSTERED
(
[nDex] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]









SET ANSI_NULLS ON
go
ALTER procedure [dbo].[Custom_data_driven_subscription]
(
@SubscriptionID uniqueidentifier,
@parameterNameLIST nvarchar(4000), -- pipe delimeted
@parameterValueLIST nvarchar(4000), -- pipe delimeted
@ExtensionSettingNameLIST nvarchar(4000),
@ExtensionSettingValueLIST nvarchar(4000),

@exitCode int output,
@exitMessage nvarchar(255) output
)
AS

/*

DATE CREATED: 11/30/2007
AUTHOR: Matt Spilich : Heavily Modified from code from Jason L. Selburg (http://www.sqlservercentral.com/articles/Development/2824/)

PURPOSE:
This procedure extends the functionality of the subscription feature in
Microsoft SQL Reporting Services 2005, allowing the subscriptions to be triggered
via code.


PARAMETERS:
@SubscriptionID = This is the GUID of the subscription for the report we want to blast. This is passed (instead of the report name to ensure uniqueness)

@parameterNameLIST nvarchar(4000), -- A pipe delimeted list of parameter names
@parameterValueLIST nvarchar(4000), -- A pipe delimeted list of values to replace those names with

-- ex. @parameterNameLIST = 'PARAM1|PARAM2|PRRAM3' where these are the parameter names defined in RS for the report
-- @parameterValueLIST = 'VALUE1|VALUE2|VALUE3' where these are the parameter values that match with each p name in the given sequance
--

@ExtensionSettingNameLIST nvarchar(4000),
@ExtensionSettingValueLIST nvarchar(4000), -- same as above. Here, some knowledge of what the Extenstion names the SSRS uses is required. Look at the XML, folks!

@exitCode = The returned integer value of the procedure's execution result.
-2 'The subscription does not exist.'
-4 'A data base error occurred inserting the subscription history record.'
-5 'A data base error occurred clearing the previous subscription settings.'
-6 'A data base error occurred replacing the XML of the ExtensionSetting.'
-7 'A data base error occurred updating the Delivery settings.'
-8 'A data base error occurred replacing the XML of the Parameter.'
-9 'A data base error occurred updating the Parameter settings.'
-10 'A data base error occurred updating the subscription history record.'
-11 'A data base error occurred resetting the previous subscription settings.'

@exitMessage = The text description of the failure or success of the procedure.

PRECONDITIONS:
The subscription being called must exist

If any of the recipients email address are outside of the report server's domain, then you may
need to contact your Network Administrator to allow email forwarding from your email server.

POST CONDITIONS:
The report is delivered or an error code and message is returned.

SECURITY REQUIREMENTS:
The user which calls this stored procedure must have execute permissions.

DEPENDANCES:
Tables:
ReportSchedule = Installed with SQL RS 2005
Subscription_History = Must be created using the following script.
---------------------------------------------------------------------
CREATE TABLE [dbo].[Custom_Subscription_History](
[nDex] [int] IDENTITY(1,1) NOT NULL,
[SubscriptionID] [uniqueidentifier] NULL,
[ScheduleName] [nvarchar](260) COLLATE Latin1_General_CI_AS_KS_WS NULL,
[parameterSettings] [varchar](8000) COLLATE Latin1_General_CI_AS_KS_WS NULL,
[deliverySettings] [varchar](8000) COLLATE Latin1_General_CI_AS_KS_WS NULL,
[dateExecuted] [datetime] NULL,
[executeStatus] [nvarchar] (260) NULL,
[dateCompleted] [datetime] NULL,
[executionTime] AS (datediff(second,[datecompleted],[dateexecuted])),
CONSTRAINT [PK_Subscription_History] PRIMARY KEY CLUSTERED
(
[nDex] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
---------------------------------------------------------------------

Subscriptions = Installed with SQL RS 2005
Schedule = Installed with SQL RS 2005

*/


DECLARE
--@subscriptionID uniqueidentifier,
@scheduleID uniqueidentifier,
@DELIM varchar(1),
@NODE_NAME varchar(1000),
@NODE_VALUE varchar(1000),
@SUB_DATA xml,
@UPDATE_DONE bit,
@lerror int,

@starttime datetime,
@lastruntime datetime,
@execTime datetime,
@previousDVALUES nvarchar (4000),
@previousPVALUES nvarchar (4000),

@insertID int,
@lretval int,
@rowcount int


DECLARE @PARAMS TABLE (PARAM_NAME varchar(1000), PARAM_VALUE varchar(1000))
DECLARE @EXTENSION TABLE (EXTENSION_NAME varchar(1000), EXTENSION_VALUE varchar(1000))

set @delim = '|'
set @UPDATE_DONE = 0
set @starttime = dateadd(ss,-2,getdate())

-- populate the @PARAMS table
insert into @PARAMS
select A.VALUE [Name], B.VALUE [Value] from dbo.fn_Split(@parameterNameLIST,@delim) A
inner join dbo.fn_Split(@parameterValueLIST,@delim) B
on A.idx = B.idx

-- populate the @EXTENSION table
insert into @EXTENSION
select A.VALUE [Name], B.VALUE [Value] from dbo.fn_Split(@ExtensionSettingNameLIST,@delim) A
inner join dbo.fn_Split(@ExtensionSettingValueLIST,@delim) B
on A.idx = B.idx


select @scheduleID = rs.ScheduleID
FROM
ReportSchedule rs
WHERE rs.subscriptionID = @subscriptionID

IF @subscriptionID Is Null or @scheduleID is NULL
BEGIN
SET @exitCode = -2
SET @exitMessage = 'The subscription does not exist.'
RETURN 0
END


-- Modify the XML in the Parameters column to use the data that was passed in.
select @SUB_DATA = Parameters
, @previousDVALUES = extensionSettings
, @previousPVALUES = parameters
from SUBSCRIPTIONS
where SubscriptionID = @subscriptionID

set @UPDATE_DONE = 0
Declare C_PARAMS cursor for select * from @PARAMS
open C_PARAMS
fetch next from C_PARAMS into @NODE_NAME, @NODE_VALUE
WHILE @@FETCH_STATUS = 0
BEGIN

set @SUB_DATA.modify('
replace value of (/ParameterValues[1]/ParameterValue[Name=sql:variable("@NODE_NAME")][1]/Value[1]/text()[1])
with sql:variable("@NODE_VALUE")
')

set @UPDATE_DONE = 1

fetch next from C_PARAMS into @NODE_NAME, @NODE_VALUE
END
if @UPDATE_DONE = 1
BEGIN
update SUBSCRIPTIONS set Parameters = convert(text, convert(varchar(max), @SUB_DATA)) where SubscriptionID = @SubscriptionID

SELECT @lerror=@@error

IF @lerror <> 0
BEGIN
SET @exitcode = -8
SET @exitMessage = 'A data base error occurred updating the XML of the Parameter settings.'
RETURN IsNull(@lerror, 0)
END
END
close C_PARAMS
deallocate C_PARAMS


-- Modify the XML in the ExtensionSettings column to use the data that was passed in.
select @SUB_DATA = ExtensionSettings from SUBSCRIPTIONS where SubscriptionID = @subscriptionID
set @UPDATE_DONE = 0
Declare C_SETTINGS cursor for select * from @EXTENSION
open C_SETTINGS
fetch next from C_SETTINGS into @NODE_NAME, @NODE_VALUE
WHILE @@FETCH_STATUS = 0
BEGIN

set @SUB_DATA.modify('
replace value of (/ParameterValues[1]/ParameterValue[Name=sql:variable("@NODE_NAME")][1]/Value[1]/text()[1])
with sql:variable("@NODE_VALUE")
')
set @UPDATE_DONE = 1
fetch next from C_SETTINGS into @NODE_NAME, @NODE_VALUE
END
close C_SETTINGS
deallocate C_SETTINGS
if @UPDATE_DONE = 1
BEGIN
update SUBSCRIPTIONS set ExtensionSettings = convert(text, convert(varchar(max), @SUB_DATA)) where SubscriptionID = @SubscriptionID

SELECT @lerror=@@error

IF @lerror <> 0
BEGIN
SET @exitcode = -9
SET @exitMessage = 'A data base error occurred updating the XML of the ExtensionSettings settings.'
RETURN IsNull(@lerror, 0)
END
END


-- insert a record into the history table

SET @execTime = getdate()
INSERT [Custom_Subscription_History]
(subscriptionID, scheduleName, ParameterSettings, DeliverySettings, dateExecuted, executeStatus)
select subscriptionID, '', Parameters, ExtensionSettings , @execTime, 'incomplete'
from subscriptions
where subscriptionID = @subscriptionID


SELECT @lerror=@@error, @rowCount=@@rowCount

IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0
BEGIN
SET @exitcode = -4
SET @exitMessage = 'A data base error occurred inserting the subscription history record.'
RETURN IsNull(@lerror, 0)
END

-- run the job
EXEC msdb..sp_start_job @job_name = @scheduleID

-- this gives the report server time to execute the job
-- We're going to look for the report in executionlog, that's the best way to actaully wait for it to complete.
-- I don't know for sure that it will appear if there is an issue, so we'll wait up to 5 minutes in total.

--While not exists (select
-- or
SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
WHILE (@starttime > @lastruntime)
BEGIN
WAITFOR DELAY '00:00:01'
SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
END

/* update the history table with the completion time */
UPDATE [Custom_Subscription_History]
SET dateCompleted = getdate(), executeStatus = 'completed'
WHERE subscriptionID = @subscriptionID
and dateExecuted = @execTime

SELECT @lerror=@@error, @rowCount=@@rowCount

IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0
BEGIN
SET @exitcode = -10
SET @exitMessage = 'A data base error occurred updating the subscription history record.'
RETURN IsNull(@lerror, 0)
END

/* reset the previous delivery and parameter values */
UPDATE Subscriptions
SET extensionSettings = @previousDVALUES
, parameters = @previousPVALUES
WHERE SubscriptionID = @SubscriptionID

SELECT @lerror=@@error, @rowCount=@@rowCount

IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0
BEGIN
SET @exitcode = -11
SET @exitMessage = 'A data base error occurred resetting the previous subscription settings.'
RETURN IsNull(@lerror, 0)
END

/* return the result of the subscription */
SELECT @exitMessage = LastStatus
FROM subscriptions
WHERE subscriptionID = @subscriptionID

SET @exitCode = 1
RETURN 0
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO













/****** Object: UserDefinedFunction [dbo].[fn_Split] Script Date: 11/30/2007 12:10:30 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO




ALTER FUNCTION [dbo].[fn_Split]
(
@sText varchar(MAX),
@sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint

IF @sText = ''
RETURN

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 1
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END
ELSE
BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END

END

RETURN
END


--- DONE WITH FN_SPLIT



Ok now to run this code: Here are some sample calls to the stored procedure for various examples. These require a little knowlege of the subscription table in ReportServer database but it's suprisingly easy I think. Remember that you only need to override the Parameters and Extension settings that you want to be different for a custom delivery of the report. For example, when delivering by email, there is an extension setting to customize the renderformat of the report, but I'm only choosing to override the TO and the SUBJECT values.


-- Example of calling the report for delivery to file share
-- for this call the subscription ID being passed in should be subscription to deliver to file share

exec [Custom_data_driven_subscription]
@SubscriptionID = '7EAB32BA-BEB2-4066-B0FC-BB7691E6ED38',
@parameterNameLIST = 'STARTDATE|ENDDATE|PARAM_INT',
@parameterValueLIST = '1/1/2007|1/1/2008|100',
@ExtensionSettingNameLIST = 'PATH|FILENAME',
@ExtensionSettingValueLIST = '\\servername\fileshare\subfolder|Report_Name',
@exitCode = @exitCD out,
@exitMessage = @exitMSG out

select @exitCD, @exitMSG

-- Example of calling the report for delivery to email
-- for this call the subscription ID being passed in should be a email delivey subscription

exec [Custom_data_driven_subscription]
@SubscriptionID = '56YB32BA-BEB2-4066-B0FC-BB7691E6ED38',
@parameterNameLIST = 'STARTDATE|ENDDATE|PARAM_INT',
@parameterValueLIST = '1/1/2007|1/1/2008|100',
@ExtensionSettingNameLIST = 'TO|Subject',
@ExtensionSettingValueLIST = 'myemail@mydomain.com|@ReportName was executed at @ExecutionTime',
@exitCode = @exitCD out,
@exitMessage = @exitMSG out

select @exitCD, @exitMSG


Enjoy!
Matt

16 comments:

Unknown said...

Matt - This is great! Exactly what I was looking for. It took a little while to get it to work exactly right - but it is now doing exactly what I needed. Just awesome.

One note - the delay mechanism, where it checks whether the job has completed, works just fine for e-mailed reports. However, for file based subscriptions it would let me try to run another report before the previous one was done. I tried to figure it out but finally just inserted a two second delay which cleared it up.

Thanks!

Matt said...

Yeah, I think I ended up doing the same thing myself in my live version of this code - but then never went back to the post to fix it. Thanks for adding the comment.

Matt

Anonymous said...

Doods. what db should these objects get created in, Subscribers, ReportServer, other?

Also, why are schema qualified names necessary i.e.

ReportServer..Subscriptions

Matt said...

I created them in my ReportServer DB. If you create them in the reportserver db, then you shouldn't need qualified names. I suppose you could create your own user DB to hold your custom code and then you'd need the fully qualified names.

Anonymous said...

Matt,

Fantastic, helped me out of a real hole!

One thing that may help others, who like me are new to some of the concepts in the SP is that the ParameterNames in the @parameterNameLIST are Case sensitive.

Hope this will help someone.

Thanks again Matt.

MarkGA said...

Thaks Matt for the info.... I have it all setup with the subscription...the function, the proc and the new table on the report server database of the hosting website....When I go to run the sample code

DECLARE @SubscriptionID uniqueidentifier,
@parameterNameLIST nvarchar(4000), -- pipe delimeted
@parameterValueLIST nvarchar(4000), -- pipe delimeted
@ExtensionSettingNameLIST nvarchar(4000),
@ExtensionSettingValueLIST nvarchar(4000),
@exitCd int ,
@exitMsg nvarchar(255),
@exitCode int ,
@exitMessage nvarchar(255)

exec [Custom_data_driven_subscription]
@SubscriptionID = '56YB32BA-BEB2-4066-B0FC-BB7691E6ED38',
@parameterNameLIST = 'REQUESTID',
@parameterValueLIST = '5715', -- POPULATE THIS FIELD
@ExtensionSettingNameLIST = 'TO|Subject',
@ExtensionSettingValueLIST = 'A016668|@ReportName was executed at @ExecutionTime', -- POPULATE THIS FIELD
@exitCode = @exitCD out,
@exitMessage = @exitMSG out

select @exitCD, @exitMSG


I get this error message

Msg 8114, Level 16, State 5, Procedure Custom_data_driven_subscription, Line 0
Error converting data type varchar to uniqueidentifier.

(1 row(s) affected)


Any ideas????

Matt said...

Hmm, I'll take a look at this error messageand get back to you. Are you running SQL 2005 or SQL 2008 reporting services?

Anonymous said...

Thanks Matt this is brilliant and very useful.

Also after going through your code and a few trial and errors i managed to get you code to work for 2008 and 2008R2.

Replace the following piece of code:

set @SUB_DATA.modify('replace value of (/ParameterValues[1]/ParameterValue[Name=sql:variable("@NODE_NAME")][1]/Value[1]/text()[1])
with sql:variable("@NODE_VALUE")')

with the the below piece of code:

SET @SUB_DATA.modify('replace value of (/ParameterValues/ParameterValue/Name[text()=sql:variable("@NODE_NAME")]/../Value/text())[1]
with sql:variable("@NODE_VALUE")')

Thanks again.

Anonymous said...

Hi, I was wondering how to distribute to multiple receipients.
I have to get TO address dynamically from another db table.

Please advise how can I assign to
@ExtensionSettingValueLIST ='@TO|@ReportName was executed at @ExecutionTime'

Thanks

Unknown said...

Hello, How to handle Multiple email id's dynamically from distribution list.

Matt said...

Sammy: I generally have the list of people that I want to get the report in a table where I can query it and then use a cursor to make multiple calls to the sproc like this:


DECLARE C CURSOR FOR
SELECT EMAIL + '|()', PARAMETER from [TABLE]

OPEN C

FETCH NEXT FROM C INTO @evalue, @pvalue
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC GLG_data_driven_subscription
@SubscriptionID = 'XXXXXXX-F939-48FB-8715-8B1E3EBC7825' -- YOUR REPORT GUID
,@parameterNameLIST = 'YOUR_PARAM'
,@parameterValueLIST = @pvalue
,@ExtensionSettingNameLIST = 'TO|BCC'
,@ExtensionSettingValueLIST = @evalue
,@exitCode = @exitCD out
,@exitMessage = @exitMSG out

PRINT 'Exit Code: ' + CONVERT(VARCHAR(20), @exitCD)
PRINT 'Exit Msg: ' + @exitMSG

WAITFOR DELAY '00:00:10'


FETCH NEXT FROM C INTO @evalue , @pvalue

END

CLOSE C
DEALLOCATE C

Ben said...

Hi Matt,

This is great, got this all working and solved a massive problem.

One thing I am hoping to achieve is renaming the file that is attached to the email. I have tried a few things (file, attachment, reportname) in the ,@ExtensionSettingNameLIST variable but nothing seemed to change it.

Do you know what the appropriate setting is? Also do you have a list of other settings that can be altered there as that may be useful moving forward.

Many Thanks
Ben

Anonymous said...

Thanks Matt. I used the cursor to pick dynamic data (email) from table to distribute. I just wondering, how can I schedule this script?
Thank you
Sammy

Matt said...

Ben, I'm not aware of a way to change the filename that is attached. It appears to default to the name of the report. In general, you can use this code to override anything in the ExtensionSettings or ParameterColumns of your subscription. For example, I am overriding the Comment to customize a message that is sent to the user in one of my reports by passing 'TO|BCC|Comment' in the @ExtensionSettingNameList and the corresponding new values in @ExtensionsettingValueList.

Matt said...

Sammy, as for scheduling, I use SQL Agent for these on the server where the ReportingServices database lives.

Anonymous said...

What do you recommend to set the script delimiter & extension setting tokens inside the report manager subscription for the file share delivery?

we cannot place a | X | in the name of the file or the file path directory itself (UNC Convention). :-[ i am rather new to all of this. any advice is much appreciated!