question

natongm avatar image
natongm 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?
ssrssubscriptions
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
0 Likes 0 ·
Mark avatar image
Mark answered
@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
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you @Mark, @William, @Oleg ..I am getting there
0 Likes 0 ·
You're welcome @natongm, glad to help. That's what we're here for.
0 Likes 0 ·
Another task that I need to accomplish, preferably, within the same stored procedure is to parse the string values in dbo.subscriptions.LastStatus of the reporting server database that I am inserting into this new table..
0 Likes 0 ·
Sorry natongm, but I'm not very familiar with the text in that table. Maybe you should show the text or make this a separate question.
0 Likes 0 ·
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
0 Likes 0 ·
Show more comments
Mark avatar image
Mark answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

natongm avatar image
natongm answered
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,
15 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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;
2 Likes 2 ·
You will have a fun time of it. IIRC, the report information sits in the database ReportingServer (or something similar). The jobs for them are created using GUIDs! I don't know if the report information in the database is any easier to manage/query. Good Luck!
0 Likes 0 ·
@Oleg - thanks for the script - it should help @notongm to get a start on setting up some sort of repository. You ought to change it to a real answer it is worth a +1 at least.
0 Likes 0 ·
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?
0 Likes 0 ·
@natongm - you could do that, put the insert above the select statement and away you go.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.