November 2, 2009 at 9:06 am
Apparently its blocked by my workplace (the govt). *grumble grumble*
Randy
November 2, 2009 at 9:15 am
Maybe this will help. Here's the SP I have. I took the SP from the aforementioned website and created a new delay mechanism for file share delivery that did seem to work in initial testing. At this time, I am not currently using report subscriptions, so I can't verify it works 100% of the time, though.
USE [ReportServer]
GO
CREATE PROCEDURE [dbo].[CustomDataDrivenSubscription]
(
@SubscriptionID uniqueidentifier,
@parameterNameLIST nvarchar(4000), -- pipe delimited
@parameterValueLIST nvarchar(4000), -- pipe delimited
@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 sequence
--
@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,
@notifications int,
@events 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 [CustomSubscriptionHistory]
(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 database error occurred inserting the subscription history record.'
RETURN IsNull(@lerror, 0)
END
-- run the job
--SELECT StartRunTime = getDate()
EXEC msdb..sp_start_job @job_name = @scheduleID
--select * from reportserver..event
-- 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
--DECLARE @test-2 nvarchar(4000)
/* Test Queries */
--SELECT ReportID, ExtensionSettings, Parameters, ProcessStart, NotificationEntered, SubscriptionLastRunTime, BatchID, ProcessHeartbeat
--FROM dbo.Notifications
--WHERE (SubscriptionID = @SubscriptionID)
--SELECT
--SubsProcessing = (SELECT COUNT(*)
--FROM dbo.Notifications
--WHERE (SubscriptionID = @SubscriptionID) AND (ProcessHeartBeat IS NOT NULL)),
--SubsPending = (SELECT COUNT(*)
--FROM dbo.Notifications
--WHERE (SubscriptionID = @SubscriptionID) AND (ProcessHeartBeat IS NULL))
/* Old Delay Algorithm - Does not work for file share delivery */
--SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
--WHILE (@starttime > @lastruntime)
--BEGIN
--WAITFOR DELAY ''00:00:10''
--SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
--END
/* New Delay Algorithm
Note: IF EXISTS may be faster, however, TWO threads can run simultaneously, so need to wait longer than needed
Note: @events >= 1 OR @notifications >= 1 *WORKS* with 01 sec and 02 secs respectively, but takes longer than it should */
WAITFOR DELAY '00:00:02'
SELECT @events = COUNT(*) FROM ReportServer..Event WHERE (EventData = @SubscriptionID)
SELECT @notifications = COUNT(*) FROM ReportServer..Notifications WHERE (SubscriptionID = @SubscriptionID) --AND (ProcessHeartBeat IS NOT NULL)
WHILE (@events >= 1 OR @notifications >= 2)
BEGIN
WAITFOR DELAY '00:00:05'
SELECT @events = COUNT(*) FROM ReportServer..Event WHERE (EventData = @SubscriptionID)
SELECT @notifications = COUNT(*) FROM ReportServer..Notifications WHERE (SubscriptionID = @SubscriptionID) --AND (ProcessHeartBeat IS NOT NULL)
END
/* update the history table with the completion time */
UPDATE [CustomSubscriptionHistory]
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 database 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
November 2, 2009 at 9:32 am
Here's some additional sample code. This is the SQL to execute the stored procedure. You use it from the database where the physical data resides, NOT the ReportServer DB.
USE [DBToRetrieveDataFrom]
-- Schedule Reports for Companies
-- You would generally run this in a cursor to loop through CompanyID's, InvoiceID's, etc.
-- The entire batch would be scheduled via Agent or available on-demand through an administrative page.
DECLARE @compid int, @company nvarchar(100), @pvallist nvarchar(4000), @evallist nvarchar(4000)
DECLARE @exitCD int, @exitMSG varchar(100)
SET @compid = 12345
SELECT @company = Company
FROM dbo.Companies
WHERE (CompanyID = @compid)
SET @evallist = REPLACE(REPLACE(REPLACE(REPLACE(@company,N'''',N''''''),N',',N''),N'.',N''),N'&',N'-') + N' Activity Report (' + CONVERT(nvarchar(8), getdate(), 10) + N')'
SET @pvallist = CAST(@compid AS nvarchar(10)) + N'|9/1/2009|9/30/2009'
EXEC ReportServer.dbo.[CustomDataDrivenSubscription]
@SubscriptionID = 'AB4185FC-F1BF-422C-80A9-9CB0C3841E73',
@parameterNameLIST = 'companyid|startDate|stopDate',
@parameterValueLIST = @pvallist,
@ExtensionSettingNameLIST = 'FILENAME',
@ExtensionSettingValueLIST = @evallist,
@exitCode = @exitCD out,
@exitMessage = @exitMSG out
SELECT ExitCode = @exitCD, ExitMsg = @exitMSG
--<ParameterValues><ParameterValue><Name>PATH</Name><Value>UNC Path (i.e. \\MainServer\Reports\ReportA)</Value></ParameterValue><ParameterValue><Name>FILENAME</Name><Value>The name of the report being run</Value></ParameterValue><ParameterValue><Name>FILEEXTN</Name><Value>True</Value></ParameterValue><ParameterValue><Name>USERNAME</Name><Value>Encrypted Data</Value></ParameterValue><ParameterValue><Name>PASSWORD</Name><Value>Encrypted Data</Value></ParameterValue><ParameterValue><Name>RENDER_FORMAT</Name><Value>PDF</Value></ParameterValue><ParameterValue><Name>WRITEMODE</Name><Value>Overwrite</Value></ParameterValue></ParameterValues>
--<ParameterValues><ParameterValue><Name>stopDate</Name><Value>1/31/2009 12:00:00 AM</Value></ParameterValue><ParameterValue><Name>companyid</Name><Value>19021</Value></ParameterValue><ParameterValue><Name>startDate</Name><Value>1/1/2009 12:00:00 AM</Value></ParameterValue></ParameterValues>
November 2, 2009 at 10:46 am
Okay, got it! Instead of worrying about username/password for the extensionsettings, you're just editing a pre-existing subscription. Guess I was working too hard at it! 😛
Thanks for the assistance and all the copy/pastes! Much obliged!
Out of curiosity, why start the job using
EXEC msdb..sp_start_job @job_name = @scheduleID
instead of just doing what the job would be doing
exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @eventdata = @YourSubID
I am sure that the timing you have built in will be extremely useful as I get on with this now. Thanks for the assist!
Randy
November 2, 2009 at 11:09 am
Randy-574768 (11/2/2009)
Okay, got it! Instead of worrying about username/password for the extensionsettings, you're just editing a pre-existing subscription. Guess I was working too hard at it! 😛Thanks for the assistance and all the copy/pastes! Much obliged!
Out of curiosity, why start the job using
EXEC msdb..sp_start_job @job_name = @scheduleIDinstead of just doing what the job would be doing
exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @eventdata = @YourSubIDI am sure that the timing you have built in will be extremely useful as I get on with this now. Thanks for the assist!
Right, you just create a template manual subscription and then edit it on the fly. You're not actually re-creating it.
In terms of starting the job, not sure why it is done that way. That portion is not my code. If it works by calling AddEvent, just use that.
February 19, 2010 at 9:00 am
Hello,
Thanks for your script, it works very well.
Unfortunately, I can’t execute the same ScheduleName twice.
The subscription indicates “Root element is missing” and I have the error -7,-2 or -10 when I try a second time.
I don't understand how to fix it
Kevin ALBURQUERQUE
Business Intelligence Consultant
February 19, 2010 at 9:03 am
Kevin,
When that situation occurs it means that something went wrong during the execution. You will need to delete that subscription and create a new one.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 22, 2010 at 1:26 am
I tried to create a new subscription. And indeed, it works.
But in my case, I would like to generate a report several times without creating and deleting the subscription manually.
Here is my project:
- Users choose a CSV file on their computer
- This CSV file is converted to XML and introduced in a a SQL Table with an uniqueidentifier ID
- Then, I extract the XML in a table and join with an other table to get my final table on which a report is based.
- This report is generated and an email is send to the user (thanks to you !)
So ,your script should be run several times for several users.
I need to create a subsciption one time only manually.
Maybe it's possible to create and delete a subscription with a script or a stored procedure ?
Thx
Kevin
February 22, 2010 at 6:36 am
You should find out why the subscription is getting messed up instead of working around the problem. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 5, 2010 at 6:31 am
Hi,
Im trying to automate the subscription to store the report file as PDF to a file share.
My procedure works well with output to email and the PDF-File attached.
Then i tried this: http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html .
But it doesnt work for me. I think the problem could be timecritical because of the timeintervall the report is generated.
Has anybody any hints? Do you know any other procedures to automate a data driven subscription to save the report to a file share within the standard edition. My demand is to generate multiple reports with one parameter and a file share which is different for each report. For each report i want to generate i have one line in my subscription table with the value for the parameter and the path to the file share, etc.
March 5, 2010 at 8:29 am
March 25, 2010 at 10:01 am
Great article do you have an update for 2008? I have tried to implement this with SSRS 2008 -- I tested in SQL Managemetn studio executing the SP data_driven_subscription and it says the job started successfully but when I look in the Report Manager the Status column states Root element is missing.
Any suggestions to fix this?
Thanks,
March 25, 2010 at 10:14 am
The code I used and rewrote some posts back worked in SQL2008 when I tested it at the time. Unfortunately I am not now actively using data-driven subscriptions. Perhaps try to delete the preliminary subscription and re-create it (thus recreating the XML) and run the query again. If after running the query causes the corruption again, post your code here so I and others can take a look.
March 25, 2010 at 10:36 am
So I tried your suggestion of deleting and recreating the subscription and the first time I executed the SP it worked perfectly. The second time I executed it I get the same status of root element missing. As per your request here is the code (sorry I don't know how to put the code in that neat little box I have seen others do):
Create procedure [dbo].[data_driven_subscription]
( @scheduleName nvarchar(255),
@emailTO nvarchar (2000) = NULL,
@emailCC nvarchar (2000) = NULL,
@emailBCC nvarchar (2000) = NULL,
@emailReplyTO nvarchar (2000) = NULL,
@emailBODY nvarchar (4000) = NULL,
@parameterName nvarchar(4000) = NULL,
@parameterValue nvarchar (256) = NULL,
@sub nvarchar(1000) = NULL,
@renderFormat nvarchar(50) = 'PDF',
@exitCode int output,
@exitMessage nvarchar(255) output
)
AS
DECLARE
@ptrval binary(16),
@PARAMptrval binary(16),
@subscriptionID uniqueidentifier,
@scheduleID uniqueidentifier,
@starttime datetime,
@lastruntime datetime,
@execTime datetime,
@dVALUES nvarchar (4000),
@pVALUES nvarchar (4000),
@previousDVALUES nvarchar (4000),
@previousPVALUES nvarchar (4000),
@lerror int,
@insertID int,
@lretval int,
@rowcount int
SET @starttime = DATEADD(second, -2, getdate())
SET @emailTO = rtrim(IsNull(@emailTO, ''))
SET @emailCC = rtrim(IsNull(@emailCC, ''))
SET @emailBCC = rtrim(IsNull(@emailBCC, ''))
SET @emailReplyTO = rtrim(IsNull(@emailReplyTO, ''))
SET @emailBODY = rtrim(IsNull(@emailBODY, ''))
SET @parameterValue = rtrim(IsNull(@parameterValue, ''))
SET @lerror = 0
SET @rowcount = 0
IF @emailTO = '' AND @emailCC = ''
AND @emailBCC = ''
BEGIN
SET @exitCode = -1
SET @exitMessage = 'A recipient is required.'
RETURN 0
END
-- get the subscription ID
SELECT
@subscriptionID = rs.subscriptionID,
@scheduleID = rs.ScheduleID
FROM
--ReportSchedule rs--Original Code
Sib_ReportServer..ReportSchedule rs--Updated from original code by Mark Fyffe
--INNER JOIN subscriptions s--Original Code
INNER JOIN Sib_ReportServer..subscriptions s--Updated from original code by Mark Fyffe
ON rs.subscriptionID = s.subscriptionID
WHERE
extensionSettings like '%' + @scheduleName + '%'
IF @subscriptionID Is Null
BEGIN
SET @exitCode = -2
SET @exitMessage = 'The subscription does not exist.'
RETURN 0
END
/* just to be safe */
SET @dVALUES = ''
SET @pVALUES = ''
SET @previousDVALUES = ''
SET @previousPVALUES = ''
/* apply the settings that are defined */
IF IsNull(@emailTO, '') <> ''
SET @dVALUES = @dVALUES + '<ParameterValue><Name>TO</Name><Value>'
+ @emailTO + '</Value></ParameterValue>'
IF IsNull(@emailCC, '') <> ''
SET @dVALUES = @dVALUES + '<ParameterValue><Name>CC</Name><Value>'
+ @emailCC + '</Value></ParameterValue>'
IF IsNull(@emailBCC, '') <> ''
SET @dVALUES = @dVALUES + '<ParameterValue><Name>BCC</Name><Value>'
+ @emailBCC + '</Value></ParameterValue>'
IF IsNull(@emailReplyTO, '') <> ''
SET @dVALUES = @dVALUES + '<ParameterValue><Name>ReplyTo</Name><Value>'
+ @emailReplyTO + '</Value></ParameterValue>'
IF IsNull(@emailBODY, '') <> ''
SET @dVALUES = @dVALUES + '<ParameterValue><Name>Comment</Name><Value>'
+ @emailBODY + '</Value></ParameterValue>'
IF IsNull(@sub, '') <> ''
SET @dVALUES = @dVALUES + '<ParameterValue><Name>Subject</Name><Value>'
+ @sub + '</Value></ParameterValue>'
IF IsNull(@dVALUES , '') <> ''
SET @dVALUES = '<ParameterValues>' + @dVALUES
+ '<ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue>'
IF IsNull(@dVALUES , '') <> ''
SET @dVALUES = @dVALUES +'<ParameterValue><Name>RenderFormat</Name><Value>' +
@renderFormat + '</Value></ParameterValue>' +
'<ParameterValue><Name>IncludeLink</Name><Value>False</Value></ParameterValue></ParameterValues>'
IF IsNull(@parameterName, '') <> '' and IsNull(@parameterValue, '') <> ''
SET @pVALUES = '<ParameterValues><ParameterValue><Name>' +
@parameterName +
'</Name><Value>' +
@parameterValue +
'</Value></ParameterValue></ParameterValues>'
/* verify that some delivery settings where passed in */
-- @pVALUES are not checked as they may all be defaults
IF IsNull(@dVALUES , '') = ''
BEGIN
SET @exitCode = -3
SET @exitMessage = 'No delivery settings were supplied.'
RETURN 0
END
/* get the current parameter values and delivery settings */
SELECT @previousDVALUES = extensionSettings
--FROM Subscriptions--Original Code
FROM Sib_ReportServer..Subscriptions--Updated from original code by Mark Fyffe
WHERE SubscriptionID = @SubscriptionID
SELECT @previousPVALUES = parameters
--FROM Subscriptions--Original Code
FROM Sib_ReportServer..Subscriptions--Updated from original code by Mark Fyffe
WHERE SubscriptionID = @SubscriptionID
--UPDATE Subscriptions--Original Code
UPDATE Sib_ReportServer..Subscriptions--Updated from original code by Mark Fyffe
SET extensionSettings = '', parameters = ''
WHERE SubscriptionID = @SubscriptionID
SELECT @lerror=@@error, @rowCount=@@rowCount
IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0
BEGIN
SET @exitcode = -5
SET @exitMessage = 'A data base error occurred clearing the previous subscription settings.'
RETURN IsNull(@lerror, 0)
END
-- set the text point for this record
SELECT @ptrval = TEXTPTR(ExtensionSettings)
--FROM Subscriptions--Original Code
FROM Sib_ReportServer..Subscriptions--Updated from original code by Mark Fyffe
WHERE SubscriptionID = @SubscriptionID
SELECT @lerror=@@error
IF @lerror <> 0 OR @ptrval Is NULL
BEGIN
SET @exitcode = -6
SET @exitMessage = 'A data base error occurred retrieving the TEXT Pointer of the Delivery Values.'
RETURN IsNull(@lerror, 0)
END
UPDATETEXT Subscriptions.ExtensionSettings
@ptrval
null
null
@dVALUES
SELECT @lerror=@@error
IF @lerror <> 0
BEGIN
SET @exitcode = -7
SET @exitMessage = 'A data base error occurred updating the Delivery settings.'
RETURN IsNull(@lerror, 0)
END
-- set the text point for this record
SELECT @PARAMptrval = TEXTPTR(Parameters)
--FROM Subscriptions--Original Code
FROM Sib_ReportServer..Subscriptions--Updated from original code by Mark Fyffe
WHERE SubscriptionID = @SubscriptionID
SELECT @lerror=@@error
IF @lerror <> 0 OR @ptrval Is NULL
BEGIN
SET @exitcode = -8
SET @exitMessage = 'A data base error occurred retrieving the TEXT Pointer of the Parameter Values.'
RETURN IsNull(@lerror, 0)
END
UPDATETEXT Subscriptions.Parameters
@PARAMptrval
null
null
@pVALUES
SELECT @lerror=@@error
IF @lerror <> 0
BEGIN
SET @exitcode = -9
SET @exitMessage = 'A data base error occurred updating the Parameter settings.'
RETURN IsNull(@lerror, 0)
END
/* insert a record into the history table */
SET @execTime = getdate()
--INSERT Subscription_History--Original Code
INSERT Sib_ReportServer..Subscription_History--Updated from original code by Mark Fyffe
(subscriptionID, scheduleName, ParameterSettings, DeliverySettings, dateExecuted, executeStatus)
VALUES
(@subscriptionID, @scheduleName, @parameterValue, @dVALUES , @execTime, 'incomplete' )
SELECT @lerror=@@error, @insertID=@@identity
IF @lerror <> 0 OR IsNull(@insertID, 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
--SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID--Original Code
SELECT @lastruntime = LastRunTime FROM Sib_ReportServer..Schedule WHERE ScheduleID = @scheduleID--Updated from original code by Mark Fyffe
WHILE (@starttime > @lastruntime)
BEGIN
WAITFOR DELAY '00:00:01'
--SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID--Original Code
SELECT @lastruntime = LastRunTime FROM Sib_ReportServer..Schedule WHERE ScheduleID = @scheduleID--Original Code
END
/* update the history table with the completion time */
--UPDATE Subscription_History--Original Code
UPDATE Sib_ReportServer..Subscription_History--Updated from original code by Mark Fyffe
SET dateCompleted = getdate()
WHERE subscriptionID = @subscriptionID
and scheduleName = @scheduleName
and ParameterSettings = @parameterValue
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--Original code
UPDATE Sib_ReportServer..Subscriptions--Updated from original code by Mark Fyffe
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--Original code
FROM Sib_ReportServer..subscriptions--Updated from original code by Mark Fyffe
WHERE subscriptionID = @subscriptionID
SET @exitCode = 1
RETURN 0
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
Appreciate the help thanks,
March 25, 2010 at 11:17 am
Don't use the UPDATETEXT method. I recall people saying they were having trouble with that method. Refer to sample SQL given on page 5 and at http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html for an alternate using the XML data type. If you still have issues, let me know and I'll see what I can do as time permits.
Viewing 15 posts - 46 through 60 (of 90 total)
You must be logged in to reply to this topic. Login to reply