Snippet Name: Reports_without_shared_DS

Tags:

Created Date: 2025-06-16 13:12:50
Last Modified Date: 2025-05-31 10:32:04

Dependencies:

DBA Notes:

Sample Output:

Title: Reports without shared DS
Author: user
in SQL Server 2019, can you help me find all current reports that are not using a shared data source

Author: ChatGPT
Yes, in SQL Server Reporting Services (SSRS), you can find reports that **do not use a shared data source** by querying the `ReportServer` database.

Here???s a query for SQL Server 2019 that will help identify **reports using embedded (non-shared) data sources**:

```sql
USE [ReportServer];
GO

SELECT 
    C.[Name] AS ReportName,
    C.[Path] AS ReportPath,
    DS.Name AS DataSourceName,
    DS.Link AS SharedDataSourceLink
FROM 
    dbo.Catalog C
CROSS APPLY 
    (
        SELECT 
            x.value('(Name)[1]', 'nvarchar(255)') AS Name,
            x.value('(Link)[1]', 'nvarchar(512)') AS Link
        FROM 
            C.DataSourceDefinitionXml.nodes('//DataSource') AS R(x)
    ) AS DS
WHERE 
    C.Type = 2  -- 2 = Report
    AND DS.Link IS NULL
ORDER BY 
    C.[Path], DS.Name;
```

### Explanation:
- `Catalog.Type = 2` filters for reports.
- `DataSourceDefinitionXml` is an XML column that stores the data source info for each report.
- The `Link` element is **null** for **embedded** data sources (i.e., not shared).
- Shared data sources have a `<Link>` node that references a shared data source path.

### Notes:
- If you're using a custom SSRS catalog or versioning, structure may vary slightly.
- If you don???t have the `DataSourceDefinitionXml` column (depends on SSRS version), a more complex XML parsing against `dbo.Catalog` and `dbo.DataSource` might be needed.

Would you like to filter this further by folder, user, or deployment date?

Author: user
i don't think that's what i am after. i don't need to examine the Content. i need to left join catalogs to shared data sources

Author: ChatGPT
Ah, perfect ??? you're looking to **identify reports that are not linked to shared data sources**, and want to do it relationally, without parsing XML.

In that case, here's a clean query that uses the `Catalog` and `DataSource` tables to **find reports that use embedded data sources** (i.e., ones that **do not have a `Link` to a shared data source**):

```sql
USE [ReportServer];
GO

SELECT 
    r.ItemID AS ReportID,
    r.Name AS ReportName,
    r.Path AS ReportPath,
    ds.Name AS DataSourceName,
    ds.Link AS SharedDataSourcePath
FROM 
    dbo.Catalog r
    INNER JOIN dbo.DataSource ds ON r.ItemID = ds.ItemID
    LEFT JOIN dbo.Catalog sds ON ds.Link = sds.ItemID
WHERE 
    r.Type = 2  -- Report
    AND ds.Link IS NULL  -- Embedded data source
ORDER BY 
    r.Path, ds.Name;
```

### Explanation:
- `Catalog r`: represents reports (`Type = 2`).
- `DataSource ds`: links each report to its data source.
- `ds.Link` will be `NULL` if the data source is embedded (not shared).
- If `ds.Link IS NOT NULL`, then it???s using a shared data source.

### Optional: To **exclude subreports or hidden items**, you can add filters like:
```sql
AND r.Hidden = 0
```

Would you also like a version that shows both embedded and shared, just to compare side by side?

Author: user
can you also tell me which reports also have subscriptions tied to teh report?

Author: ChatGPT
Absolutely ??? you can enhance the previous query to also show whether each report has **any subscriptions** linked to it.

Here's an updated version that includes a `HasSubscription` flag:

```sql
USE [ReportServer];
GO

WITH ReportDataSources AS (
    SELECT 
        r.ItemID AS ReportID,
        r.Name AS ReportName,
        r.Path AS ReportPath,
        ds.Name AS DataSourceName,
        ds.Link AS SharedDataSourcePath
    FROM 
        dbo.Catalog r
        INNER JOIN dbo.DataSource ds ON r.ItemID = ds.ItemID
    WHERE 
        r.Type = 2  -- Report
        AND ds.Link IS NULL  -- Embedded data source
)
SELECT 
    rds.ReportID,
    rds.ReportName,
    rds.ReportPath,
    rds.DataSourceName,
    rds.SharedDataSourcePath,
    CASE 
        WHEN s.Report_OID IS NOT NULL THEN 1
        ELSE 0
    END AS HasSubscription
FROM 
    ReportDataSources rds
    LEFT JOIN (
        SELECT DISTINCT Report_OID
        FROM dbo.Subscriptions
    ) s ON rds.ReportID = s.Report_OID
ORDER BY 
    rds.ReportPath, rds.DataSourceName;
```

### What this does:
- `ReportDataSources` CTE: filters to reports with embedded data sources only.
- Joins to `Subscriptions` using `Report_OID`, which matches `Catalog.ItemID`.
- Adds `HasSubscription` column (1 = has at least one subscription, 0 = none).

Let me know if you'd like to return subscription details (e.g., schedule, user, delivery method), or if you'd like to also include reports that **do** use shared data sources.