CODEWITHSUNDEEP
sqlsql-server-2008
user384080
user384080

Reputation: 4692

transfer subscription reporting service

Would you recommend the best and easy way to transfer (or copy) the subscriptions from one reporting service to another reporting service in different server?

Upvotes: 7

Views: 19539

Answers (3)

Mike
Mike

Reputation: 1876

Building on the answer from @S.Juarez, this script fixes his bug that breaks the parameters (and thus prevents subscriptions from working), and also transfers across the related Schedules and Schedule User records. It maintains the same GUIDs across both source and target.

The starting point for using this script is after you have already transferred the reports (for example using the tool ReportSync), and you have manually configured the security on all your report folders on the target server. You also need to decide which User record on the target server to associate Subscriptions with, for those cases where the UserName exists on the source server and not on the target server. (This can happen in situations where you decide not to recreate Users on the target, or when you cannot do so because that person no longer a valid account on the domain - i.e. they have left your organisation).

Before you begin, I'd recommend running this small script against both your source and target ReportServer databases and saving the results. Also, take full backups of the databases as whole. These steps give you the ability to rollback both small and large changes.

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

The first part of this next script will transfer the Subscriptions, followed by the Schedules and then the linking records between the Reports, Subscriptions and Schedules. You need to put in the name of your target and source servers, the name of your default user (must already exist in the target Users table) and then execute this on the source server. 

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

To test if your migration has worked properly, you can execute a statement like this against the target server. The GUID should be a SubscriptionID from the Subscriptions table, ideally for something that will be delivered to your inbox.

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

If it works, then you should receive an email within ~20 seconds. If it fails, I found that the best place to look for troubleshooting information was in the SSRS log files, described here.

Upvotes: 11

S.Juarez
S.Juarez

Reputation: 21

Here's something we used to copy subscriptions from a 2008 SSRS to a 2012 SSRS Server. You'll need the data sources to be setup properly ahead of time. 

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 '...'

Upvotes: 2

markdigi
markdigi

Reputation: 1292

How many subscriptions are there?

If theres a low number easiest thing would be to recreate them manually on the other server.

If we are talking a fair amount then there is a database reporting services to store the subscription data I believe called dbo.Subscriptions. I would recommend looking there first to see if you can see the subscriptions.

Otherwise if you would be looking to transfer the whole reporting server database (schedules included) then the following link might be of use:

MSDN Moving the Report Server Databases to Another Computer

Upvotes: 3

Related Questions