Forces SSRS catalog to update report definition based on report content.

SSRS - Force Description Update.sql

USE ReportServer;

WITH XMLNAMESPACES
(
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd,
    DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
)
--SELECT Report.value('(.)[1]', 'nvarchar(512)')
UPDATE c
SET c.Description = Report.value('(.)[1]', 'nvarchar(512)')
FROM dbo.Catalog AS c
    CROSS APPLY (
		SELECT CONVERT(xml, CONVERT(varbinary(MAX), Content)) AS ContentXml
	) AS ca
    CROSS APPLY ca.ContentXml.nodes('/Report/Description') AS T2(Report)
WHERE Path LIKE N'/%'
      AND c.Description <> Report.value('(.)[1]', 'nvarchar(512)');


WITH XMLNAMESPACES
(
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd,
    'http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS cl,
    DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
)
--SELECT Report.value('(.)[1]', 'nvarchar(512)')
UPDATE c
SET c.Description = Report.value('(.)[1]', 'nvarchar(512)')
FROM dbo.Catalog AS c
    CROSS APPLY (
		SELECT CONVERT(xml, CONVERT(varbinary(MAX), Content)) AS ContentXml
	) AS ca
    CROSS APPLY ca.ContentXml.nodes('/Report/Description') AS T2(Report)
WHERE Path LIKE N'/%'
      AND c.Description <> Report.value('(.)[1]', 'nvarchar(512)');

Thank you for publishing this script. It helped me!

Super helpful!! I had to make a few mods, like adding a TRY_CAST, to get it to execute.

USE ReportServer;

;WITH XMLNAMESPACES
(
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd,
    'http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS cl,
    DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition'
)
SELECT --c.[Name] AS ReportName
--, Report.value('(.)[1]', 'nvarchar(max)') AS RDLDescription
--, c.[Description] AS ServerDescription
UPDATE c
SET c.Description = Report.value('(.)[1]', 'nvarchar(512)')
FROM dbo.Catalog AS c
    CROSS APPLY (
SELECT TRY_CAST(CAST(CAST(Content AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS XML) AS ContentXml
	) AS ca
    CROSS APPLY ca.ContentXml.nodes('/Report/Description') AS T2(Report)
WHERE Path LIKE N'/%'
      AND ISNULL(c.[Description],'') <> Report.value('(.)[1]', 'nvarchar(max)')
	  AND Report.value('(.)[1]', 'nvarchar(max)') <> 'ERA Simplified Report Template. ** Dev Note - Modify report description here.' -- optional
	  AND c.[Type] = 2 -- report descriptions
;