Execution of Subscribed report through Stored procedure
-
Hi,
I have a requirment where reports which are subscribed needs to executed through the Stored procedure
I mean for eg My Subscription Id
is 18EC9ADE-E538-46C0-AB78-2D1FF950CE85
so what i want to do is
EXEC 18EC9ADE-E538-46C0-AB78-2D1FF950CE85 along with passing the parameters required to generate the report
can any please help me out...
Thanking You
-
All SSRS subscriptions are SQL Server Agent Jobs. Use the following script to figure out which Agent job corresponds to which subscription. Run this against your reporting services database
SELECT
cat.[Name] AS RptName
, U.UserName
, cat.[Path]
, res.ScheduleID AS JobID
, sub.LastRuntime
, sub.LastStatus
, LEFT(CAST(sch.next_run_date AS CHAR(8)) , 4) + '-'
+ SUBSTRING(CAST(sch.next_run_date AS CHAR(8)) , 5 , 2) + '-'
+ RIGHT(CAST(sch.next_run_date AS CHAR(8)) , 2) + ' '
+ CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN '0' + LEFT(CAST(sch.next_run_time AS VARCHAR(6)) , 1)
ELSE LEFT(CAST(sch.next_run_time AS VARCHAR(6)) , 2)
END + ':'
+ CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)) , 2 , 2)
ELSE SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)) , 3 , 2)
END + ':00.000' AS NextRunTime
, CASE WHEN job.[enabled] = 1 THEN 'Enabled'
ELSE 'Disabled'
END AS JobStatus
, sub.ModifiedDate
, sub.Description
, sub.EventType
, sub.Parameters
, sub.DeliveryExtension
, sub.Version
FROM
dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON cat.ItemID = sub.Report_OID
INNER JOIN dbo.ReportSchedule AS res
ON cat.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedules AS sch
ON job.job_id = sch.job_id
INNER JOIN dbo.Users U
ON U.UserID = sub.OwnerID
ORDER BY
U.UserName
, RptName
Once you know what the appropriate agent job is, disable it. Then you can create another agent job that executes the agent job using sp_start_job. The only issue here is you will have to build your reports with default parameters.
-
Hi Daniel
Thanks for your reply
But in my requirment the parameters are bound to change at runtime
so is not possible to pass the parameters to the report at runtime
if i want to execute the subscribed report through stored procedure
-
Then try passing your run time parameters to a database table, and make your subscription a data driven subscription that can read that table for the current parameters
-
Firstly, I'd ask what is behind the requirement to run the subscription using T-SQL? That really defeats the purpose of setting up the subscription in the first place. When you create a subscription you're saying "run this report, with these parameter values, at this time".
Using a data-driven subscription, you're making the parameter values dynamic, but you would still have the SQL Agent job running the subscription at the specified time.
If you disable the SQL Agent job and run the subscription yourself, you're saying "I don't want to run the report at the time I specified". You'd also have to make sure that the job stays disabled in case someone inadvertantly enables it.
Personally, I'd put some effort into running the reports programatically via the web service. That removes the subscription, its stored parameters and the associated SQL Agent job.
This article has an example of rendering a report via the web service and saving the results to a file,
Also, @daniel-2 Bowlin, saying "All SSRS subscriptions are SQL Server Agent Jobs" is not entirely correct. The SQL Agent job is purely the triggering mechanism for the subscription. All the job does is put the subscription ID into the EventData table in the ReportServer database. Once that is done the job is complete. The Reporting Services service polls that table to find the subscriptions that need to run.
Use this instead of linking directly to the sysjobschedules table and you avoid returning duplicate rows when the SQL Agent job has many schedules,
SELECT
[job_id]
,MIN(CASE
WHEN next_run_time > 0
THEN DATEADD(ss, (CAST(next_run_time/10000 AS INT)* 3600
+ (CAST(next_run_time/100 AS INT)%100)*60
+ (next_run_time%100))
, CAST(CAST(next_run_date AS VARCHAR(8)) AS DATETIME))
WHEN next_run_date > 0
THEN CAST(CAST(next_run_date AS VARCHAR(8)) AS DATETIME)
ELSE '1900-01-01'
END
) AS NextRunTime
FROM msdb.dbo.sysjobschedules
GROUP BY [job_id]
--------------------
Colt 45 - the original point and click interface
-
philcart (7/6/2011)
Firstly, I'd ask what is behind the requirement to run the subscription using T-SQL? That really defeats the purpose of setting up the subscription in the first place. When you create a subscription you're saying "run this report, with these parameter values, at this time".Lack of a version other than Standard is the most likely cause. If you have a single report that has to be run for 10-20 "groups" via a different value for a parameter you you find yourself in the position the OP is or was in. I'm facing the same thing and I don't want to write a web-based front end because we already have something like that to setup all the details about what the report should run for and where it should go.
Cheers
-
jfogel (6/17/2013)
philcart (7/6/2011)
Firstly, I'd ask what is behind the requirement to run the subscription using T-SQL? That really defeats the purpose of setting up the subscription in the first place. When you create a subscription you're saying "run this report, with these parameter values, at this time".Lack of a version other than Standard is the most likely cause. If you have a single report that has to be run for 10-20 "groups" via a different value for a parameter you you find yourself in the position the OP is or was in. I'm facing the same thing and I don't want to write a web-based front end because we already have something like that to setup all the details about what the report should run for and where it should go.
Rendering the reports via the web service isn't a "web-based front end". Generally it's a console application that calls the appropriate methods as shown in the example url I posted.
If your requirement is to render and distribute, not display, the reports, then the web service is what you should be using.
--------------------
Colt 45 - the original point and click interface -
I'll check it out. Right now I'm thinking of a different method but I really don't want to close the door to other and possibly better options.
Cheers
-
I have same requirement
-
It is for people that have a Standard or Enterprise License trying to create Data Driven Subscriptions. Of course you can create 30+ copies of the same report with different parameters, create the subscriptions for them to run, and then call those subscriptions based on underlying data using T-SQL.
The concept that others and myself are looking for is to have a table that stores the parameters and maybe even some SQL to do what data driven subscriptions do, then assign the parameters and fire the subscription.
I could have a dozen client numbers, each having different email addresses, have a specific condition to fire a report. With a data driven subscription in BI or Enterprise, I could easily do this. It's when you do not have this feature you want a work around and I still haven't been able to find a way without actually updating the ReportServer Data Base.
As previously stated earlier and by myself, it can kind of be done, but requires you to create and manage an individual subscription for each client, setting the parameters as required. Then you can use a procedure to loop through these subscriptions, check if conditions are met to fire, and if so fire it from the stored proc. Note, when setting up each of these subscriptions you set the start and end dates of the subscription in the past.
Ideally though, you want just one subscription to manage and this requires the BI or Enterprise License for Data Driven Subscriptions.
This is what I use in many stored procs to fire subscriptions manually, note that SQL 2016 supports the descriptions field on regular (non-data driven) subscriptions, 2014 and earlier did not. The below basically just creates an agent to run the subscription. I am currently working with enterprise but would love an Express Solution so I can work with clients that do not have an enterprise license (I do a LOT with data driven subscriptions).
declare @sqlsub nvarchar(max), @fpath nvarchar(250), @desc nvarchar(250);
set @fpath = '/The/Path/To/Your/Report'
set @desc = 'Your Report Subscription Description'
select @sqlsub = N'ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' + convert(nvarchar(40), a.SubscriptionID) + ''''
from ReportServer.dbo.ReportSchedule a
join msdb.dbo.sysjobs b on convert(nvarchar(40), a.ScheduleID) = convert(nvarchar(40), b.name)
join ReportServer.dbo.ReportSchedule c on convert(nvarchar(40), b.name) = convert(nvarchar(40), c.ScheduleID)
join ReportServer.dbo.Subscriptions d on c.SubscriptionID = d.SubscriptionID
join ReportServer.dbo.Catalog e on d.report_oid = e.itemid
where upper(e.path) = upper(@fpath) and upper(d.Description) = @desc;
execute(@sqlsub); -
Standard or EXPRESS license**
-
I totally forgot about this. What I ended up doing was creating a stored procedure that generates a script that is called by the RS executable. I have another scheduled procedure that is called by an agent job. That procedure looks for work and executes, exports and delivers the report to either an email recipient, file location or both. The front-end that I created provides the user the same functionality as data-driven features. I'm basically using SSRS for execution and storage of the RDL, but not much else. It has been in production since 07/2013 and works better than expected.
Cheers
-
Are you able to assign parameters?
Let's say I have a report with @cust_id parameter, a table with the cust_id and their emails. There are 100 plus clients that COULD get the report based on criteria. The criteria is easy to check with a stored proc. Where I'm stuck is how to assign the cust_id parameter and the mail to's in the subscription.
-
Hopefully this gives you some direction, as mentioned in my prior post, I'm not using subscriptions. Instead, I'm using a home-brewed scheduling and delivery system. This is an example of a call to the stored procedure I wrote that RS.exe executes
set @ProcParamRSParam = 'ReportParameterYear:'+convert(varchar(4),@ReportParameterYear)+';ReportParameterMonth:'+convert(varchar(2),@ReportParameterMonth)+';Historical:'+@Historical+';'
exec ReportSystem.dbo.usp_GenerateReportRSS NULL, 'EXCEL', 'f:\ReportSystem\ITApps\OCBilling', 'OCBilling', '/Customer Billing/SAICOC/OC Billing Report', @ProcParamRSParam, @errormessage outputThe first parameter is for a mode. It currently isn't used so null is passed. The second param is the export file type. The third is the location where the file is exported to. the fourth is the name of the report in SSRS. The fifth is the path to the report in SSRS. The sixth is the parameter variables and the last is the output. The procedure breaks down the parameters internally and includes them in the script that RS runs. The scheduling tool I created reads the parameters of the report and gives the user the ability to enter static values or things like @BOM and @EOM (beginning and end of month). DBmail procedure is called for delivery.
Below is the code to the procedure. Be aware that to use this you will need to change database name and file paths. Towards the end there is a call to get encrypted credentials that I've obscured. What this does is takes in the parameters and builds a script. RS executes it and then deletes the script. This is important because the script contains creds. The script rarely exists for more than a fe seconds, so i accepted that security issue, but it has been a long time since I revisited what I could improve on. There is also a comment about how I modified it for multiple simultaneous executions. I ended up backing that out and never got back to it. Because the script file that is written out has the same name, this is best executed in a serialized fashion.
USE [ReportSystem]
GO
if exists (select 1 from sys.objects where name='usp_GenerateReportRSS' and type='p')
begin
drop procedure usp_GenerateReportRSS
end
go
create procedure [dbo].[usp_GenerateReportRSS](
@Mode varchar(255),--For future use
@OutputFormat varchar(20),--EXCEL, pdf, etc. Must be in line with the rs.exe expectations
@OutputPath varchar(500),--The path where the output file should go regardless of email of Sales Force requirements
@OutputReportName varchar(800),--Used to tell the RSS process what to name the output file
@RSReportPath varchar(800),--The path to the report on the report server
@ParameterString varchar(max),--All parameters to pass to the report. Param name is case sensitive, parameter and value must be separated by : and end with ';'
@@ErrorMessage varchar(500) output) --outputs pass or fail
--***********************************************************************************************************************
-- Name:
-- usp_GenerateReportRSS
--
-- Purpose:
-- Accepts parameters and then builds the RSS statement so that xp_cmdshell can create the actual report for the automated reports
--
-- Execute Syntax:
/*
declare @@ErrorMessage varchar(500)
exec ReportSystem.dbo.usp_GenerateReportRSS NULL, 'Excel','F:\ReportSystem\Project','AWS_MCDONALDS_RptID2_061918','/VITAL FS/Service Delivery/Daily Review/Project Schedule','RecNo:2;',@@errormessage output
select @@ErrorMessage
*/
--
-- Revision History:
-- 06/18/2013 JF: Created
-- 06/19/2018 JF: Extensively modified to allow multiple executions at the same time.
/*
drop table ReportSystem.dbo.RSSMaker
create table ReportSystem.dbo.RSSMaker(RecNo Varchar(55), RunDate datetime default getdate(), TheStatement nvarchar(max))
create clustered index IDX_RSSMaker_RecNo on ReportSystem.dbo.RSSMaker (RecNo)
*/
--***********************************************************************************************************************
with encryption
as
set nocount on
declare
@sqlstmt varchar(8000),
@sqlstmt2 Nvarchar(4000),
@FILENAME varchar(75),
@countParam int,
@ParamString nvarchar(max),
@Measure int,
@result int,
@resultWord varchar(15),
@Blah int,
@UIn varbinary(8000),
@PIn varbinary(8000),
@U varchar(100),
@P varchar(100),
@BaseName Varchar(55),
@ErrorTable Varchar(55)
select @@ErrorMessage = null, @countParam = 0,
@BaseName = replace(replace(replace(replace(convert(varchar(25),getdate(),121),' ',''),'-',''),':',''),'.',''),
@countParam = LEN(@ParameterString) - LEN(REPLACE(@ParameterString,';',''))
select @FILENAME = 'F:\ReportSystem\'+@BaseName+'.rss', @ErrorTable = 'ERR'+@BaseName
select @SQLSTMT2 = 'Create table ReportSystem.dbo.'+@ErrorTable+' (ErrorMessage varchar(1000))'
exec sp_executesql @SQLSTMT2
declare @ParamRoller table (recid int identity(1,1), TheColumn varchar(75), TheValue varchar(255))
--build the list of parameters (if any)
while CHARINDEX(';',@ParameterString)>0
begin
select @Measure = len(substring(@ParameterString,1,CHARINDEX(';',@ParameterString)))
insert into @ParamRoller
select replace(substring(substring(@ParameterString,1,@Measure),1,CHARINDEX(':',substring(@ParameterString,1,@Measure))),':',''),
Replace(substring(substring(@ParameterString,1,@Measure),
CHARINDEX(':',substring(@ParameterString,1,@Measure))+1,
CHARINDEX(';',substring(@ParameterString,1,@Measure))
),';','')
set @ParameterString = substring(@ParameterString,@Measure+1,LEN(@ParameterString))
end
select @ParamString = isnull(@ParamString,'')+
'parameters('+convert(varchar(5),RecID-1)+') = New ParameterValue()'+CHAR(10)+
'parameters('+convert(varchar(5),RecID-1)+').Name = "'+TheColumn+'"'+CHAR(10)+
'parameters('+convert(varchar(5),RecID-1)+').Value = "'+Thevalue+'"'+CHAR(10)+CHAR(10)
from @ParamRoller
select @ParamString = 'Dim parameters('+convert(varchar(20),@countParam)+') As ParameterValue'+char(10)+
@ParamString
insert ReportSystem.dbo.RSSMaker(RecNo, RunDate, TheStatement)
select convert(varchar(55),@BaseName), getdate(), 'Public Sub Main() '+CHAR(10)+CHAR(10)+
'Dim format as string = "'+@OutputFormat+'"'+CHAR(10)+
'Dim fileName as string = "'+@OutputPath+'\'+@OutputReportName+
case @OutputFormat when 'EXCEL' then '.xls'
when 'PDF' then '.pdf'
when 'CSV' then '.'+@OutputFormat
when 'MHTML' then '.'+@OutputFormat
end+'"'+CHAR(10)+
'Dim reportPath as string = "'+@RSReportPath+'"'+CHAR(10)+CHAR(10)+
'Dim historyID as string = Nothing'+CHAR(10)+
'Dim deviceInfo as string = Nothing'+CHAR(10)+
'Dim extension as string = Nothing'+CHAR(10)+
'Dim encoding as string'+CHAR(10)+
'Dim mimeType as string = "application/'+@OutputFormat+'"'+CHAR(10)+
'Dim warnings() AS Warning = Nothing'+CHAR(10)+
'Dim streamIDs() as string = Nothing'+CHAR(10)+
'Dim results() as Byte'+CHAR(10)+CHAR(10)+
'rs.Credentials = System.Net.CredentialCache.DefaultCredentials'+CHAR(10)+
'rs.LoadReport(reportPath, historyID)'+CHAR(10)+CHAR(10)+
case when @ParamString is not null then
isnull(@ParamString,'')+'rs.SetExecutionParameters(parameters, "en-us")'
else '' end
+CHAR(10)+CHAR(10)+
'results = rs.Render(format, deviceInfo, extension, mimeType, encoding, warnings, streamIDs)'+CHAR(10)+CHAR(10)+
'Dim stream As FileStream = File.OpenWrite(fileName)'+CHAR(10)+
'stream.Write(results, 0, results.Length)'+CHAR(10)+
'stream.Close()'+CHAR(10)+CHAR(10)+
'End Sub'
begin try
select @UIn = UserName, @PIn = Pass_Word from ReportSystem.dbo.ReportSystemCreds where Purpose = '****'
select @U = DECRYPTBYPASSPHRASE('***********',@UIn)
select @P = DECRYPTBYPASSPHRASE('***********',@PIn)
select @SQLSTMT = ''''+'BCP "select TheStatement from ReportSystem.dbo.RSSMaker" queryout '+@FILENAME+' /c /t, /r \n /U '+@U+' /P '+@P+' /S '+convert(varchar(55),ServerProperty('servername'))+''''
select @SQLSTMT2 = 'insert into ReportSystem.dbo.'+@ErrorTable+' EXEC MASTER.DBO.XP_CMDSHELL '+@SQLSTMT
exec sp_executesql @SQLSTMT2
select @SQLSTMT2 = 'select top 1 @resultWord = case when substring(RS.ErrorMessage,1,500) not like ''%error%'' then ''Success'' else ''Failure'' end,
@@ErrorMessage = case when substring(RS.ErrorMessage,1,500) not like ''%error%'' then ''Success'' else ''Failure'' end +'' ''+
substring(RS.ErrorMessage,1,500)
from ReportSystem.dbo.'+@ErrorTable+char(10)+char(10)+
'CROSS APPLY (SELECT ErrorMessage+ '' '' FROM ReportSystem.dbo.'+@ErrorTable+' FOR XML PATH('''')) as RS(ErrorMessage)'
EXEC sp_executesql @sqlstmt2, N'@ErrorTable VARCHAR(55), @resultWord Varchar(15) output, @@ErrorMessage varchar(500) output',@ErrorTable, @resultWord = @resultWord OUTPUT, @@ErrorMessage = @@ErrorMessage OUTPUT
end try
begin catch
set @Blah = 1
end catch
If @resultWord <> 'Failure'
begin
select @SQLSTMT2 = 'delete from ReportSystem.dbo.'+@ErrorTable
EXEC sp_executesql @sqlstmt2
set @result = 0
begin try
--if this fails make sure BUILTIN\Users has "broswer" permission on the actual report in RS.
select @SQLSTMT = ''''+'rs.exe -i '+@FILENAME+' -l 3600 -s http://ditto/reportserver -e Exec2005'+''''
select @SQLSTMT2 = 'insert into ReportSystem.dbo.'+@ErrorTable+' EXEC MASTER.DBO.XP_CMDSHELL '+@SQLSTMT
exec sp_executesql @SQLSTMT2
select @SQLSTMT = ''''+'BCP "select TheStatement from ReportSystem.dbo.RSSMaker" queryout '+@FILENAME+' /c /t, /r \n /U '+@U+' /P '+@P+' /S '+convert(varchar(55),ServerProperty('servername'))+''''
select @SQLSTMT2 = 'insert into ReportSystem.dbo.'+@ErrorTable+' EXEC MASTER.DBO.XP_CMDSHELL '+@SQLSTMT
exec sp_executesql @SQLSTMT2
select @SQLSTMT2 = 'select top 1 @resultWord = case when substring(RS.ErrorMessage,1,500) not like ''%error%'' then ''Success'' else ''Failure'' end,
@@ErrorMessage = case when substring(RS.ErrorMessage,1,500) not like ''%error%'' then ''Success'' else ''Failure'' end +'' ''+
substring(RS.ErrorMessage,1,500)
from ReportSystem.dbo.'+@ErrorTable+char(10)+char(10)+
'CROSS APPLY (SELECT ErrorMessage+ '' '' FROM ReportSystem.dbo.'+@ErrorTable+' FOR XML PATH('''')) as RS(ErrorMessage)'
EXEC sp_executesql @sqlstmt2, N'@ErrorTable VARCHAR(55), @resultWord Varchar(15) output, @@ErrorMessage varchar(500) output',@ErrorTable, @resultWord = @resultWord OUTPUT, @@ErrorMessage = @@ErrorMessage OUTPUT
end try
begin catch
set @Blah = 1
end catch
end
select @SQLSTMT2 = 'drop table ReportSystem.dbo.'+@ErrorTable
exec sp_executesql @SQLSTMT2
delete ReportSystem.dbo.RSSMaker where RecNo = @BaseName
select @SQLSTMT='del '+@FILENAME
exec master..xp_cmdshell @sqlstmt, no_output
EndIt:
If @resultWord <> 'Failure' set @@ErrorMessage = 'Pass'
GOCheers
-
Also, after looking at the code, it looks like I may have addressed the multiple simultaneous execution issue for the most part, but not completely. The BCP selet statement would still be an issue. One other thing about parameters - You can find those and all the required info in the report server DB and use them to build the param string as shown in my first code sample.
Cheers
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply