This website requires JavaScript.

Microsoft SQL Server : 修改SSRS 共享数据源的 XML值

本文以SSRS的数据源为例,修改数据源连接

一. 获取SSRS共享数据源

其实需要的只是中间content字段转换的部分

WITH XMLNAMESPACES -- XML namespace def must be the first in with clause. ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource' , 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd ) ,SDS AS ( SELECT SDS.NAME AS SharedDsName ,SDS.[Path] ,CONVERT(XML, CONVERT(VARBINARY(max), content)) AS DEF FROM dbo.[Catalog] AS SDS WHERE SDS.Type = 5 ) -- 5 = Shared Datasource SELECT CON.[Path] ,CON.SharedDsName ,CON.ConnString FROM ( SELECT SDS.[Path] ,SDS.SharedDsName ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString FROM SDS CROSS APPLY SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN ) ) AS CON -- Optional filter: -- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%' ORDER BY CON.[Path] ,CON.SharedDsName;
** 二. 修改数据源**
DECLARE @myDoc xml; SET @myDoc = '<DataSourceDefinition xmlns="http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource"> <Extension>SQL</Extension> <ConnectString>Data Source=192.168.3.222;Initial Catalog=test</ConnectString> <CredentialRetrieval>Store</CredentialRetrieval> <WindowsCredentials>False</WindowsCredentials> <ImpersonateUser>False</ImpersonateUser> <Enabled>True</Enabled> </DataSourceDefinition>';

--修改数据源链接, 注意,如果xml中有xmlns的,这里需要写 declare default element xxxx 如果没有就不需要定义 SET @myDoc.modify(' declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource"; replace value of (/DataSourceDefinition/ConnectString/text())[1] with "new text describing the manu step" '); SELECT @myDoc;

三 . 参考文章

BI SQL # 174 : SQL Server DBA Scripts : List connection strings of all SSRS Shared Data sources Xml modify issue when namespace is involved

0条评论
avatar