Service Reporting

Question

Would you recommend the best and easiest way to transfer (or copy) a subscription from one reporting service to another reporting service on another server?

Answer 1

How many subscribers are there?

If theres a smaller amount, the easiest way would be to recreate them manually on another server.

If we are talking about a fair amount, then there are database reporting services for storing subscription data, which I think are called dbo.Subscriptions. I would recommend looking there first to see if you can see the subscriptions.

, ( ), :

MSDN

Answer 2

@S.Juarez, script , ( ), . GUID , .

script , (, ReportSync), . , , , , . ( , , , , ).

, script ReportServer . , . , .

SELECT u.UserName, c.Path, Parameters, s.ExtensionSettings, s.Report_OID, SubscriptionID, u.UserID
FROM dbo.[Subscriptions] s
JOIN users u 
on s.OwnerID = u.UserID
JOIN catalog c
on c.ItemID = s.Report_OID

script , , , . , ( ), .

DECLARE @Default_User varchar(50) 
SELECT @Default_User = UserID FROM [SourceServer].ReportServer.dbo.Users WHERE UserName = '[DOMAIN\YourDefaultUserNameGoesHere]'

INSERT INTO [TargetServer].ReportServer.dbo.Subscriptions(
    SubscriptionID, OwnerID, Report_OID,  Locale, InactiveFlags, ExtensionSettings, ModifiedByID, ModifiedDate, 
    [Description], LastStatus, EventType, MatchData, LastRunTime, [Parameters], DataSettings, DeliveryExtension, Version
    )
SELECT 
    --cSource.Path,
    --uSource.UserName,
    SubscriptionID,
    --u.UserName,
    --LastStatus,
    COALESCE(uTarget.UserID, @Default_User) AS OwnerID,
    cTarget.ItemID,
    Locale, InactiveFlags, ExtensionSettings,
    @Default_User AS ModifiedByID,
     GETDATE(),
    sSource.[Description], LastStatus, EventType, MatchData, LastRunTime, [Parameters], DataSettings, DeliveryExtension, Version

FROM [SourceServer].ReportServer.dbo.Subscriptions sSource
    LEFT JOIN [SourceServer].ReportServer.dbo.Catalog cSource ON cSource.ItemId = sSource.Report_OID
    LEFT JOIN [SourceServer].ReportServer.dbo.Users uSource ON sSource.OwnerID = uSource.UserID
    LEFT JOIN [TargetServer].ReportServer.dbo.Catalog cTarget ON cTarget.Path = cSource.Path
    LEFT JOIN [TargetServer].ReportServer.dbo.Users uTarget ON uTarget.UserName = uSource.UserName
WHERE sSource.SubscriptionID NOT IN 
(
SELECT SubscriptionID FROM [TargetServer].ReportServer.dbo.Subscriptions
)


INSERT INTO [TargetServer].ReportServer.dbo.Schedule
(
ScheduleID, Name, StartDate, Flags, NextRunTime, LastRunTime, EndDate, RecurrenceType, MinutesInterval, DaysInterval, WeeksInterval, DaysOfWeek, DaysOfMonth, [Month], MonthlyWeek, State, LastRunStatus, ScheduledRunTimeout, EventType, EventData, Type, ConsistancyCheck, Path, CreatedById
)
SELECT
ScheduleID, Name, StartDate, Flags, NextRunTime, LastRunTime, EndDate, RecurrenceType, MinutesInterval, DaysInterval, WeeksInterval, DaysOfWeek, DaysOfMonth, [Month], MonthlyWeek, State, LastRunStatus, ScheduledRunTimeout, EventType, EventData, Type, ConsistancyCheck, Path, 
COALESCE(uTarget.UserID, @Default_User) AS CreatedById
FROM [SourceServer].ReportServer.dbo.Schedule s
INNER JOIN [SourceServer].ReportServer.dbo.Users uSource
ON s.CreatedById = uSource.UserID
LEFT JOIN [TargetServer].ReportServer.dbo.Users uTarget
ON uSource.UserName = uTarget.UserName
WHERE ScheduleID NOT IN (SELECT ScheduleID FROM [TargetServer].ReportServer.dbo.Schedule)


INSERT INTO [TargetServer].ReportServer.dbo.ReportSchedule
(
ScheduleID, ReportID, SubscriptionID, ReportAction
)
SELECT
    rsSource.ScheduleID, cTarget.ItemID, rsSource.SubscriptionID, rsSource.ReportAction
FROM [SourceServer].ReportServer.dbo.ReportSchedule rsSource
INNER JOIN [TargetServer].ReportServer.dbo.Schedule sTarget
ON rsSource.ScheduleID = sTarget.ScheduleID
INNER JOIN [SourceServer].ReportServer.dbo.Catalog cSource
On cSource.ItemID = rsSource.ReportID
INNER JOIN [TargetServer].ReportServer.dbo.Catalog cTarget
ON cSource.Path = cTarget.Path
LEFT JOIN [TargetServer].ReportServer.dbo.ReportSchedule rsTarget
ON  rsSource.ScheduleID = rsTarget.ScheduleID
AND rsSource.ReportID = rsTarget.ReportID
AND rsSource.SubscriptionID = rsTarget.SubscriptionID
WHERE rsTarget.ReportID IS NULL

, , . GUID SubscriptionID Subscriptions, -, .

exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='cb38a708-7735-4b5a-8ff3-e03ee1b18edb'

, ~ 20 . , , SSRS, .

Answer 3

Here we used to copy a subscription from SSRS 2008 to the 2012 SSRS server. You will need the correct data source settings.

INSERT INTO Mercury.ReportServer.dbo.Subscriptions(SubscriptionID, OwnerID, Report_OID,  Locale, InactiveFlags, ExtensionSettings, ModifiedByID, ModifiedDate, Description, LastStatus, EventType, MatchData, LastRunTime, Parameters, DataSettings, DeliveryExtension, Version)
SELECT 
    --Path,
    SubscriptionID
    ,(SELECT UserID FROM <Destination Linked Server>.ReportServer.dbo.Users WHERE UserName = '<User from DB>')  OwnerID
    ,(select ItemId from <Destination Linked Server>.ReportServer.dbo.Catalog mCatalog where mCatalog.Path = Catalog.Path )Report_OID
    ,Locale, InactiveFlags, ExtensionSettings
    ,(SELECT UserID FROM <Destination Linked Server>.ReportServer.dbo.Users WHERE UserName = 'User from DB') ModifiedByID
    , GETDATE()
    ,Sub.Description, LastStatus, EventType, MatchData, LastRunTime, Parameter, DataSettings, DeliveryExtension, Version

FROM ReportServer..Subscriptions Sub
    LEFT JOIN ReportServer.dbo.Catalog ON Catalog.ItemId = Sub.Report_OID
WHERE Path NOT IN 
  (
    SELECT Path
    FROM <Destination Linked Server>.ReportServer.dbo.Subscriptions
      LEFT JOIN <Destination Linked Server>.ReportServer.dbo.Catalog ON Catalog.ItemId = Subscriptions.Report_OID
  )
--AND
--  PATH LIKE '...'