Tuesday, January 24, 2017

Extract SSRS reports objects

The script below is:
·       Retrieving object names from Data Sources of SSRS reports stored as XML on Report Server database
·       Selecting these objects definitions from information schema of the desirable database
·       Searching a keyword in the report definition

;WITH
     XMLNAMESPACES
         (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'
                 ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'
          AS rd)
    ,DEF AS
        (SELECT RPT.ItemID
                ,RPT.Name
                ,RPT.ReportPath
                ,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName
                ,CommandText = REPLACE(REPLACE(LTRIM((R.RptNode.value('(./Query/CommandText)[1]', 'nvarchar(4000)'))),'&gt;', '>'),'&lt;', '<')
         FROM (SELECT RPT.ItemID
                        ,RPT.Name
                        ,RPT.Path AS ReportPath
                     ,RPT.name AS ReportName
                     ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
               FROM ReportServer.dbo.[Catalog] AS RPT
               WHERE RPT.Type in (2, 5, 7, 8)
             ) AS RPT
         CROSS APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS R(RptNode))

    SELECT   DEF.Name ReportName
            ,DEF.ReportPath
            ,DEF.DataSetName
            ,DEF.CommandText
            ,o.OBJECT_NAME
            ,o.OBJECT_DEFINITION
    FROM DEF
        ,(select o.name as OBJECT_NAME
                , m.definition as OBJECT_DEFINITION
            from sys.sql_modules m
            inner join sys.objects o on o.object_id = m.object_id
            WHERE (o.type in ('P','V','TF','IF','FN')
            AND (m.definition Like '%fld_current%'
            OR m.definition Like '%originalcost%')
            )
    ) o
    WHERE DEF.CommandText like '%' + o.object_name + '%'

Dynamic CRM and SSRS integration script

The T-SQL script below is:
  • Connecting to CRM server
  • Storing CRM's guid and Report Name in the #Table temporary table
  • Converts this data to serious of insert statements and exports them to the text file UploadTempTable.sql
  • Connecting to the Report Server box
  • Creating #Table temporary table
  • Running  UploadTempTable.sql which populates the #Table table
  • Displaying report attributes and last time it was run
:CONNECT COA-PROD-SQL12\CRM
GO

USE [coateshire_MSCRM]
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('tempdb..#Table') IS NOT NULL DROP Table #Table
GO
SELECT [ReportId],[Name]
  INTO #Table
  FROM [dbo].[ReportBase]
GO

:OUT C:\temp\UploadTempTable.sql
 
  SELECT 'insert into #Table(ReportID, Name) values ('
  +  IIF(ReportId is null, 'Null','''' + CAST(ReportId AS VARCHAR(50)) + '''')
  + ', '''+ REPLACE(Name, '''', '"') + ''')'
  FROM #Table
GO

:OUT stdout
GO

:CONNECT COA-PROD-SQL01\ERP
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('tempdb..#Table') IS NOT NULL DROP Table #Table
CREATE TABLE #Table(ReportID nvarchar(50)
                    , Name nvarchar(425))
GO

:R C:\temp\UploadTempTable.sql
GO

USE ReportServer
GO

SELECT *
FROM #Table RIGHT JOIN
    (SELECT *
        FROM(SELECT Name = REPLACE(REPLACE(c.Name,'{',''),'}','')
                , c.Path
                ,MAX(e.TimeEnd) AS MaxDate
            FROM   dbo.ExecutionLogStorage AS e RIGHT JOIN dbo.Catalog AS c ON c.ItemID = e.ReportID
            WHERE c.type in (2, 5, 7, 8)
            GROUP BY c.Name, c.Path) t) t
ON #Table.ReportID = t.Name collate Latin1_General_CI_AS_KS_WS
WHERE MaxDate is not null
GO