XML DML (modify) in SQL Server Agent Job

I'm trying to modify an XML value using XML.modify in an SQL Server Agent job. I'm using SQL Server 2008. Here is my code...

DECLARE @temp XML;
DECLARE @newname VARCHAR(50);
SELECT @temp = CAST(ExtensionSettings AS XML) FROM [ReportServer].[dbo].[Subscriptions] WHERE SubscriptionID = 'a2e1dd4e-5f65-4f0e-bc5a-8e58d21d7292';
SET @newname = 'Monthly_Data_' + CONVERT(VARCHAR(7), DATEADD(day, -1, GETDATE()), 120);

SET @temp.modify('replace value of (/ParameterValues/ParameterValue/Value[../Name/text()="FILENAME"]/text())[1] with sql:variable("@newname")');

UPDATE [ReportServer].[dbo].[Subscriptions] SET ExtensionSettings = CAST(@temp AS varchar(2000)) WHERE SubscriptionID = 'a2e1dd4e-5f65-4f0e-bc5a-8e58d21d7292';

This code runs fine if I just run it in a query window, but when I run it as a step in my job, the job fails. The problem seems to be with the line...

SET @temp.modify('replace value of (/ParameterValues/ParameterValue/Value[../Name/text()="FILENAME"]/text())[1] with sql:variable("@newname")');

Because if I comment it out and run the job, it completes fine.

Can XML.modify not be used in a job? What am I missing?!

Thanks

Solution

I worked on this the better part of today, finally post my question, and promptly found the answer!

I finally discovered how to view the job run history (right click on job, View History). There it mentioned QUOTED_IDENTIFIER being set wrong. A Google search later tells me that Agent jobs by default turn QUOTED_IDENTIFIER OFF (https://dba.stackexchange.com/questions/52802/sql-server-agent-quoted-identifier).

I added the line...

SET QUOTED_IDENTIFIER ON

to my job's step and it started working!