SQL Server Performance

Catalog table on Reportserver database

Discussion in 'SQL Server 2005 Reporting Services' started by bobo224, Nov 12, 2008.

  1. bobo224 New Member

    Hello, After I made changes to Catalog table on Reportserver database directly, the reports still display the old data. They don't reflect the database. I also checked the execution set up on report manager and the reports run always with the most recent data. I didn't edit the reports through VS because there are lots of reports to go through. What do I do to make the reports reflect the fresh data?? Your help will be appreciated. thanks
  2. satya Moderator

    Welcome to the forums.
    What was the query you have used to update the catalog table?
    By design the data for all report runs is stored in the ExecutionLog table in the ReportServer database. With the data in this table along with the data that is stored in the Catalog table and run the below TSQL to get execution stats:
    SELECT
    ex.UserName, ex.Format, ex.TimeStart, cat.Name, ex.Parameters, CONVERT(nvarchar(10), ex.TimeStart, 101) AS rundate
    FROM ExecutionLog AS ex INNER JOIN
    Catalog AS cat ON ex.ReportID = cat.ItemID
    ORDER BY ex.TimeStart DESC
  3. bobo224 New Member

    Thanks. I've modifed Content field in Catalog
    Please see my query below.
    After I execute the statement, the data is updated, but the actual report displays still the old link.UPDATE dbo.Catalog
    SET content=replace(cast(cast(content as varbinary(max)) as varchar(max)) ,'http://reportserver1/Reportserver/Pages/Reportviewer.aspx?%2fDeacons+Reports%2fAdministration%2fCatalogue%2fReport+Description+-+151','http://reportserver1/Reportserver/Pages/Reportviewer.aspx?%2fDeacons+Reports%2fAdministration%2fRS005+-+Report+Description')FROM
    dbo.CatalogWHERE
    path like '%/DEVELOPMENT/BO%'AND cast(cast(content as varbinary(max)) as varchar(max)) like '%"http://reportserver1/Reportserver/Pages/Reportviewer.aspx?%2fDeacons+Reports%2fAdministration%2fCatalogue%2fReport+Description+-+151&ReportName="+Globals!ReportName%'
    AND cast(cast(content as varbinary(max)) as varchar(max)) like '%<Value>="More information about " + Globals.ReportName</Value>%'
    thanks for your help

Share This Page