kristinaconley

/*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) 

kristinaconley

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())
        )

kristinaconley

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)

kristinaconley

/*************************************************************
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)

kristinaconley

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)

kristinaconley

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 

kristinaconley

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

kristinaconley

USE ReportServer
 
SELECT
  C.Name AS ReportName
  ,CASE 
  	WHEN NEXT_RUN_DATE > 0 
				THEN DATEADD(N,(NEXT_RUN_TIME%10000)/100
            ,DATEADD(HH,NEXT_RUN_TIME/10000
            ,CONVERT(DATETIME,CONVERT(VARCHAR(8),NEXT_RUN_DATE),112)))  
		ELSE CONVERT(DATETIME,CONVERT(VARCHAR(8),'19000101'),112) 

kristinaconley

/*SQL 2000 Find Column*/
SELECT 
  SO.NAME AS 'Table Name'
	,SC.NAME AS 'Column Name'
	,SM.TEXT AS 'Default Value'
FROM dbo.sysobjects SO (NOLOCK)
	INNER JOIN dbo.syscolumns SC (NOLOCK) ON (SO.id = SC.id)
	LEFT JOIN dbo.syscomments SM (NOLOCK) ON (SC.cdefault = SM.id)
WHERE 
	SO.xtype = 'U'  

kristinaconley

DECLARE @string VARCHAR(255)
SET @string='Insert String You Are Searching'

/*Drop temp table if already exists in connection.*/
IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data

/*Gather Data*/
SELECT 
  DISTINCT 
		CASE sysobjects.TYPE