I need to build a SQL Server Agent job that will run the SSMS built-in report found under SSISDB/Standard Reports/All Executions and send it to a list of recipients.
I do not want to have to run the report interactively from SSMS, but have the report run on a scheduled basis so that it is ready and distributed to the required recipients before the start of their business day.
Couple of years back, I had to do something like this. I created a stored proc that could be called to run the report and mail it. First, you set up a subscription for the report on the report server and then use the stored proc with the report path.
This was not perfect; it has at least two problems/issues: a) if I had two subscriptions on a report, this would have failed, or picked up a random one (not sure which). b) this does not give you control over who to mail it out to etc. That is pre-defined in the subscription. But, for my purposes, this was good enough.
If you develop something more robust/flexible, and if you can share it, that would be appreciated.
CREATE PROCEDURE [dbo].[GenerateReport]
@ReportPath VARCHAR(255)
AS
-- To generate a report based on a subscription if the path of the report is given.
-- This is the report to be generated.
-- example: @ReportPath '/MyReports/AverageCost'
DECLARE @subscriptionId UNIQUEIDENTIFIER;
DECLARE @msg VARCHAR(255);
SELECT @subscriptionId = rs.SubscriptionId
FROM ReportServer.dbo.ReportSchedule RS
INNER JOIN ReportServer.dbo.Catalog C ON ( C.ItemID = RS.ReportID )
WHERE Path = @ReportPath ;
IF (@subscriptionId IS NULL)
BEGIN
INSERT INTO dbo.DIANGOSE_ReportRuns VALUES (GETDATE(), @ReportPath,@subscriptionId, 0);
SET @msg = 'No scheduled report was found for path = ' + @ReportPath;
RAISERROR(@msg,16,1);
RETURN;
END
exec [ReportServer].dbo.AddEvent
@EventType='TimedSubscription',
@EventData=@subscriptionId;
INSERT INTO dbo.DIANGOSE_ReportRuns VALUES (GETDATE(), @ReportPath,@subscriptionId, 1);
GO
The dbo.DIANGOSE_ReportRuns is my debugging/logging table.
Thanks James. I have no problem scheduling regular reports (that I develop). It's the standard reports built-in to SSMS that I'm having trouble with. There are no RDLs (that I can find so far). The reports do not appear on the report server at all.
I want to use the standard reports but run those on a schedule.
we need to re-create the reports in Reporting Service.
Based on my understanding, the SQL Server built-in Reports are generated by SSMS itself. It is not available to be controlled by SQL Server Database. So we cannot use T-SQL to export it out as a rdl report or create a Agent Job for it.