This would be really useful to me but your code in part 2 sometimes gives some results (but not a full set) and always gives me the following error message...

 
Conversion failed when converting from a character string to uniqueidentifier.

Can you help with this? I'm unfamiliar with the tables in the ReportServer database so would find it difficult to diagnose the problem myself.

Hi Welsh,

If you don't mind, can you send me the version of SQL Server [use SELECT @@VERSION] and a screenshot if possible to my email id at SankarAThotmailDOTcom. We can post the findings once we identify the root cause here.

 Thanks a lot, yes that's the setup I have and your fix make it work just fine now :)

I got duplicate on SOME reports with the original query posted

I think it's from the "old/deleted" subscription and the double-join between table a and c (ReportServer.dbo.ReportSchedule)

This one works for me

--http://www.mssqltips.com/tip.asp?tip=1846
SELECT  'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData='''
        + CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand
       ,b.name AS JobName
       ,a.SubscriptionID
       ,e.name
       ,e.path
       ,d.description
       ,laststatus
       ,eventtype
       ,LastRunTime
       ,date_created
       ,date_modified
FROM    ReportServer.dbo.ReportSchedule a
        JOIN msdb.dbo.sysjobs b ON a.ScheduleID = b.name
        --JOIN ReportServer.dbo.ReportSchedule c ON b.name = c.ScheduleID
        JOIN ReportServer.dbo.Subscriptions d ON a.SubscriptionID = d.SubscriptionID
        JOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemid
WHERE   1 = 1
        AND b.description = 'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.'
--e.name = 'Sales_Report'
ORDER BY LastRunTime DESC

RETURN
 

This is great for figuring out what is what, but is there a way to permanently change the SQL Server agent Job Name?  Just by finding the name of the job, then updating the column in that table? Thanks!

You certainly don't want to do that. On a related note, here is the MSFT recommendation.

Here is a quote from BOL: 

The table structure for both databases is optimized for server operations and should not be modified or tuned. Microsoft might change the table structure from one release to the next. If you modify or extend the database, you might limit or prevent the capability to perform future upgrades or apply service packs. You might also introduce changes that impair report server operations.

http://technet.microsoft.com/en-us/library/ms156016.aspx

Since all the metadata is identified for a subscription report, is there a way to insert a subscription and a schedule into these tables without going through the SSRS pages.

I am working on a project were subscriptin details are maintained in user defined tables.

Any suggestions ?

Jimmy 

Hi Benjim,

Were u able to do maintain ubscriptions and related schedule for a reports?

I am currently working on same kind of project, where, I have to update schedule for a subscription of report using my application and update the tables of reportserver db from backend and ASP.Net.

Please share the details if you were able to complete your project.

Always funny how somebody opens an old thread days before I need it. At any rate, I was getting conversion errors when I ran this (conversion failed when converting from a character string to uniqueidentifier). This is in the joins to b.name. Convert a.ScheduleID and C.ScheduleID to varchar for a proper join.

Hope this helps somebody.

BTW,
Sankar, thanks for the code. I was really getting tired of searching for the scheduled time any time I wanted to force a job to run.

--aas4mis

Your query really helped me track failed subscriptions and rerun them. (with the first 5 remarks updates too). Thanks a lot!

Very helpful tip..Thanks for you help Sankar

With the following sql statement will show you the definition for each report, if the report is using a store procedure or a SQL statement, Of course, If your Reporting Services instance has a different name, you'll need to change the bold name

--Find the procedure names that are being used along with report, datasource names
-- ==============================================================
WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT  name
, x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType
, x.value('CommandText[1]','VARCHAR(50)') AS CommandText
, x.value('DataSourceName[1]','VARCHAR(50)') AS DataSource
FROM ( SELECT name, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM ReportServer.dbo.Catalog
WHERE content is not null and TYPE = 2) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'

USE ReportServer

--Find the relationship between a report and a DataSet
-- ==============================================================
SELECT --TOP 100 PERCENT
    c.Name [Object]
    , ( CASE [Type] WHEN 1 THEN 'Folder' WHEN 2 THEN 'Report' WHEN 3 THEN 'Resource'
             WHEN 4 THEN 'Linked Report' WHEN 5 THEN 'Data Source' WHEN 6 THEN 'Report Model' END ) [Object Type]  
    , c.Path, ds.Name [DataSource], ds.Link [DataSet Link]
FROM dbo.Catalog C (NOLOCK)
JOIN dbo.DataSource DS (NOLOCK) ON DS.ItemID = C.ItemID
WHERE c.Type NOT IN (1,5,6)

I changed the EXEC part in order to be able to rerun caching etc. which failed too:

SELECT

'EXEC ReportServer.dbo.AddEvent @EventType=''' 

+ eventtype +''',@EventData='''+

+CAST(a.SubscriptionID ASVARCHAR(40))+''''AS ReportCommand

SELECT

            Cast(b.name as Uniqueidentifier) AS JobName

            , e.name

            , e.path

            , d.description

            , a.SubscriptionID

            , laststatus

            , eventtype

            , LastRunTime

            , date_created

            , date_modified

    FROM ReportServer.dbo.ReportSchedule a JOIN msdb.dbo.sysjobs b

            ON Cast(a.ScheduleID as nvarchar(50)) =CAST(b.name as nvarchar(50))

            JOIN ReportServer.dbo.ReportSchedule c

            ON Cast(b.name as nvarchar(50)) = cast(c.ScheduleID as nvarchar(50))

            JOIN ReportServer.dbo.Subscriptions d

            ON Cast(c.SubscriptionID as nvarchar(50)) = Cast(d.SubscriptionID as nvarchar(50))

            JOIN ReportServer.dbo.Catalog e

            ON Cast(d.report_oid as nvarchar(50)) = Cast(e.itemid as nvarchar(50))

    WHERE e.name = 'Sales'