SQL Server 2005 Standard (9.0.1399)
Windows Server 2003 Standard (SP2 5.2.3790)
If I am posting this in the wrong forum, please let me know. Any help will be greatly appreciated.
I am using a trigger and a stored procedure to fire a single parameter report in SSRS. When data is inserted to the table, a separate report is run for each "buyer" referenced by the data.
The stored procedure loops through the buyers from the inserted data and changes the parameter in the Subscriptions table in the ReportServer database and then fires the job to send the report.
When I manually execute the stored procedure using a test table, the reports are generated and sent properly.
When I perform an insert on the live table, the trigger executes properly but does not change the parameters in the Subscriptions table in the ReportServer database. When the report job is fired, the subscription fails and the InactiveFlags column is set to "16". I have used the PRINT command to output the parameter and counter every time the procedure loops which has shown the stored procedure is operating properly in that respect.
I have created and tried the stored procedure on the ReportServer database and the Merchandising database that holds the data.
The account running the stored procedure is a sysAdmin account and a member of the RSexecRole on the ReportServer database.
The stored procedure:
USE [ReportServer]
GO
/****** Object: StoredProcedure [dbo].[ExecuteSubscribedReport_depletingInventory] Script Date: 05/12/2009 09:28:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
ExecuteSubscribedReport
PROCEDURE DESCRIPTION:
Creates the effect of a data driven subscription by replacing the fields in
an existing subscription with the supplied values, executing the report
and then replacing the original values.
INPUT:
@ScheduleID The Job Name in SQL Server
@EmailTo The TO address of the email
@EmailCC The Carbon Copy address of the email
@EmailBCC The Blind Copy address of the email
@EmailReplyTo The Reply TO address of the email
@EmailBody Any text that you want in the email body
@EmailFormat Choices: PDF, EXCEL, MHXML, TIFF, CSV
@ParameterName The name of the report parameter
@ParameterValue The value of the report parameter
WRITTEN BY:
Scott Naylor. Adapted from Greg Low's version at his Bit Bucket Blog.
Greg Low. Based on a concept from Jason L. Selburg at CodeProject.com
*/
CREATE PROCEDURE [dbo].[ExecuteSubscribedReport_depletingInventory]
( @ScheduleID uniqueidentifier
, @EmailTo varchar (1000) = NULL
, @EmailCC varchar (1000) = NULL
, @EmailBCC varchar (1000) = NULL
, @EmailReplyTo varchar (1000) = NULL
, @EmailBody varchar (8000) = NULL
, @EmailSubject varchar (1000) = NULL
, @EmailFormat varchar (5) = NULL
, @ParameterName varchar (1000) = NULL
, @ParameterValue varchar (1000) = NULL
)
with EXECUTE AS 'na\svc_ismjb031'
AS
BEGIN
DECLARE @extensionSettingsPointer binary(16),
@parametersPointer binary(16),
@subscriptionID uniqueidentifier,
@originalExtensionSettings varchar(8000),
@originalParameters varchar(8000),
@newExtensionSettings varchar(8000),
@newParameters varchar(8000);
-- wait for the subscription system
WHILE EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name = '##ReportInUse')
WAITFOR DELAY '00:00:15';
CREATE TABLE ##ReportInUse (ReportID int);
-- find the subscriptionID
SELECT @subscriptionID = SubscriptionID
FROM dbo.ReportSchedule
WHERE ScheduleID = @ScheduleID;
-- save away the original values of ExtensionSettings and Parameters
SELECT @originalExtensionSettings = CAST(ExtensionSettings AS varchar(8000)),
@originalParameters = CAST(Parameters AS varchar(8000))
FROM dbo.Subscriptions
WHERE SubscriptionID = @subscriptionID;
SET @newExtensionSettings = @originalExtensionSettings;
SET @newParameters = @originalParameters;
-- process arguments. A blank entry for format will default to PDF
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|TO|',COALESCE(@EmailTo,''));
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|CC|',COALESCE(@EmailCC,''));
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|BC|',COALESCE(@EmailBCC,''));
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|RT|',COALESCE(@EmailReplyTo,''));
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|BD|',COALESCE(@EmailBody,''));
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|Subject|',COALESCE(@EmailSubject,''));
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'PDF',COALESCE(@EmailFormat,'PDF'));
SET @newParameters = REPLACE(@newParameters,'|PN1|', COALESCE(@ParameterName,''));
SET @newParameters = REPLACE(@newParameters,'|PV1|', COALESCE(@ParameterValue,''));
-- Temporarily update the values
UPDATE dbo.Subscriptions
SET ExtensionSettings = CAST(@newExtensionSettings AS ntext),
Parameters = CAST(@newParameters AS ntext)
WHERE SubscriptionID = @subscriptionID;
-- run the job
EXEC msdb..sp_start_job @job_name = @ScheduleID
-- make enough delay for the report to have started
WAITFOR DELAY '00:00:15'
-- put the original extensionsettings and parameter values back
UPDATE dbo.Subscriptions
SET ExtensionSettings = CAST(@originalExtensionSettings AS ntext),
Parameters = CAST(@originalParameters AS ntext)
WHERE SubscriptionID = @subscriptionID;
-- free up the subscription system for another person to use
DROP TABLE ##ReportInUse;
END;
The execute statement when running the stored procedure manually. Once again, this works properly:
DECLARE
@EmailTo_t varchar (1000)
, @ParameterValue_t varchar (1000)
, @buyerNbr int
, @buyerCounter int
,@ScheduleID varchar(50)
, @EmailTo varchar(500)
, @EmailCC varchar(500)
, @EmailBCC varchar(500)
, @EmailReplyTo varchar(500)
, @EmailBody varchar(500)
, @EmailSubject varchar(500)
, @EmailFormat varchar (500)
, @ParameterName varchar (500)
, @ParameterValue varchar (500)
/*
Insert buyers with new data into temp table
*/
SELECT DISTINCT DENSE_RANK() OVER (ORDER BY i.buyer)as rankBuyer
, i.buyer
, i.buyerEmail
INTO #tempBuyersWithData
FROM merchandising.dbo.BKUP_tblDepletingInventory i
SET @buyerNbr = (Select MAX(rankBuyer)
FROM #tempBuyersWithData);
SET @buyerCounter = 1;
/*
Loop through temp table to run reports for the different buyers
*/
WHILE @buyerCounter <= @buyerNbr
BEGIN
SET @EmailTo_t = (SELECT buyerEmail
FROM #tempBuyersWithData
WHERE rankBuyer = @buyerCounter);
SET @ParameterValue_t = (SELECT buyer
FROM #tempBuyersWithData
WHERE rankBuyer = @buyerCounter);
EXEC Merchandising.dbo.ExecuteSubscribedReport_depletingInventory
@ScheduleID = 'c00f205e-cec2-45ed-a893-64a85ac7cc4f'
,@EmailTo = @EmailTo_t
,@EmailCC = ''
,@EmailBCC = ''
,@EmailReplyTo = 'naylor.scott@xxxx.com'
,@EmailBody = 'TEST'
,@EmailSubject = 'TEST'
,@EmailFormat = 'MHTML'
,@ParameterName = 'buyer'
,@ParameterValue = @ParameterValue_t;
SET @buyerCounter = @buyerCounter +1;
END
DROP TABLE #tempBuyersWithData
Here is the trigger that loops properly, but does not write the changes to the Subscriptions table:
USE [Merchandising]
GO
/****** Object: Trigger [sendDepletingInventoryReports] Script Date: 05/12/2009 09:43:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [sendDepletingInventoryReports]
ON [Merchandising].[dbo].[tblDepletingInventory]
with EXECUTE AS 'svc_ismjb031'
AFTER INSERT
AS
BEGIN
DECLARE @buyerNbr int
, @buyerCounter int
, @EmailTo_t varchar (1000)
, @ParameterValue_t varchar (1000)
,@ScheduleID varchar(50)
, @EmailTo varchar(500)
, @EmailCC varchar(500)
, @EmailBCC varchar(500)
, @EmailReplyTo varchar(500)
, @EmailBody varchar(500)
, @EmailSubject varchar(500)
, @EmailFormat varchar (500)
, @ParameterName varchar (500)
, @ParameterValue varchar (500)
/*
Insert buyers with new data into temp table
*/
SELECT DISTINCT DENSE_RANK() OVER (ORDER BY i.buyer)as rankBuyer
, i.buyer
, i.buyerEmail
INTO #tempBuyersWithData
FROM inserted i;
SET @buyerNbr = (Select MAX(rankBuyer)
FROM #tempBuyersWithData);
SET @buyerCounter = 1;
/*
Start loop to go through temp table. Set variables for stored procedure.
*/
WHILE @buyerCounter <= @buyerNbr
BEGIN
SET @EmailTo_t = (SELECT buyerEmail
FROM #tempBuyersWithData
WHERE rankBuyer = @buyerCounter);
SET @ParameterValue_t = (SELECT buyer
FROM #tempBuyersWithData
WHERE rankBuyer = @buyerCounter);
EXEC Merchandising.dbo.ExecuteSubscribedReport_depletingInventory
@ScheduleID = 'c00f205e-cec2-45ed-a893-64a85ac7cc4f'
,@EmailTo = @EmailTo_t
,@EmailCC = ''
,@EmailBCC = ''
,@EmailReplyTo = 'naylor.scott@xxxx.com'
,@EmailBody = 'TEST'
,@EmailSubject = 'TEST'
,@EmailFormat = 'MHTML'
,@ParameterName = 'buyer'
,@ParameterValue = @ParameterValue_t;
SET @buyerCounter = @buyerCounter +1
PRINT @ParameterValue_t + ' ' + @EmailTo_t + ' ' + CAST(@buyerCounter as varchar)
END;
DROP TABLE #tempBuyersWithData
END;
Windows Server 2003 Standard (SP2 5.2.3790)
If I am posting this in the wrong forum, please let me know. Any help will be greatly appreciated.
I am using a trigger and a stored procedure to fire a single parameter report in SSRS. When data is inserted to the table, a separate report is run for each "buyer" referenced by the data.
The stored procedure loops through the buyers from the inserted data and changes the parameter in the Subscriptions table in the ReportServer database and then fires the job to send the report.
When I manually execute the stored procedure using a test table, the reports are generated and sent properly.
When I perform an insert on the live table, the trigger executes properly but does not change the parameters in the Subscriptions table in the ReportServer database. When the report job is fired, the subscription fails and the InactiveFlags column is set to "16". I have used the PRINT command to output the parameter and counter every time the procedure loops which has shown the stored procedure is operating properly in that respect.
I have created and tried the stored procedure on the ReportServer database and the Merchandising database that holds the data.
The account running the stored procedure is a sysAdmin account and a member of the RSexecRole on the ReportServer database.
The stored procedure:
USE [ReportServer]
GO
/****** Object: StoredProcedure [dbo].[ExecuteSubscribedReport_depletingInventory] Script Date: 05/12/2009 09:28:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
ExecuteSubscribedReport
PROCEDURE DESCRIPTION:
Creates the effect of a data driven subscription by replacing the fields in
an existing subscription with the supplied values, executing the report
and then replacing the original values.
INPUT:
@ScheduleID The Job Name in SQL Server
@EmailTo The TO address of the email
@EmailCC The Carbon Copy address of the email
@EmailBCC The Blind Copy address of the email
@EmailReplyTo The Reply TO address of the email
@EmailBody Any text that you want in the email body
@EmailFormat Choices: PDF, EXCEL, MHXML, TIFF, CSV
@ParameterName The name of the report parameter
@ParameterValue The value of the report parameter
WRITTEN BY:
Scott Naylor. Adapted from Greg Low's version at his Bit Bucket Blog.
Greg Low. Based on a concept from Jason L. Selburg at CodeProject.com
*/
CREATE PROCEDURE [dbo].[ExecuteSubscribedReport_depletingInventory]
( @ScheduleID uniqueidentifier
, @EmailTo varchar (1000) = NULL
, @EmailCC varchar (1000) = NULL
, @EmailBCC varchar (1000) = NULL
, @EmailReplyTo varchar (1000) = NULL
, @EmailBody varchar (8000) = NULL
, @EmailSubject varchar (1000) = NULL
, @EmailFormat varchar (5) = NULL
, @ParameterName varchar (1000) = NULL
, @ParameterValue varchar (1000) = NULL
)
with EXECUTE AS 'na\svc_ismjb031'
AS
BEGIN
DECLARE @extensionSettingsPointer binary(16),
@parametersPointer binary(16),
@subscriptionID uniqueidentifier,
@originalExtensionSettings varchar(8000),
@originalParameters varchar(8000),
@newExtensionSettings varchar(8000),
@newParameters varchar(8000);
-- wait for the subscription system
WHILE EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name = '##ReportInUse')
WAITFOR DELAY '00:00:15';
CREATE TABLE ##ReportInUse (ReportID int);
-- find the subscriptionID
SELECT @subscriptionID = SubscriptionID
FROM dbo.ReportSchedule
WHERE ScheduleID = @ScheduleID;
-- save away the original values of ExtensionSettings and Parameters
SELECT @originalExtensionSettings = CAST(ExtensionSettings AS varchar(8000)),
@originalParameters = CAST(Parameters AS varchar(8000))
FROM dbo.Subscriptions
WHERE SubscriptionID = @subscriptionID;
SET @newExtensionSettings = @originalExtensionSettings;
SET @newParameters = @originalParameters;
-- process arguments. A blank entry for format will default to PDF
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|TO|',COALESCE(@EmailTo,''));
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|CC|',COALESCE(@EmailCC,''));
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|BC|',COALESCE(@EmailBCC,''));
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|RT|',COALESCE(@EmailReplyTo,''));
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|BD|',COALESCE(@EmailBody,''));
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|Subject|',COALESCE(@EmailSubject,''));
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'PDF',COALESCE(@EmailFormat,'PDF'));
SET @newParameters = REPLACE(@newParameters,'|PN1|', COALESCE(@ParameterName,''));
SET @newParameters = REPLACE(@newParameters,'|PV1|', COALESCE(@ParameterValue,''));
-- Temporarily update the values
UPDATE dbo.Subscriptions
SET ExtensionSettings = CAST(@newExtensionSettings AS ntext),
Parameters = CAST(@newParameters AS ntext)
WHERE SubscriptionID = @subscriptionID;
-- run the job
EXEC msdb..sp_start_job @job_name = @ScheduleID
-- make enough delay for the report to have started
WAITFOR DELAY '00:00:15'
-- put the original extensionsettings and parameter values back
UPDATE dbo.Subscriptions
SET ExtensionSettings = CAST(@originalExtensionSettings AS ntext),
Parameters = CAST(@originalParameters AS ntext)
WHERE SubscriptionID = @subscriptionID;
-- free up the subscription system for another person to use
DROP TABLE ##ReportInUse;
END;
The execute statement when running the stored procedure manually. Once again, this works properly:
DECLARE
@EmailTo_t varchar (1000)
, @ParameterValue_t varchar (1000)
, @buyerNbr int
, @buyerCounter int
,@ScheduleID varchar(50)
, @EmailTo varchar(500)
, @EmailCC varchar(500)
, @EmailBCC varchar(500)
, @EmailReplyTo varchar(500)
, @EmailBody varchar(500)
, @EmailSubject varchar(500)
, @EmailFormat varchar (500)
, @ParameterName varchar (500)
, @ParameterValue varchar (500)
/*
Insert buyers with new data into temp table
*/
SELECT DISTINCT DENSE_RANK() OVER (ORDER BY i.buyer)as rankBuyer
, i.buyer
, i.buyerEmail
INTO #tempBuyersWithData
FROM merchandising.dbo.BKUP_tblDepletingInventory i
SET @buyerNbr = (Select MAX(rankBuyer)
FROM #tempBuyersWithData);
SET @buyerCounter = 1;
/*
Loop through temp table to run reports for the different buyers
*/
WHILE @buyerCounter <= @buyerNbr
BEGIN
SET @EmailTo_t = (SELECT buyerEmail
FROM #tempBuyersWithData
WHERE rankBuyer = @buyerCounter);
SET @ParameterValue_t = (SELECT buyer
FROM #tempBuyersWithData
WHERE rankBuyer = @buyerCounter);
EXEC Merchandising.dbo.ExecuteSubscribedReport_depletingInventory
@ScheduleID = 'c00f205e-cec2-45ed-a893-64a85ac7cc4f'
,@EmailTo = @EmailTo_t
,@EmailCC = ''
,@EmailBCC = ''
,@EmailReplyTo = 'naylor.scott@xxxx.com'
,@EmailBody = 'TEST'
,@EmailSubject = 'TEST'
,@EmailFormat = 'MHTML'
,@ParameterName = 'buyer'
,@ParameterValue = @ParameterValue_t;
SET @buyerCounter = @buyerCounter +1;
END
DROP TABLE #tempBuyersWithData
Here is the trigger that loops properly, but does not write the changes to the Subscriptions table:
USE [Merchandising]
GO
/****** Object: Trigger [sendDepletingInventoryReports] Script Date: 05/12/2009 09:43:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [sendDepletingInventoryReports]
ON [Merchandising].[dbo].[tblDepletingInventory]
with EXECUTE AS 'svc_ismjb031'
AFTER INSERT
AS
BEGIN
DECLARE @buyerNbr int
, @buyerCounter int
, @EmailTo_t varchar (1000)
, @ParameterValue_t varchar (1000)
,@ScheduleID varchar(50)
, @EmailTo varchar(500)
, @EmailCC varchar(500)
, @EmailBCC varchar(500)
, @EmailReplyTo varchar(500)
, @EmailBody varchar(500)
, @EmailSubject varchar(500)
, @EmailFormat varchar (500)
, @ParameterName varchar (500)
, @ParameterValue varchar (500)
/*
Insert buyers with new data into temp table
*/
SELECT DISTINCT DENSE_RANK() OVER (ORDER BY i.buyer)as rankBuyer
, i.buyer
, i.buyerEmail
INTO #tempBuyersWithData
FROM inserted i;
SET @buyerNbr = (Select MAX(rankBuyer)
FROM #tempBuyersWithData);
SET @buyerCounter = 1;
/*
Start loop to go through temp table. Set variables for stored procedure.
*/
WHILE @buyerCounter <= @buyerNbr
BEGIN
SET @EmailTo_t = (SELECT buyerEmail
FROM #tempBuyersWithData
WHERE rankBuyer = @buyerCounter);
SET @ParameterValue_t = (SELECT buyer
FROM #tempBuyersWithData
WHERE rankBuyer = @buyerCounter);
EXEC Merchandising.dbo.ExecuteSubscribedReport_depletingInventory
@ScheduleID = 'c00f205e-cec2-45ed-a893-64a85ac7cc4f'
,@EmailTo = @EmailTo_t
,@EmailCC = ''
,@EmailBCC = ''
,@EmailReplyTo = 'naylor.scott@xxxx.com'
,@EmailBody = 'TEST'
,@EmailSubject = 'TEST'
,@EmailFormat = 'MHTML'
,@ParameterName = 'buyer'
,@ParameterValue = @ParameterValue_t;
SET @buyerCounter = @buyerCounter +1
PRINT @ParameterValue_t + ' ' + @EmailTo_t + ' ' + CAST(@buyerCounter as varchar)
END;
DROP TABLE #tempBuyersWithData
END;