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"&gt;
<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

打赏支持:如果你觉得我的文章对你有所帮助,可以打赏我哟。