博客

背景：数据库迁移到另外一台服务器，原来的SSRS的报表服务器跟着移动，然后IP端口等都需要修改，故所有SSRS里面的报表的数据库链接，SQL凭据等都需要修改。

在一开始如果配置得好的话用共享的数据源是最好解决这个问题的，只需要修改共享数据源里面的链接，所有的报表就可以跟着改了，但是如果没有设置共享数据源呢，如果要一个一个报表去改数据源，那就太麻烦了，下面提供的办法需要用到SQL的操作。

首先，了解一下SSRS的数据库里面的涉及到本次操作的表和部分关键信息。其中包括，表 dbo.Catalog，里面保存了报表的实际路径和对应的主键ItemID，表 dbo.DataSource，里面保存的是每个报表对应的数据库链接信息等。

注意：dbo.DataSource里面的数据库链接信息是有被加密过的，而且在里面是通过执行特定的存储过程来进行更新的，所以不能通过简单的Update语句直接去更新上面的连接字符串和凭据。

这时候需要用到SQL Server Profiler 并打开它，随意找一个或者建一个新的报表，然后输入正确的链接字符串和凭据，保存，可以从SQL Server Profiler中找到类似如下的SQL语句。

exec DeleteDataSources @ItemID='0D1E5F90-0AFD-482B-AF25-C072AD9286E7'

exec AddDataSource @AuthType=1,@DSID='D3A1AE51-9524-443E-BF29-151862D14150',@ItemID='0D1E5F90-0AFD-482B-AF25-C072AD9286E7',@Name=N'DataSource1',@Extension=N'SQL',@CredentialRetrieval=1,@OriginalConnectionString=加密字符串,@Flags=3,@OriginalConnectStringExpressionBased=0,@Version=3

exec DeleteDataSets @ItemID='0D1E5F90-0AFD-482B-AF25-C072AD9286E7'

exec DeleteDataSources @ItemID='0D1E5F90-0AFD-482B-AF25-C072AD9286E7'

exec AddDataSource @AuthType=1,@DSID='D3A1AE51-9524-443E-BF29-151862D14150',@ItemID='0D1E5F90-0AFD-482B-AF25-C072AD9286E7',@Name=N'DataSource1',@Extension=N'SQL',@CredentialRetrieval=2,@Prompt=N'输入用户名和密码以访问数据源:',@ConnectionString=加密字符串,@OriginalConnectionString=加密字符串,@UserName=加密用户,@Password=加密密码,@Flags=3,@OriginalConnectStringExpressionBased=0,@Version=3

只要拿出这部分的SQL语句，然后给表 dbo.Catalog 做一个循环基本上就可以实现批量的更新。

完整代码参考如下：

--需从SQL Server Profiler获取的参数,注意这里有两个@OriginalConnectionString，传入的值不一定一致
DECLARE @OriginalConnectionStringFirst IMAGE
,@ConnectionString IMAGE
,@OriginalConnectionStringTwo IMAGE
,@UserName IMAGE
,@Password IMAGE
-------------------------------------------
if object_id('tempdb..#tmp_Catalog') is not null    
  drop table #tmp_Catalog

DECLARE @i INT = 1, @Cnt INT 
SELECT ROW_NUMBER() OVER (ORDER BY Name) AS id, ItemID INTO #tmp_Catalog FROM dbo.Catalog WHERE Type = 2
SELECT @Cnt = COUNT(*) FROM #tmp_Catalog

WHILE @i <= @Cnt
BEGIN
	DECLARE @ItemID NVARCHAR(100),@DSID NVARCHAR(100),@DataSource NVARCHAR(260),@Extension NVARCHAR(260)
	SELECT @ItemID = ItemID FROM #tmp_Catalog WHERE id = @i

	SELECT @DSID = DSID,@DataSource = Name,@Extension = Extension  FROM dbo.DataSource WHERE ItemID = @ItemID

	IF ISNULL(@DSID,'') = N'' 
	BEGIN
		SET @i = @i + 1
		CONTINUE
	END 

	EXEC DeleteDataSources @ItemID = @ItemID
	
	EXEC AddDataSource @AuthType=1,@DSID = @DSID,@ItemID = @ItemID,@Name=@DataSource,@Extension=@Extension,@CredentialRetrieval=1,
	@OriginalConnectionString=@OriginalConnectionStringFirst,@Flags=3,@OriginalConnectStringExpressionBased=0,@Version=3
	
 
	EXEC DeleteDataSets @ItemID= @ItemID

	EXEC DeleteDataSources @ItemID = @ItemID

	EXEC AddDataSource @AuthType=1,@DSID = @DSID,@ItemID=@ItemID,@Name=@DataSource,@Extension=@Extension,@CredentialRetrieval=2,@Prompt=N'输入用户名和密码以访问数据源:',
	@ConnectionString=@ConnectionString,@OriginalConnectionString=@OriginalConnectionStringTwo,@UserName=@UserName,@Password=@Password,@Flags=3,@OriginalConnectStringExpressionBased=0,@Version=3

	SET @i = @i + 1
END 

if object_id('tempdb..#tmp_Catalog') is not null    
  drop table #tmp_Catalog