I am currently struggling to create method of scheduling Reporting service subscriptions without having that stupid GUID as the job name. First I needed to learn more about querying the dubiously formatted Reporting services xml.
USE [ReportServer];
/****** Script for SelectTopNRows command from SSMS ******/
WITH cteSubs (SubscriptionId, Params) AS
(
SELECT
SubscriptionID
, CAST(CAST([ExtensionSettings] AS NVARCHAR(max)) AS XML) AS params
FROM [dbo].[Subscriptions] subs
INNER JOIN [dbo].[Schedule] sch
ON subs.SubscriptionID = sch.[EventData]
)
SELECT
SubscriptionID
,(SELECT nref.value('Value[1]', 'nvarchar(50)') Comment FROM Params.nodes('/ParameterValues/ParameterValue') AS R(nref) WHERE nref.exist('.[Name = "Comment"]') = 1) AS Comment
FROM cteSubs
This extracts the value of the comment so I can use it for the Job Name later