Uncategorized

List all data sources with connection string detail

2. 4. 2019

Following query is handful in case of migrating reports to different Report Server or when shared data sources should be changed, renamed or deleted.  Query takes some time to be executed because of XML parsing.

WITH [XmlData] AS (
    SELECT [ItemID], [Name], [Type],
         [Xml] = TRY_CONVERT(XML, CONVERT(VARBINARY(MAX), [Content]))
    FROM [dbo].[Catalog]
),
[SharedDS] AS 
(
    SELECT 
		[ds].[ItemID], [c].[Name] [SharedDS], [ds].[Name] [LocalDS],
		[Xml].[value]('(/*:DataSourceDefinition/*:Extension)[1]', 'NVARCHAR(260)') [DataProvider],
        [Xml].[value]('(/*:DataSourceDefinition/*:ConnectString)[1]', 'NVARCHAR(MAX)') [ConnString]
    FROM [dbo].[DataSource] [ds]
        INNER JOIN [XmlData] [c] ON [ds].[Link] = [c].[ItemID]
),
[DS] AS 
(
    SELECT
        [r].[ItemID], [sds].[SharedDS], [r].[LocalDS], 
        CAST ((CASE WHEN [sds].[SharedDS] IS NOT NULL THEN 1 ELSE 0 END) AS BIT) [IsSharedDS],
		ISNULL([r].[DataProvider], [sds].[DataProvider]) [DataProvider],
		ISNULL([r].[ConnString], [sds].[ConnString]) [ConnString]
    FROM ( SELECT [c].[ItemID],
               [DataSourceXml].[value]('@Name', 'NVARCHAR(260)') [LocalDS],
               [DataSourceXml].[value]('(*:ConnectionProperties/*:DataProvider)[1]', 'NVARCHAR(260)') [DataProvider],
               [DataSourceXml].[value]('(*:ConnectionProperties/*:ConnectString)[1]', 'NVARCHAR(MAX)') [ConnString]
            FROM [XmlData] [c]
                CROSS APPLY [Xml].[nodes]('/*:Report/*:DataSources/*:DataSource') [DataSource]([DataSourceXml])
            WHERE [c].[Type] = 2
        ) [r]
        LEFT JOIN [SharedDS] [sds] ON [r].[ItemID] = [sds].[ItemID] AND [r].[LocalDS] = [sds].[LocalDS]
),
[Main] AS 
(
    SELECT [ItemID],
        [QueryXml].[value]('@Name', 'NVARCHAR(256)') [DataSet],
        [QueryXml].[value]('(*:Query/*:DataSourceName)[1]', 'NVARCHAR(260)') [DataSource],
        [QueryXml].[value]('(*:Query/*:CommandType)[1]', 'NVARCHAR(15)') [CommandType],
        [QueryXml].[value]('(*:Query/*:CommandText)[1]', 'NVARCHAR(MAX)') [CommandText]
    FROM [XmlData]
        CROSS APPLY [Xml].[nodes]('/*:Report/*:DataSets/*:DataSet') [QueryData]([QueryXml])
)
SELECT 
    [c].[Path] [ReportPath], [c].[Name] [ReportName], [ds].[LocalDS], [ds].[SharedDS], [ds].[IsSharedDS],
    [ds].[DataProvider], [ds].[ConnString], [m].[DataSet], ISNULL([m].[CommandType], 'Text') [CommandType], [m].[CommandText]
FROM [Main] [m]
    INNER JOIN [DS] [ds] ON [ds].[ItemID] = [m].[ItemID] AND [m].[DataSource] = [ds].[LocalDS]
    INNER JOIN [dbo].[Catalog] [c] ON [ds].[ItemID] = [c].[ItemID]
ORDER BY [ReportPath], [ReportName]
GO

 

77
Share: