February 18, 2019 at 5:30 am
Hi,
I am trying to send SSRS notification to recipients when data exists in report with the code below . The subscription works if I execute manually from the reporting server . When I try to execute from SQL agent job the job fails . Please help .
Error : Executed as user: USER$. Incorrect syntax near the keyword 'EXEC'. [SQLSTATE 42000] (Error 156). The step failed.
Code :
/* Subscription identifier of the report that must be data driven */
/* PLEASE USE YOUR Subscription Id */
declare @SubscriptionID nvarchar(50) = '46BE202C-EF24-43BC-8951-12B29E98540B'
/* Variables to backup original subscription params */
declare @ExtensionSettingsBackup nvarchar(max), @ParametersBackup nvarchar(max)
/* Store original subscription values */
select *--@ExtensionSettingsBackup=[ExtensionSettings], @ParametersBackup=[Parameters]
from [ReportServer].dbo.Subscriptions
where SubscriptionID=@SubscriptionID
/* Variables for data driven simulation */
declare @EmailAddress nvarchar(max)
/* Prepare dataset for simulation */
/* To test report, send to ourself */
declare data_driven cursor local static forward_only for
EXEC DBNAME.SCHEMANAME.usp_rpt_SSRS_Gaps
open data_driven
/* Get first values drom dataset */
fetch next from data_driven into @EmailAddress
while @@fetch_status=0 begin
/* Update subscription with values obtained from dataset */
update [ReportServer].dbo.Subscriptions set
/* This column is used for delivery module configuration - for e-mail it is "TO", "CC", "BCC" etc. */
ExtensionSettings=
N'<ParameterValues>
<ParameterValue><Name>TO</Name><Value>'+@EmailAddress+N'</Value></ParameterValue>
<ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue>
<ParameterValue><Name>RenderFormat</Name><Value>MHTML</Value></ParameterValue>
<ParameterValue><Name>Subject</Name><Value>@ReportName was executed at @ExecutionTime</Value></ParameterValue>
<ParameterValue><Name>IncludeLink</Name><Value>True</Value></ParameterValue>
<ParameterValue><Name>Priority</Name><Value>NORMAL</Value></ParameterValue>
</ParameterValues>'
where [SubscriptionID]=@SubscriptionID
/* Ask SSRS to process the Report with updated subscription parameters */
exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData=@SubscriptionID
/* Wait while SSRS processed Report execution */
while exists(select top 1 1 from [ReportServer].dbo.[Event] (nolock) where [EventData]=@SubscriptionID) WAITFOR DELAY '00:00:02'
/* Get next values from dataset */
fetch next from data_driven into @EmailAddress
end
close data_driven
deallocate data_driven
/* Restore original params for subscription */
update [ReportServer].dbo.Subscriptions set
[ExtensionSettings]=@ExtensionSettingsBackup
,[Parameters]=@ParametersBackup
where [SubscriptionID]=@SubscriptionID Thanks,
PSB
February 18, 2019 at 3:59 pm
PSB - Monday, February 18, 2019 5:30 AMdeclare data_driven cursor local static forward_only forEXEC DBNAME.SCHEMANAME.usp_rpt_SSRS_Gaps
open data_driven
I would guess it's this EXEC part throwing the error. Not sure how that's working manually but try changing that to an INSERT INTO...EXEC and insert into table, table variable, temp table,
Did you try any of the other alternatives rather than updating the subscription table? Updating the table isn't supported and can be dangerous in this context. Other alternatives often used are to base the report on a stored procedure and at the end of the stored procedure, raise an error when there is no data.
Or in the query used to get the information for the data driven subscription, add a where clause checking for the existence of data for the report using whatever parameters would be used for the report. There is some more information in this blog post and some other suggestions in the comments for the post:
How to prevent empty reports from being sent as part of a SQL Reporting Services subscription
Sue
February 19, 2019 at 1:15 pm
The procedure just checks if there is any data returned by the view then send to Person A else Send to person B.
ALTER PROCEDURE [schemaname].[usp_rpt_SSRS_MitigationGaps]
AS
BEGIN
IF EXISTS ( SELECT TOP 1 1 AS RecordCount FROM [schemaname].[vwName] )
BEGIN
SELECT 'email1@xxx.com' AS [ResourceEmailAddress]
END
ELSE
SELECT 'email2@xxx.com' AS [ResourceEmailAddress]
END
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply