Friday, January 31, 2014

SQL Query to find the Report subscription details for a CRM on premise

Below query has to be run against the ReportServer Database.Which will give you the report name, to address, CC & BCC and last report ran time details. i Found this query very handy when there are lots of report subscriptions going from your CRM.. :)

USE ReportServer
GO
DECLARE @Subscriptions TABLE (
                Report_OID  UNIQUEIDENTIFIER,
                ToList      VARCHAR(8000),
                CCList      VARCHAR(8000),
                BCCList     VARCHAR(8000),
                SubjectLine VARCHAR(8000))DECLARE @ExtensionSettings XML
DECLARE @Report_OID UNIQUEIDENTIFIER
DECLARE @idoc  INT
DECLARE SubscriptionList CURSOR FOR
SELECT Report_OID,ExtensionSettings
    FROM Subscriptions SubscriptionList
   Open SubscriptionList
    FETCH NEXT     FROM SubscriptionList INTO @Report_OID ,@ExtensionSettings
     WHILE (@@FETCH_STATUS=0)
    BEGIN
    EXEC sp_xml_preparedocument @idoc OUTPUT, @ExtensionSettings
    INSERT INTO @Subscriptions
        SELECT @Report_OID,[TO],[CC],[BCC],[Subject]
        FROM
        (
        SELECT *
            FROM OPENXML (@idoc, '/ParameterValues/ParameterValue')
                    WITH (Name NVARCHAR(1500) 'Name',
                        Value NVARCHAR(1500) 'Value')
        ) AS SourceTable
                pivot
       (
                    MAX(value) FOR [Name] IN ([TO],[BCC],[CC],[Subject])
        ) AS pivottable
    EXEC sp_xml_removedocument @idoc
   FETCH NEXT  FROM SubscriptionList INTO @Report_OID ,@ExtensionSettings

    END
            CLOSE SubscriptionList
            DEALLOCATE SubscriptionList

SELECT c.CreationDate,c.ExecutionTime,c.path,c.name,s.Tolist,s.cclist,s.bcclist,s.subjectline    FROM
Catalog c INNER JOIN @Subscriptions  s ON c.ItemID = s.Report_OID    ORDER BY [path], Name



select C.Description,C.ExecutionTime,C.ExecutionFlag,C.Type,C.CreationDate,S.LastRunTime,S.EventType,S.DeliveryExtension,S.InactiveFlags from Catalog C inner join Subscriptions S on C.ItemID=S.Report_OID




Meanwhile in the Dynamics CRM 2011!!

CRM 2011 has got a new update too

Rollup 16 has been released for the CRM 2011 version
Dynamics CRM 2011 Rollup 16 available for download


Latest SDK for CRM 2011:
CRM 2011 sdk latest

Watch this space for any new downloads information related to CRM 2011

CRM 2013 rollup one is available now!! (watch this space for any CRM 2013 related downloads)

CRM 2013 on-premise installer:
CRM 2013 On premise Source file

CRM 2013 Roll-up 1 is available for download:
CRM 2013 Roll-up 1

CRM 2013 Latest SDK download:
CRM 2013 SDK