none
Table Explaination for dbo.catalog table in ReportServer database RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

     

    I am trying to look for the explaination for the columns in dbo.catalog table. Is there any place i have explainations for these tables?

     

    For instance there is a column 'Type' in dbo.catalog table. But not sure what information it has - it has values like 1,2,3,4 .. Wanted to know what these values are.. Any idea on this?

     

    Thanks, Uma

    Wednesday, October 29, 2008 6:23 PM
    |

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Uma,

     

    We do not document or support querying any Report Catalog tables.  If you need to find out what items are in the report catalog, use the ListChildren SOAP API to list them.  We do support the ExecutionLog (Table/View; version dependent) or the ExecutionLog2 View.    Otherwise, writing queries against the SSRS DB is not supported and the schema, content, interpretation of the content is subject to change without notice.

     

    Probably not the answer you wanted to hear...

    -Lukasz

    Wednesday, October 29, 2008 8:12 PM
    |
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Uma,

     

    We do not document or support querying any Report Catalog tables.  If you need to find out what items are in the report catalog, use the ListChildren SOAP API to list them.  We do support the ExecutionLog (Table/View; version dependent) or the ExecutionLog2 View.    Otherwise, writing queries against the SSRS DB is not supported and the schema, content, interpretation of the content is subject to change without notice.

     

    Probably not the answer you wanted to hear...

    -Lukasz

    Wednesday, October 29, 2008 8:12 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Lukasz,

     

    Do you know if it is possible to copy a modelID to another model in the catalog table? Or is the ModelID stored in another table(s) within the ReportServer database?

     

    I had to generate a new report model and cannot overwrite the existing due to it having a different ModelID. I need to overwrite, as there are numerous reports that have been created against the original model?

     

    Thanks!

    -Carla

     

    Thursday, October 30, 2008 12:00 AM
    |
  • Question
    Sign in to vote
    7
    Sign in to vote

    Hi Uma,

     

    It is referring to the type of Path column.

     

    if the value was 1 then the path is a folder.

    Value 2 refers to Report

    Value 3 refers to Resource

    Value 4 refers to Linked Report

    Value 5 refers to Data Source

     

    i hope it makes you clear your doubt

     

     

    • Proposed as answer by maplemale Wednesday, January 11, 2012 11:49 PM
    Friday, November 14, 2008 6:29 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Carla - You can export the previous model and then upload it again to the server.  Unfortunately, model item ids are not guaranteed to be identical across multiple Generate Model calls. 

     

    Hope that helps,

    -Lukasz

    Monday, November 24, 2008 4:59 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you so much b_rusly
    Uma
    Monday, May 3, 2010 8:37 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All,

    I saw value 6 in type column of Catalog table, am wondering what does '6' represent?

    thanks,

    Ling

    Wednesday, March 2, 2011 6:03 AM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote
    6 = report model
    Friday, March 11, 2011 6:40 PM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    In case someone end up here using SQL Server 2008 R2, newly-added values since R2 are:

    8 = Shared Dataset
    9 = Report Part

    MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)

    Friday, March 2, 2012 8:21 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Valentino,

    can we get the detail of tables and views in ReportServer database?

    Sushant

    Tuesday, July 10, 2012 6:08 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Sushant,

    As Lukasz said, Microsoft does not document the internal report server tables/views.  However, that of course doesn't mean that you can't query them.  As long as you don't rely on the structure of the tables for any production systems, I don't see any harm in running select statements on those tables.

    Have a look at the script here for some interesting usage of the reportserver tables: http://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c

    MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)

    Saturday, July 14, 2012 1:28 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi valentine,

    Thanks for your answer. But can you help in getting the list of reports deployed in project web access site?

    in catalog table i can find out those names but those contain the deleted report names also. how can i separate reports from deleted reports to existing reports?

    Thanks

    Sushant


    Wednesday, July 25, 2012 12:35 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    in catalog table i can find out those names but those contain the deleted report names also. how can i separate reports from deleted reports to existing reports?

    The Catalog table does not contain deleted reports. Once they're deleted, they're gone from the table as well.  Are you sure you're not confusing "deleted" with "hidden"?  To filter out hidden reports, have a look at the Hidden column in the Catalog table:

    WHERE Hidden = 0


    MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)

    Tuesday, October 23, 2012 12:33 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    I am experiencing the same issue with deleted reports showing in the Catalog table. Any ideas why reports would still be showing in the table?
    • Edited by SqlSam Thursday, December 13, 2012 12:22 AM Can't type...apparently.
    Tuesday, December 11, 2012 11:01 PM
    |
  • Question
    Sign in to vote
    2
    Sign in to vote

    deleted reports should come off the catalogue table as soon as deleted.

    also for the Type use a case:

    CASE
    WHEN C.type = 1 THEN '1-Folder'
    WHEN C.type = 2 THEN '2-Report'
    WHEN C.type = 3 THEN '3-File'
    WHEN C.type = 4 THEN '4-Linked Report'
    WHEN C.type = 5 THEN '5-Datasource'
    WHEN C.type = 6 THEN '6-Model'
    WHEN C.type = 7 Then '7-ReportPart'
    WHEN C.type = 8 Then '8-Shared Dataset'
    ELSE '9-Unknown' END AS ItemType,

    by

    Raj Sharma - Lucky Dice

    Friday, September 26, 2014 8:32 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    They should be deleted from the catalogue as soon as you physically remove them.

    As a test - do a select all on the Catalog table and then create a report, you will notice an extra rows created.

    As soon as you delete this new report, your rows sould also go down by one.

    For the types, use the CASE statment below:

    CASE
    WHEN C.type = 1 THEN '1-Folder'
    WHEN C.type = 2 THEN '2-Report'
    WHEN C.type = 3 THEN '3-File'
    WHEN C.type = 4 THEN '4-Linked Report'
    WHEN C.type = 5 THEN '5-Datasource'
    WHEN C.type = 6 THEN '6-Model'
    WHEN C.type = 7 Then '7-ReportPart'
    WHEN C.type = 8 Then '8-Shared Dataset'
    ELSE '9-Unknown' END AS [ItemType]

    Lucky Dice - Decisive Technology

    Friday, September 26, 2014 8:51 AM
    |
  • Question
    Sign in to vote
    4
    Sign in to vote

    Adding SQL SSRS 2016+ items...

    CASE
    WHEN C.type = 1 THEN '1-Folder'
    WHEN C.type = 2 THEN '2-Report'
    WHEN C.type = 3 THEN '3-File'
    WHEN C.type = 4 THEN '4-Linked Report'
    WHEN C.type = 5 THEN '5-Datasource'
    WHEN C.type = 6 THEN '6-Model'
    WHEN C.type = 8 THEN '8-Shared Dataset'
    WHEN C.type = 9 THEN '9-Report Part'

    WHEN C.type = 11 THEN 'KPI'
    WHEN C.type = 12 THEN 'Mobile Report (folder)'
    WHEN C.type = 13 THEN 'Power BI Desktop Document'

    ELSE 'Unknown' END AS [ItemType]

    • Edited by JFoushee Monday, March 4, 2019 7:52 PM
    Monday, February 26, 2018 12:57 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for this

    Thanks Shiven:) If Answer is Helpful, Please Vote

    Wednesday, November 13, 2019 12:54 AM
    |