This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE ReportServer | |
/*Data Sources used in Reports*/ | |
SELECT | |
S.name AS 'Report Name' | |
,C.Name AS 'DataSource' | |
FROM dbo.[Catalog] S (NOLOCK) | |
INNER JOIN DataSource DS (NOLOCK) ON (s.ItemID = DS.ItemID) | |
INNER JOIN dbo.[Catalog] C (NOLOCK) ON (DS.Link = C.ItemID) | |
WHERE |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE ReportServer | |
DECLARE @Today DATETIME | |
SELECT @Today = CAST(CONVERT(VARCHAR(10),GETDATE(),101) AS DATETIME) | |
/*Check to see if temp table exists in current connection*/ | |
IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP | |
/*Determine all subscriptions that have errored or are pending.*/ | |
SELECT |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
BEGIN | |
SET NOCOUNT ON; | |
/************************************************************* | |
Insert Timeframe 0 - 6 days into table variable. | |
*************************************************************/ | |
DECLARE | |
@integer Int | |
DECLARE @Projected TABLE (timeframe CHAR(10), Successes INT, Contacts INT) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/************************************************************* | |
Insert Timeframe 0 - 6 days into table variable. | |
*************************************************************/ | |
DECLARE @integer Int | |
DECLARE @Projected TABLE (timeframe CHAR(10)) | |
SELECT @integer = 0 | |
WHILE @integer < 7 | |
BEGIN | |
INSERT INTO @Projected(timeframe) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
DISTINCT P.timeframe | |
,* | |
FROM @Projected P | |
INNER JOIN ( | |
SELECT | |
s1.timeframe | |
,RunningTotal = (SELECT SUM(s2.Contacts) | |
FROM @Projected s2 | |
WHERE s2.timeframe <= s1.timeframe) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
BEGIN | |
INSERT INTO @Projected (timeframe, Successes, Contacts) | |
(SELECT | |
P.timeframe | |
,SUM(Successes) AS 'Successes' | |
,SUM(Contacts) AS 'Contact' | |
FROM CalculatedData C | |
RIGHT JOIN @Projected P ON (P.timeframe = datediff(dd,AddedDate,GETDATE())) | |
GROUP BY p.timeframe, datediff(dd,AddedDate,GETDATE()) | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/*Execute SSRS Subscription Manually*/ | |
USE ReportServer | |
SELECT | |
S.ScheduleID AS SQLAgent_Job_Name | |
,SUB.Description AS Sub_Desc | |
,SUB.DeliveryExtension AS Sub_Del_Extension | |
,C.Name AS ReportName | |
,C.Path AS ReportPath | |
FROM ReportSchedule RS (NOLOCK) | |
INNER JOIN Schedule S (NOLOCK) ON (RS.ScheduleID = S.ScheduleID) |
NewerOlder