This website requires JavaScript.

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


一. 获取SSRS共享数据源


WITH XMLNAMESPACES -- XML namespace def must be the first in with clause. ( DEFAULT '' , '' 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=""> <Extension>SQL</Extension> <ConnectString>Data Source=;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 ""; 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