asked
Managing SSRS Report Subscriptions Programmatically
I am charged with creating table which will manage SSRS subscriptions (adding/removing subscribers,reports,etc). How do I proceed?
Thanks@WilliamD.. I have a CTE which pulls subscription information from the Reporting Services Server. I am looking for way to insert that data into a table that I have created on a different database (same server) using MATCH so that only data in columns that are common to the tables is inserted?
@natongm, this is to continue the discussion from the comments. I hope that I understand what your trying to do here. If you want to use an EXEC, then I think your wanting to use it from a stored procedure. To use the EXEC, define your variables with either a SET or SELECT first. SELECT is better usually since you can string together the variables using commas. But a SET will define only one variable per line. SET @SubscriptionID=SubscriptionID SET @LastStatus=LastStatus SET @Name=Name ... Or with a SELECT SELECT @SubscriptionID=SubscriptionID, @LastStatus=LastStatus, @Name=Name, [etc.] ... After defining the variables, then you can run the EXEC: EXEC [dbo].[uspUpdateSubscriptions] @SubscriptionID, @LastStatus, @Name, @ModifiedDate, @LastRunTime
Below is the text in the LastStatus column which I need to parse or split so that the semi-colons are eliminated, and only the email addresses are retrieved. Mail sent to
nancy.jones@datatech.com;
redriver@datatech.com;
de.transactions@datatech.com;
diane.slaven@datatech.com;
jennifer.campos@datatech.com I tried SELECT DTREPORT.DBO.fnSplit(LastStatus, N';', 1), * FROM Subscriptions But I am only the first record is grabbed
If by "manage," you just want to monitor them, one place to start with this would be the [Report Server Execution Log][1]. [1]:
http://msdn.microsoft.com/en-us/library/ms159110(SQL.90).aspx
I apologize for the ambiguity. I created a table on a non-SSRS database, and the goal is possibly write a stored procedure which will query the reporting server to retrieve subscription information based on the columns in the table which do not correspond with those in reporting server. Table: [CatalogID] [int] IDENTITY(1,1) NOT NULL, [ReportID] [uniqueidentifier] NOT NULL, [ReportName] [nvarchar](160) NOT NULL, [EmailAddress] [nvarchar](260) NOT NULL, [UserName] [nvarchar](260) NULL, [SubEffective] [datetime] NOT NULL, [SubEnd] [datetime] NULL, [ExecutionTime] [datetime] NOT NULL,
@WilliamD Here is a somewhat useful script to figure what each job is doing:
select
Schedule.ScheduleID as SQLAgent_Job_Name,
Subscriptions.Description as sub_desc,
Subscriptions.DeliveryExtension as sub_delExt,
[Catalog].Name as reportname, [catalog].path as reportpath
from reportschedule inner join Schedule
on ReportSchedule.ScheduleID = Schedule.ScheduleID
inner join Subscriptions
on ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
inner join [Catalog]
on ReportSchedule.ReportID = [Catalog].ItemID
and Subscriptions.Report_OID = [Catalog].ItemID;
Thanks @WilliamD for the script I have a CTE which pulls subscription information from the Reporting Services Server. I am looking for way to insert that data into a table that I have created on a different database (same server) using MATCH so that only data in columns that are common to the tables is inserted?
