SSRS批量更新Link的DB

 通过脚本批量更新SSRS templates 相关的 link DB。

此脚本用于解决rdl文件上传到SSRS服务器后default DB connection无法更新到对应的report的数据库连接池的问题。

@PathName是Report的folder，需要改成自己对应的目录。

  declare @MyTestNoticeReportsLink [uniqueidentifier], @PathName varchar(255);

  set @PathName='/MyTestNoticeReports/';
  set @MyTestNoticeReportsLink=(select top 1 ItemID from [ReportServer].[dbo].[Catalog] where [Path]=@PathName+'MyTest')

  update [ReportServer].[dbo].[DataSource] set Link=@MyTestNoticeReportsLink
  from [ReportServer].[dbo].[DataSource] d join [ReportServer].[dbo].[Catalog] ct on ct.ItemID=d.ItemID  
  where ct.path like @PathName+'%' and d.Name='MyTest'

  --/Test Reports/MyTest
   set @PathName='/MyTest Reports/';
  set @MyTestNoticeReportsLink=(select top 1 ItemID from [ReportServer].[dbo].[Catalog] where [Path]=@PathName+'MyTest')

  update [ReportServer].[dbo].[DataSource] set Link=@MyTestNoticeReportsLink
  from [ReportServer].[dbo].[DataSource] d join [ReportServer].[dbo].[Catalog] ct on ct.ItemID=d.ItemID  
  where ct.path like @PathName+'%' and d.Name='MyTest'


  SELECT ct.ItemID,ct.Path,d.* from [ReportServer].[dbo].[Catalog] ct join [ReportServer].[dbo].[DataSource] d on ct.ItemID=d.ItemID order by Path