Sql-server – SSRS Data Source enabled/disabled

sql serverssrsssrs-2014

This is completely lame that I cannot find it, so I have to ask. I know in the reportsmanager site where to enable and disable data sources. But where in the tables of the reporting database is this stored? I've found the data source in the Catalog table, type 5, but don't see anything that remotely looks like a disabled/enabled field. I've also checked several of the other tables, like Policies and DataSource and SecData and etc…, but cannot find it. Can someone enlighten me please. I want to programmtically enable and disable data sources for certain reports, hence the reason I ask. SQL 2014 Standard. Thanks.

Best Answer

It's stored in the Content column in the Catalog table as XML, and the Content column is in binary - so you'll need to decode it from binary and find the Enabled XML element. For example, this will give you a list of the shared Data Sources with their enabled/disabled status:

;WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource

SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.Enabled
FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('Enabled[1]', 'bit') AS [Enabled]
     FROM SDS
          CROSS APPLY 
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON
Related Question