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