EitanBlumin/Data Driven Alternative Email and File Share.sql

## Data Driven Alternative Email and File Share.sql
-- Based on script by Jason Selburg
-- https://www.sqlservercentral.com/Forums/Topic279460-150-1.aspx
-- http://www.sqlservercentral.com/scripts/Miscellaneous/31733/
USE ReportServer
GO

IF OBJECT_ID(N'[dbo].[data_driven_subscription]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[data_driven_subscription]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.data_driven_subscription
(
@scheduleID uniqueidentifier,
@emailTO nvarchar (2000) = ' ',
@emailCC nvarchar (2000) = ' ',
@emailBCC nvarchar (2000) = ' ',
@emailReplyTO nvarchar (2000) = ' ',
@emailBODY nvarchar(max) = ' ',
@sub nvarchar(1000) = ' ',
@renderFormat nvarchar(50) = 'PDF',
@IncludeReport bit = 0,
@IncludeLink bit = 0,
@Priority nvarchar(10) = 'NORMAL',
@param1 nvarchar (256) = ' ',
@p1 nvarchar(4000) = NULL
)

AS

DECLARE
@ptrval binary(16),
@PARAMptrval binary(16),
@TOpos int,
@CCpos int,
@BCCpos int,
@RTpos int,
@BODYpos int,
@PARAM1Pos int,
@length int,
@subscriptionID uniqueidentifier,
@job_status int,
@I int, -- the rest were added by hugh
@starttime datetime,
@lastruntime datetime,
@execTime datetime,
@dValues nvarchar (max),
@pValues nvarchar (max)

set @starttime = DATEADD(second, -2, getdate())
set @job_status = 1
set @I = 1
set @emailTO = rtrim(@emailTO)
set @emailCC = rtrim(@emailCC)
set @emailBCC = rtrim(@emailBCC)
set @emailReplyTO = rtrim(@emailReplyTO)
set @emailBODY = rtrim(@emailBODY)
set @param1 = rtrim(@param1)
set @Priority = rtrim(@Priority)
set @renderFormat = rtrim(@renderFormat)


-- set the subscription ID
SELECT @subscriptionID = SubscriptionID
FROM ReportSchedule WHERE ScheduleID = @scheduleID


set @dValues = ''
set @pValues = ''


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 = @dValues + '<ParameterValue><Name>Priority</Name><Value>' + IsNull(NullIf(@Priority,''), 'NORMAL') + '</Value></ParameterValue>'

if IsNull(@dValues, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>IncludeReport</Name><Value>' + CASE WHEN @IncludeReport = 1 THEN 'True' ELSE 'False' END + '</Value></ParameterValue>'

if IsNull(@dValues, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>IncludeLink</Name><Value>' + CASE WHEN @IncludeLink = 1 THEN 'True' ELSE 'False' END + '</Value></ParameterValue>'

if IsNull(@dValues, '') <> ''
set @dValues = '<ParameterValues>' + @dValues + '<ParameterValue><Name>RenderFormat</Name><Value>' + IsNull(@renderFormat, 'PDF') + '</Value></ParameterValue>' +
'</ParameterValues>'


if IsNull(@p1, '') <> '' and IsNull(@param1, '') <> ''
	set @pValues = '<ParameterValues><ParameterValue><Name>' +
	@p1 +
	'</Name><Value>' +
	@param1 +
	'</Value></ParameterValue></ParameterValues>'
ELSE
	set @pValues = '<ParameterValues/>'

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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

----------------------------------------------------------
/* Use .WRITE instead of UPDATETEXT
The following example uses the .WRITE clause to update a partial value in DocumentSummary,
an nvarchar(max) column in the Production.Document table.
The word components is replaced with the word features by specifying the replacement word,
the starting location (offset) of the word to be replaced in the existing data,
and the number of characters to be replaced (length).
The example also uses the OUTPUT clause to return the before and after images of the DocumentSummary column to the @MyTableVar table variable.

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table (
    SummaryBefore nvarchar(max),
    SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT deleted.DocumentSummary,
       inserted.DocumentSummary
    INTO @MyTableVar
WHERE Title = N'Front Reflector Bracket Installation';
SELECT SummaryBefore, SummaryAfter
FROM @MyTableVar;
GO*/

-- Based on script by Jason Selburg
-- https://www.sqlservercentral.com/Forums/Topic279460-150-1.aspx
-- http://www.sqlservercentral.com/scripts/Miscellaneous/31733/
USE ReportServer
GO

IF OBJECT_ID(N'[dbo].[data_driven_subscription_file_share]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[data_driven_subscription_file_share]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.data_driven_subscription_file_share
(
@scheduleID uniqueidentifier,
@FileName nvarchar (2000) = ' ',
@FilePath nvarchar (2000) = ' ',
@renderFormat nvarchar(50) = 'PDF',
@WriteMode nvarchar (20) = 'OverWrite'
)

AS

DECLARE
@ptrval binary(16),
@subscriptionID uniqueidentifier,
@job_status int,
@I int, -- the rest were added by hugh
@starttime datetime,
@lastruntime datetime,
@execTime datetime,
@dValues nvarchar (max),
@UserName nvarchar(4000),
@Password nvarchar(4000),
@CurrentExtensionSettings XML

set @starttime = DATEADD(second, -2, getdate())
set @job_status = 1
set @I = 1
set @FileName = rtrim(@FileName)
set @FilePath = rtrim(@FilePath)
set @renderFormat = rtrim(@renderFormat)


-- set the subscription ID
SELECT @subscriptionID = SubscriptionID
FROM ReportSchedule WHERE ScheduleID = @scheduleID

SELECT @CurrentExtensionSettings = CONVERT(xml,extensionsettings)
FROM Subscriptions WHERE SubscriptionID = @SubscriptionID

SELECT @UserName = p.[USERNAME], @Password = p.[PASSWORD]
FROM
(
	SELECT
		X.query('.').value('(ParameterValue/Name/text())[1]', 'nvarchar(4000)') AS Nam,
		X.query('.').value('(ParameterValue/Value/text())[1]', 'nvarchar(4000)') AS Val
	FROM @CurrentExtensionSettings.nodes('ParameterValues/ParameterValue') as T(X)
	WHERE X.query('.').value('(ParameterValue/Name/text())[1]', 'nvarchar(4000)') IN ('USERNAME', 'PASSWORD')
) AS q
pivot (max(Val) for Nam in ([USERNAME], [PASSWORD])) p


set @dValues = ''


if IsNull(@FileName, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>FILENAME</Name><Value>' + @FileName + '</Value></ParameterValue>'

if IsNull(@FilePath, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>PATH</Name><Value>' + @FilePath + '</Value></ParameterValue>'

if IsNull(@UserName, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>USERNAME</Name><Value>' + @UserName + '</Value></ParameterValue>'

if IsNull(@Password, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>PASSWORD</Name><Value>' + @Password + '</Value></ParameterValue>'

if IsNull(@dValues, '') <> ''
set @dValues = @dValues + '<ParameterValue><Name>WRITEMODE</Name><Value>' + IsNull(NullIf(@WriteMode,''), 'OverWrite') + '</Value></ParameterValue>'

if IsNull(@dValues, '') <> ''
set @dValues = '<ParameterValues>' + @dValues + '<ParameterValue><Name>RENDER_FORMAT</Name><Value>' + IsNull(@renderFormat, 'PDF') + '</Value></ParameterValue>' +
'</ParameterValues>'

if IsNull(@dValues, '') <> ''
BEGIN

update Subscriptions set extensionsettings = '' 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

-- 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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

----------------------------------------------------------
/* Use .WRITE instead of UPDATETEXT
The following example uses the .WRITE clause to update a partial value in DocumentSummary,
an nvarchar(max) column in the Production.Document table.
The word components is replaced with the word features by specifying the replacement word,
the starting location (offset) of the word to be replaced in the existing data,
and the number of characters to be replaced (length).
The example also uses the OUTPUT clause to return the before and after images of the DocumentSummary column to the @MyTableVar table variable.

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table (
    SummaryBefore nvarchar(max),
    SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT deleted.DocumentSummary,
       inserted.DocumentSummary
    INTO @MyTableVar
WHERE Title = N'Front Reflector Bracket Installation';
SELECT SummaryBefore, SummaryAfter
FROM @MyTableVar;
GO*/

## Data Driven Alternative Usage Example Email.sql
USE AgenciesReports
GO

IF OBJECT_ID(N'[dbo].[RunPseudoDataDrivenReport]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[RunPseudoDataDrivenReport]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
-- Sample usage:
EXEC [RunPseudoDataDrivenReport] @ReportConfigID = 56, @ReportPath = N'/PeriodicReports/Monthly Report'
*/
CREATE PROCEDURE dbo.[RunPseudoDataDrivenReport]
	@ReportConfigID		INT,
	@ReportPath			NVARCHAR(850)
AS
DECLARE @ScheduleID uniqueidentifier;

SELECT @ScheduleID = RS.ScheduleID
FROM ReportServer..[Catalog] AS C
INNER JOIN ReportServer..ReportSchedule AS RS
ON C.ItemID = RS.ReportID
WHERE C.Path = @ReportPath

IF @ScheduleID IS NULL
BEGIN
	RAISERROR(N'No subscription schedule found for report %s',16,1,@ReportPath);
	RETURN -1;
END

DECLARE @DataDrivenConfig AS TABLE (ToList nvarchar(2000), CcList nvarchar(2000), BccList nvarchar(2000), IncludeReport bit, RenderFormat varchar(50), [Priority] nvarchar(10), [Subject] nvarchar(1000), Comment nvarchar(max), IncludeLink bit)

INSERT INTO @DataDrivenConfig
EXEC dbo.ReportConfigGet @ReportConfigID

IF @@ROWCOUNT = 0
BEGIN
	RAISERROR(N'No Report Config found for ID %d',16,1,@ReportConfigID);
	RETURN -1;
END

DECLARE
	@ToList nvarchar(2000),
	@CcList nvarchar(2000),
	@BccList nvarchar(2000),
	@IncludeReport bit,
	@RenderFormat nvarchar(50),
	@Priority nvarchar(10),
	@Subject nvarchar(1000),
	@Comment nvarchar(max),
	@IncludeLink bit

SELECT
	@ToList			= ToList,
	@CcList			= CcList,
	@BccList		= BccList,
	@IncludeReport	= IncludeReport,
	@RenderFormat	= RenderFormat,
	@Priority		= [Priority],
	@Subject		= [Subject],
	@Comment		= Comment,
	@IncludeLink	= IncludeLink
FROM @DataDrivenConfig

EXEC ReportServer.dbo.[data_driven_subscription]
						 @scheduleID	= @ScheduleID
						,@emailTO		= @ToList
						,@emailCC		= @CcList
						,@emailBCC		= @BccList
						,@IncludeReport	= @IncludeReport
						,@renderFormat	= @RenderFormat
						,@Priority		= @Priority
						,@sub			= @Subject
						,@emailBODY		= @Comment
						,@IncludeLink	= @IncludeLink
GO

## Data Driven Alternative Usage Example File Share.sql
DECLARE
	@ReportName nvarchar(4000),
	@ReportPath nvarchar(4000),
	@ScheduleID uniqueidentifier;

SELECT @ScheduleID = RS.ScheduleID
FROM ReportServer..[Catalog] AS C
INNER JOIN ReportServer..ReportSchedule AS RS
ON C.ItemID = RS.ReportID
WHERE C.Path = N'/PeriodicReports/Monthly Report'

SELECT
@ReportName = N'Monthly Report ' + CONVERT(NVARCHAR(6),DATEADD(MM,-1,GETDATE()),112),
@ReportPath = N'\\CorporateNas\Public\MonthlyReports'

EXEC ReportServer.dbo.data_driven_subscription_file_share
		@scheduleID = @ScheduleID
		, @FileName = @ReportName
		, @FilePath = @ReportPath
		, @renderFormat = 'PDF'
		, @WriteMode = 'OverWrite'
GO
	-- Based on script by Jason Selburg
	-- https://www.sqlservercentral.com/Forums/Topic279460-150-1.aspx
	-- http://www.sqlservercentral.com/scripts/Miscellaneous/31733/
	USE ReportServer
	GO

	IF OBJECT_ID(N'[dbo].[data_driven_subscription]', 'P') IS NOT NULL
	DROP PROCEDURE [dbo].[data_driven_subscription]
	GO

	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_NULLS ON
	GO

	CREATE PROCEDURE dbo.data_driven_subscription
	(
	@scheduleID uniqueidentifier,
	@emailTO nvarchar (2000) = ' ',
	@emailCC nvarchar (2000) = ' ',
	@emailBCC nvarchar (2000) = ' ',
	@emailReplyTO nvarchar (2000) = ' ',
	@emailBODY nvarchar(max) = ' ',
	@sub nvarchar(1000) = ' ',
	@renderFormat nvarchar(50) = 'PDF',
	@IncludeReport bit = 0,
	@IncludeLink bit = 0,
	@Priority nvarchar(10) = 'NORMAL',
	@param1 nvarchar (256) = ' ',
	@p1 nvarchar(4000) = NULL
	)

	AS

	DECLARE
	@ptrval binary(16),
	@PARAMptrval binary(16),
	@TOpos int,
	@CCpos int,
	@BCCpos int,
	@RTpos int,
	@BODYpos int,
	@PARAM1Pos int,
	@length int,
	@subscriptionID uniqueidentifier,
	@job_status int,
	@I int, -- the rest were added by hugh
	@starttime datetime,
	@lastruntime datetime,
	@execTime datetime,
	@dValues nvarchar (max),
	@pValues nvarchar (max)

	set @starttime = DATEADD(second, -2, getdate())
	set @job_status = 1
	set @I = 1
	set @emailTO = rtrim(@emailTO)
	set @emailCC = rtrim(@emailCC)
	set @emailBCC = rtrim(@emailBCC)
	set @emailReplyTO = rtrim(@emailReplyTO)
	set @emailBODY = rtrim(@emailBODY)
	set @param1 = rtrim(@param1)
	set @Priority = rtrim(@Priority)
	set @renderFormat = rtrim(@renderFormat)


	-- set the subscription ID
	SELECT @subscriptionID = SubscriptionID
	FROM ReportSchedule WHERE ScheduleID = @scheduleID


	set @dValues = ''
	set @pValues = ''


	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 = @dValues + '<ParameterValue><Name>Priority</Name><Value>' + IsNull(NullIf(@Priority,''), 'NORMAL') + '</Value></ParameterValue>'

	if IsNull(@dValues, '') <> ''
	set @dValues = @dValues + '<ParameterValue><Name>IncludeReport</Name><Value>' + CASE WHEN @IncludeReport = 1 THEN 'True' ELSE 'False' END + '</Value></ParameterValue>'

	if IsNull(@dValues, '') <> ''
	set @dValues = @dValues + '<ParameterValue><Name>IncludeLink</Name><Value>' + CASE WHEN @IncludeLink = 1 THEN 'True' ELSE 'False' END + '</Value></ParameterValue>'

	if IsNull(@dValues, '') <> ''
	set @dValues = '<ParameterValues>' + @dValues + '<ParameterValue><Name>RenderFormat</Name><Value>' + IsNull(@renderFormat, 'PDF') + '</Value></ParameterValue>' +
	'</ParameterValues>'


	if IsNull(@p1, '') <> '' and IsNull(@param1, '') <> ''
	set @pValues = '<ParameterValues><ParameterValue><Name>' +
	@p1 +
	'</Name><Value>' +
	@param1 +
	'</Value></ParameterValue></ParameterValues>'
	ELSE
	set @pValues = '<ParameterValues/>'

	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
	GO
	SET QUOTED_IDENTIFIER OFF
	GO
	SET ANSI_NULLS ON
	GO

	----------------------------------------------------------
	/* Use .WRITE instead of UPDATETEXT
	The following example uses the .WRITE clause to update a partial value in DocumentSummary,
	an nvarchar(max) column in the Production.Document table.
	The word components is replaced with the word features by specifying the replacement word,
	the starting location (offset) of the word to be replaced in the existing data,
	and the number of characters to be replaced (length).
	The example also uses the OUTPUT clause to return the before and after images of the DocumentSummary column to the @MyTableVar table variable.

	USE AdventureWorks2012;
	GO
	DECLARE @MyTableVar table (
	SummaryBefore nvarchar(max),
	SummaryAfter nvarchar(max));
	UPDATE Production.Document
	SET DocumentSummary .WRITE (N'features',28,10)
	OUTPUT deleted.DocumentSummary,
	inserted.DocumentSummary
	INTO @MyTableVar
	WHERE Title = N'Front Reflector Bracket Installation';
	SELECT SummaryBefore, SummaryAfter
	FROM @MyTableVar;
	GO*/

	-- Based on script by Jason Selburg
	-- https://www.sqlservercentral.com/Forums/Topic279460-150-1.aspx
	-- http://www.sqlservercentral.com/scripts/Miscellaneous/31733/
	USE ReportServer
	GO

	IF OBJECT_ID(N'[dbo].[data_driven_subscription_file_share]', 'P') IS NOT NULL
	DROP PROCEDURE [dbo].[data_driven_subscription_file_share]
	GO

	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_NULLS ON
	GO

	CREATE PROCEDURE dbo.data_driven_subscription_file_share
	(
	@scheduleID uniqueidentifier,
	@FileName nvarchar (2000) = ' ',
	@FilePath nvarchar (2000) = ' ',
	@renderFormat nvarchar(50) = 'PDF',
	@WriteMode nvarchar (20) = 'OverWrite'
	)

	AS

	DECLARE
	@ptrval binary(16),
	@subscriptionID uniqueidentifier,
	@job_status int,
	@I int, -- the rest were added by hugh
	@starttime datetime,
	@lastruntime datetime,
	@execTime datetime,
	@dValues nvarchar (max),
	@UserName nvarchar(4000),
	@Password nvarchar(4000),
	@CurrentExtensionSettings XML

	set @starttime = DATEADD(second, -2, getdate())
	set @job_status = 1
	set @I = 1
	set @FileName = rtrim(@FileName)
	set @FilePath = rtrim(@FilePath)
	set @renderFormat = rtrim(@renderFormat)


	-- set the subscription ID
	SELECT @subscriptionID = SubscriptionID
	FROM ReportSchedule WHERE ScheduleID = @scheduleID

	SELECT @CurrentExtensionSettings = CONVERT(xml,extensionsettings)
	FROM Subscriptions WHERE SubscriptionID = @SubscriptionID

	SELECT @UserName = p.[USERNAME], @Password = p.[PASSWORD]
	FROM
	(
	SELECT
	X.query('.').value('(ParameterValue/Name/text())[1]', 'nvarchar(4000)') AS Nam,
	X.query('.').value('(ParameterValue/Value/text())[1]', 'nvarchar(4000)') AS Val
	FROM @CurrentExtensionSettings.nodes('ParameterValues/ParameterValue') as T(X)
	WHERE X.query('.').value('(ParameterValue/Name/text())[1]', 'nvarchar(4000)') IN ('USERNAME', 'PASSWORD')
	) AS q
	pivot (max(Val) for Nam in ([USERNAME], [PASSWORD])) p


	set @dValues = ''


	if IsNull(@FileName, '') <> ''
	set @dValues = @dValues + '<ParameterValue><Name>FILENAME</Name><Value>' + @FileName + '</Value></ParameterValue>'

	if IsNull(@FilePath, '') <> ''
	set @dValues = @dValues + '<ParameterValue><Name>PATH</Name><Value>' + @FilePath + '</Value></ParameterValue>'

	if IsNull(@UserName, '') <> ''
	set @dValues = @dValues + '<ParameterValue><Name>USERNAME</Name><Value>' + @UserName + '</Value></ParameterValue>'

	if IsNull(@Password, '') <> ''
	set @dValues = @dValues + '<ParameterValue><Name>PASSWORD</Name><Value>' + @Password + '</Value></ParameterValue>'

	if IsNull(@dValues, '') <> ''
	set @dValues = @dValues + '<ParameterValue><Name>WRITEMODE</Name><Value>' + IsNull(NullIf(@WriteMode,''), 'OverWrite') + '</Value></ParameterValue>'

	if IsNull(@dValues, '') <> ''
	set @dValues = '<ParameterValues>' + @dValues + '<ParameterValue><Name>RENDER_FORMAT</Name><Value>' + IsNull(@renderFormat, 'PDF') + '</Value></ParameterValue>' +
	'</ParameterValues>'

	if IsNull(@dValues, '') <> ''
	BEGIN

	update Subscriptions set extensionsettings = '' 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

	-- 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
	GO
	SET QUOTED_IDENTIFIER OFF
	GO
	SET ANSI_NULLS ON
	GO

	----------------------------------------------------------
	/* Use .WRITE instead of UPDATETEXT
	The following example uses the .WRITE clause to update a partial value in DocumentSummary,
	an nvarchar(max) column in the Production.Document table.
	The word components is replaced with the word features by specifying the replacement word,
	the starting location (offset) of the word to be replaced in the existing data,
	and the number of characters to be replaced (length).
	The example also uses the OUTPUT clause to return the before and after images of the DocumentSummary column to the @MyTableVar table variable.

	USE AdventureWorks2012;
	GO
	DECLARE @MyTableVar table (
	SummaryBefore nvarchar(max),
	SummaryAfter nvarchar(max));
	UPDATE Production.Document
	SET DocumentSummary .WRITE (N'features',28,10)
	OUTPUT deleted.DocumentSummary,
	inserted.DocumentSummary
	INTO @MyTableVar
	WHERE Title = N'Front Reflector Bracket Installation';
	SELECT SummaryBefore, SummaryAfter
	FROM @MyTableVar;
	GO*/
	USE AgenciesReports
	GO

	IF OBJECT_ID(N'[dbo].[RunPseudoDataDrivenReport]', 'P') IS NOT NULL
	DROP PROCEDURE [dbo].[RunPseudoDataDrivenReport]
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_NULLS ON
	GO
	/*
	-- Sample usage:
	EXEC [RunPseudoDataDrivenReport] @ReportConfigID = 56, @ReportPath = N'/PeriodicReports/Monthly Report'
	*/
	CREATE PROCEDURE dbo.[RunPseudoDataDrivenReport]
	@ReportConfigID INT,
	@ReportPath NVARCHAR(850)
	AS
	DECLARE @ScheduleID uniqueidentifier;

	SELECT @ScheduleID = RS.ScheduleID
	FROM ReportServer..[Catalog] AS C
	INNER JOIN ReportServer..ReportSchedule AS RS
	ON C.ItemID = RS.ReportID
	WHERE C.Path = @ReportPath

	IF @ScheduleID IS NULL
	BEGIN
	RAISERROR(N'No subscription schedule found for report %s',16,1,@ReportPath);
	RETURN -1;
	END

	DECLARE @DataDrivenConfig AS TABLE (ToList nvarchar(2000), CcList nvarchar(2000), BccList nvarchar(2000), IncludeReport bit, RenderFormat varchar(50), [Priority] nvarchar(10), [Subject] nvarchar(1000), Comment nvarchar(max), IncludeLink bit)

	INSERT INTO @DataDrivenConfig
	EXEC dbo.ReportConfigGet @ReportConfigID

	IF @@ROWCOUNT = 0
	BEGIN
	RAISERROR(N'No Report Config found for ID %d',16,1,@ReportConfigID);
	RETURN -1;
	END

	DECLARE
	@ToList nvarchar(2000),
	@CcList nvarchar(2000),
	@BccList nvarchar(2000),
	@IncludeReport bit,
	@RenderFormat nvarchar(50),
	@Priority nvarchar(10),
	@Subject nvarchar(1000),
	@Comment nvarchar(max),
	@IncludeLink bit

	SELECT
	@ToList = ToList,
	@CcList = CcList,
	@BccList = BccList,
	@IncludeReport = IncludeReport,
	@RenderFormat = RenderFormat,
	@Priority = [Priority],
	@Subject = [Subject],
	@Comment = Comment,
	@IncludeLink = IncludeLink
	FROM @DataDrivenConfig

	EXEC ReportServer.dbo.[data_driven_subscription]
	@scheduleID = @ScheduleID
	,@emailTO = @ToList
	,@emailCC = @CcList
	,@emailBCC = @BccList
	,@IncludeReport = @IncludeReport
	,@renderFormat = @RenderFormat
	,@Priority = @Priority
	,@sub = @Subject
	,@emailBODY = @Comment
	,@IncludeLink = @IncludeLink
	GO
	DECLARE
	@ReportName nvarchar(4000),
	@ReportPath nvarchar(4000),
	@ScheduleID uniqueidentifier;

	SELECT @ScheduleID = RS.ScheduleID
	FROM ReportServer..[Catalog] AS C
	INNER JOIN ReportServer..ReportSchedule AS RS
	ON C.ItemID = RS.ReportID
	WHERE C.Path = N'/PeriodicReports/Monthly Report'

	SELECT
	@ReportName = N'Monthly Report ' + CONVERT(NVARCHAR(6),DATEADD(MM,-1,GETDATE()),112),
	@ReportPath = N'\\CorporateNas\Public\MonthlyReports'

	EXEC ReportServer.dbo.data_driven_subscription_file_share
	@scheduleID = @ScheduleID
	, @FileName = @ReportName
	, @FilePath = @ReportPath
	, @renderFormat = 'PDF'
	, @WriteMode = 'OverWrite'
	GO