Tuesday, January 24, 2017

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

No comments:

Post a Comment