PowerShell: Find SQL Instances installed on...

I've been spending a lot of time lately learning and writing PowerShell scripts. I didn't realize how powerful these were until a recent SQL Saturday, where I sat in on a class and was amazed by all the stuff these scripts could do. Most of what I've written is based off of databases we have locally here at my work, but I've come up with a generic script that tells you all the instances of SQL Server running on a specific Windows server. You enter the server name in the $server variable on line 2 and the script executes, makes a WMI connection and returns the services installed that are for SQL Server.

cls
$server = "localhost"    #Do not put an instance here...this is just the root server

$obje = Get-WmiObject -ComputerName $server win32_service | where {($_.name -like "MSSQL$*" -or $_.name -like "MSSQLSERVER" -or $_.name -like "SQL Server (*") -and $_.name -notlike "*helper*" -and $_.name -notlike "*Launcher*"}
cls
Write-Host " "
Write-Host "Server: " $server
Write-Host " "
if ($obje -is [System.Array])
{
 for ($i=0; $i -lt $obje.Length; $i++)
 {
  $j = $i+1
  Write-Host "Instance $j ..."
  Write-Host "Service Name:  "  $obje[$i].Name
  Write-Host "Service Desc:  "  $obje[$i].DisplayName
  Write-Host "Start Mode:    "  $obje[$i].StartMode
  Write-Host "Service State: "  $obje[$i].State
  Write-Host "Service Status:" $obje[$i].Status
  Write-Host " "
 }
}else
{
 Write-Host "Service Name:  "  $obje.Name
 Write-Host "Service Desc:  "  $obje.DisplayName
 Write-Host "Start Mode:    "  $obje.StartMode
 Write-Host "Service State: "  $obje.State
 Write-Host "Service Status:" $obje.Status
}

SQL Saturday 149 Scripts and stuff

Hello everyone,

I had a great time meeting other SQL Server Professionals at SQL Saturday 149 in Minneapolis at the U of M campus, and chatting up with people afterwards at the after party. To those of you who attended my session on Execution Plans, I'd like to say thank you, I hope you took away some useful information; please feel free to email me directly with questions and please connect with me on LinkedIn. I also want to thank you for your feedback on my session; I appreciate the comments, suggestions and observations, it will help me better present in the future.

All the queries that I used in my presentation can be found here: http://ericemployed.blogspot.com/2011/11/sqlsaturday-99-scripts.html

SQL Saturday 149 Announcement

I found out that my submission for SQL Saturday 149 was accepted and I will again be presenting at the MN SQL Saturday this year. If you haven't signed up yet and you will be in the Twin Cities MN area on Saturday September 29th, 2012, you are missing out on one of the best SQL Server conferences around. This year, the event will be held at the University of Minnesota - Keller Hall, 200 Union Street SE, Minneapolis, MN 55455. Admittance to this event is free, but they do charge a lunch fee of 10.00 so that they can provide a lunch - not pizza! Please register soon as seating is limited, and let friends and colleages know about the event. You can find out more about this (and register!!) at the event homepage: http://www.sqlsaturday.com/149/eventhome.aspx

Extended Events with a view

So last time I wrote, I talked about my interest in Extended Events; I have been playing with them a bit since then and I came across something that made them so much easier to work with. This is not my own idea, but I wanted to share it as it blew my mind away with how simple it is, yet how I never thought of it. The idea is to take your ugly XQuery code and put it in a view, and to make it even better, create a Schema just for your Extended Events. I created a Schema called "XE" then when I want to get data from an Extended Event, I just have to type SELECT * FROM [XE]. and I let IntelliSense or SQLPrompt fill out the available options. This essentially makes querying Extended Events as easy as using a DMV. So in my last post, we created a Extended Even called "XE_Log_Shrink", If I wanted to implement this idea for this XE, I would do the following: Create the Schema (if I haven't previously done this):

USE [master]
GO
CREATE SCHEMA [XE] AUTHORIZATION [dbo]
GO

Create the View (using the XQuery from my previous post:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/************************************************************
 * AUTHOR: Eric Zierdt          *
 * CREATED: 05/08/2012          *
 * USAGE: SELECT * FROM [XE].[vw_Log_Shrink]  *
 ************************************************************/
CREATE VIEW [XE].[vw_Log_Shrink]
AS
 
WITH Data
AS (
 SELECT CAST(target_data AS XML) AS TargetData
 FROM sys.dm_xe_session_targets dt
 JOIN  sys.dm_xe_sessions ds ON ds.address = dt.event_session_address
 JOIN  sys.server_event_sessions ss ON ds.Name = ss.Name
 WHERE dt.target_name = 'ring_buffer'
  AND ds.Name = 'XE_Log_Shrink'
)
SELECT  DATEADD(hour,-5,XEventData.XEvent.value('(@timestamp)[1]', 'datetime'))  AS event_timestamp
   ,XEventData.XEvent.value('@name', 'varchar(4000)')       AS event_name
   ,DB_NAME(XEventData.XEvent.value('(data/value)[2]', 'VARCHAR(100)'))  AS DatabaseName
   ,XEventData.XEvent.value('(action/value)[2]', 'VARCHAR(512)')    AS client_hostname
   ,XEventData.XEvent.value('(action/value)[3]', 'VARCHAR(512)')    AS nt_username
   ,XEventData.XEvent.value('(action/value)[6]', 'VARCHAR(512)')    AS username
   ,XEventData.XEvent.value('(action/value)[5]', 'VARCHAR(512)')    AS sql_text
   ,XEventData.XEvent.value('(action/value)[4]', 'VARCHAR(512)')    AS session_id
   ,XEventData.XEvent.value('(action/value)[1]', 'VARCHAR(512)')    AS client_app_name
FROM Data d
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData ( XEvent )

GO

Thats all you have to do, now you can query this like a DMV, just write:

SELECT *
FROM [XE].[vw_Log_Shrink]
WHERE [event_timestamp] > '5/8/2012'

Let me know your thoughts.

Extended Event - Check for Log Shrink and Email

I saw SQL Server MVP Jason Strate give a presentation a year or two ago on Extended Events. One situation I remember him giving was that he was having problems with a log file shrinking every so often; for some reason this caused some issues (auto-grow or something). So he used Extended Events to capture the shrink and get more details on it. This problem sounded interesting to me and I wanted to see if I could generate some code to do this from scratch, this is what I came up with:

First we need to create the Extended Event (hereafter referred to as XE) to capture the log shrink:

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name = 'XE_Log_Shrink')
    DROP EVENT SESSION XE_Log_Shrink ON SERVER
GO
CREATE EVENT SESSION XE_Log_Shrink ON SERVER -- Session Name
ADD EVENT sqlserver.databases_log_shrink -- Event we want to capture
(
    ACTION -- What contents to capture
        (
        sqlserver.client_app_name
        ,sqlserver.client_hostname
        ,sqlserver.nt_username
        ,sqlserver.session_id
        ,sqlserver.sql_text
        ,sqlserver.username
        )
)
ADD TARGET package0.ring_buffer WITH (MAX_DISPATCH_LATENCY = 1 SECONDS, STARTUP_STATE = ON) -- The target
GO

ALTER EVENT SESSION XE_Log_Shrink ON SERVER STATE = START

Next to check it, we need to shrink a log (I'll auto grow it again, so I can redo this numerous times if I need to)

USE [AdventureWorks]
GO
DBCC SHRINKFILE (N'AdventureWorks_Log' , 1)
GO

USE [master]
GO
ALTER DATABASE [AdventureWorks] MODIFY FILE ( NAME = N'AdventureWorks_Log', SIZE = 100MB )
GO

Lets check the XE to see if we captured the event:

;WITH Data
AS (
 SELECT CAST(target_data AS XML) AS TargetData
 FROM sys.dm_xe_session_targets dt
 JOIN  sys.dm_xe_sessions ds ON ds.address = dt.event_session_address
 JOIN  sys.server_event_sessions ss ON ds.Name = ss.Name
 WHERE dt.target_name = 'ring_buffer'
  AND ds.Name = 'XE_Log_Shrink'
)
SELECT  DATEADD(hour,-5,XEventData.XEvent.value('(@timestamp)[1]', 'datetime'))  AS event_timestamp
   ,XEventData.XEvent.value('@name', 'varchar(4000)')       AS event_name
   ,DB_NAME(XEventData.XEvent.value('(data/value)[2]', 'VARCHAR(100)'))  AS DatabaseName
   ,XEventData.XEvent.value('(action/value)[2]', 'VARCHAR(512)')    AS client_hostname
   ,XEventData.XEvent.value('(action/value)[3]', 'VARCHAR(512)')    AS nt_username
   ,XEventData.XEvent.value('(action/value)[6]', 'VARCHAR(512)')    AS username
   ,XEventData.XEvent.value('(action/value)[5]', 'VARCHAR(512)')    AS sql_text
   ,XEventData.XEvent.value('(action/value)[4]', 'VARCHAR(512)')    AS session_id
   ,XEventData.XEvent.value('(action/value)[1]', 'VARCHAR(512)')    AS client_app_name
FROM Data d
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData ( XEvent )

So, we should see the event now. But lets take it a step further, lets say we want to get emailed when it happens. I've created this stored proc which will check once an hour (this is a variable, you can configure it as you see fit, but keep it under 2 hours or else you'll have to modify my waitfor logic...or you could remove the waitfor logic and just run it from the scheduler in the agent job you setup, just match up the @How_Often variable with how often your job fires)

USE master
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===========================================================
-- Author:  Eric Zierdt
-- Create date: 4/19/2012
-- Description: Checks for Log Shrinkage and sends email
-- URL:   http://ericemployed.blogspot.com
-- Usage:  exec Email_On_Log_Shrinkage 60
-- ===========================================================
ALTER PROCEDURE Email_On_Log_Shrinkage
 -- Add the parameters for the stored procedure here
 @How_Often INT = 60
AS
BEGIN
 SET NOCOUNT ON
 Start_Code:
 --DECLARE @How_Often INT = 60
 DECLARE @RowCount INT = 0
   ,@SQL VARCHAR(MAX) = ''

 IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#TempData'))
 BEGIN
  DROP TABLE #TempData
 END

 ;WITH Data
 AS (
  SELECT CAST(target_data AS XML) AS TargetData
  FROM sys.dm_xe_session_targets dt
  JOIN  sys.dm_xe_sessions ds ON ds.address = dt.event_session_address
  JOIN  sys.server_event_sessions ss ON ds.Name = ss.Name
  WHERE dt.target_name = 'ring_buffer'
   AND ds.Name = 'XE_Log_Shrink'
 )
 SELECT  DATEADD(hour,-5,XEventData.XEvent.value('(@timestamp)[1]', 'datetime'))  AS event_timestamp
   ,XEventData.XEvent.value('@name', 'varchar(4000)')        AS event_name
   ,DB_NAME(XEventData.XEvent.value('(data/value)[2]', 'VARCHAR(100)'))   AS DatabaseName
   ,XEventData.XEvent.value('(action/value)[2]', 'VARCHAR(512)')     AS client_hostname
   ,XEventData.XEvent.value('(action/value)[3]', 'VARCHAR(512)')     AS nt_username
   ,XEventData.XEvent.value('(action/value)[6]', 'VARCHAR(512)')     AS username
   ,XEventData.XEvent.value('(action/value)[5]', 'VARCHAR(512)')     AS sql_text
   ,XEventData.XEvent.value('(action/value)[4]', 'VARCHAR(512)')     AS session_id
   ,XEventData.XEvent.value('(action/value)[1]', 'VARCHAR(512)')     AS client_app_name
 INTO #TempData
 FROM Data d
 CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData ( XEvent )
 WHERE  DATEADD(hour,-5,XEventData.XEvent.value('(@timestamp)[1]', 'datetime')) > DATEADD(MINUTE,-1*@How_Often,GETDATE())
 
 SELECT @RowCount = COUNT(1) FROM #TempData
 IF @RowCount > 0
 BEGIN
  PRINT CAST(@RowCount AS VARCHAR(10)) + ' Log Shrink records were found'
  SET @SQL = '






'
  DECLARE @DBName   VARCHAR(120)
    ,@HostName  VARCHAR(120)
    ,@TimeStamp  VARCHAR(120)
    ,@UserName  VARCHAR(120)
    ,@SQL_Text  VARCHAR(120)
  DECLARE ZCursor CURSOR FOR
  (
   SELECT DatabaseName
       ,client_hostname
       ,CAST(event_timestamp AS VARCHAR(120)) AS TimeStamp
       ,username
       ,sql_text
   FROM #TempData
  )
  OPEN ZCursor 
  FETCH NEXT FROM ZCursor INTO @DBName,@HostName,@TimeStamp,@UserName,@SQL_Text
   WHILE @@FETCH_STATUS = 0
   BEGIN
    SET @SQL += '



'

    FETCH NEXT FROM ZCursor INTO @DBName,@HostName,@TimeStamp,@UserName,@SQL_Text
   END

  CLOSE ZCursor
  DEALLOCATE ZCursor
  SET @SQL += '
 
  
Server
Database Name
Time Stamp
Client Hostname
Username
SQL_Text
 
 
  
' + @@SERVERNAME + '
' + @DBName + '
' + @TimeStamp + '
' + @HostName + '
' + @UserName + '
' + @SQL_Text + '
 
    
'
 END
 ELSE
  PRINT 'No Shrinks in the time frame requested'

 --PRINT @SQL
 
 EXEC msdb.dbo.sp_send_dbmail
   @recipients = '[Your Email Here]',
   @body = @SQL,
   @subject = 'Log Shrink Detected',
   @profile_name = '[Your Profile Name Here]',
   @body_format = 'html';
 
 DROP TABLE #TempData
 
 DECLARE @Delay VARCHAR(10)
   --,@How_Often INT = 60
 IF @How_Often < 60
  SET @Delay = '00:' + RIGHT('00' + CAST(@How_Often AS VARCHAR(2)),2) + ':00'
 ELSE
  SET @Delay = '01:' + RIGHT('00' + CAST(@How_Often-60 AS VARCHAR(2)),2) + ':00'

 WAITFOR DELAY @Delay
  GOTO Start_Code
END
GO

Then the last step is to call this proc from a agent job. Iif you use the WaitFor logic, I'd still set a schedule to run every hour, just in case it fails...but the more I think about this, the more I prefer not using the WaitFor logic and just running the job every hour; but if the job fails, you won't get emailed.

--Eric

SSRS Queries

I've recently been working with SSRS and needed to write some queries to get more knowledge about what was happening on the server.

One of the first things I wanted to know was what subscriptions had run that did not succeed. This query should be run on the server that hosts your ReportServer Database. I'm running these on SQL Server 2008R2

USE [ReportServer]
--GET THE SERVER NAME
DECLARE @ServerURL VARCHAR(MAX)
SELECT TOP 1 @ServerURL =[MachineName]
FROM [dbo].Keys
WHERE [MachineName] IS NOT NULL

IF CHARINDEX('\',@ServerURL) > 0
SET @ServerURL = LEFT(@ServerURL,CHARINDEX('\',@ServerURL)-1)

SET @ServerURL = 'http://' + @ServerURL + '/Reports/Pages'

--FIND THE NON-SUCCESSFUL SUBSCRIPTIONS
;WITH AgentJobsCTE AS (
  SELECT [SJ].job_id
     ,[SJ].name AS AgentJobName
     ,[SJS].[command]
     ,CAST(REPLACE(REPLACE(REPLACE([SJS].[command],'exec [ReportServer].dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''',''),'exec ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''',''), '''', '') AS SYSNAME) AS SubscriptionID
  FROM  msdb.[dbo].[sysjobs] AS SJ
  JOIN   msdb.[dbo].[sysjobsteps] AS SJS ON [SJ].[job_id] = [SJS].[job_id]
  WHERE  SJS.[command] LIKE '%TimedSubscription%'
)
SELECT O.[UserName] AS OwnerName,M.[UserName] AS ModifiedBy,C.[Name] AS ReportName,[AJ].AgentJobName,S.[SubscriptionID],S.[LastStatus],S.[LastRunTime],S.[ModifiedDate],S.[Report_OID]
   ,@ServerURL + '/SubscriptionProperties.aspx?ItemPath=' + REPLACE(REPLACE(C.Path,'/','%2f'),' ','+') + '&IsDataDriven=False&SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(MAX)) AS SubscriptionURL
   ,@ServerURL + '/Report.aspx?ItemPath=' + REPLACE(REPLACE(C.Path,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS ReportURL
FROM [ReportServer].[dbo].[Subscriptions] AS S
JOIN  [ReportServer].[dbo].[Users] AS O ON [S].[OwnerID] = [O].[UserID]
JOIN  [ReportServer].[dbo].[Users] AS M ON S.[ModifiedByID] = [M].[UserID]
JOIN  [ReportServer].[dbo].[Catalog] AS C ON [S].[Report_OID] = [C].[ItemID]
LEFT JOIN [AgentJobsCTE] AJ ON CAST(S.[SubscriptionID] AS SYSNAME) = AJ.[SubscriptionID]
WHERE [LastRunTime] >= '04/02/2012 15:00' --NOT NEEDED, BUT MAKES YOUR LIST SHORTER
 AND [LastStatus] NOT LIKE 'Mail sent to%'
 AND [LastStatus] NOT LIKE 'The file "%'
           --AND [LastStatus] NOT LIKE 'Pending%' --Pending are currently running.
ORDER BY [LastRunTime] DESC

I noticed that I was seeing a number of subscriptions currently running for the same report, many run at the same time. I wrote a query to tell me which subscriptions had the same "report parameter values". This doesn't look at the subscription start time or days of the week, just the parameters...so you'll need to do some investigation (the next query will help with that)

USE [ReportServer]
DECLARE @ServerURL VARCHAR(MAX)
SELECT TOP 1 @ServerURL =[MachineName]
FROM [dbo].Keys
WHERE [MachineName] IS NOT NULL

IF CHARINDEX('\',@ServerURL) > 0
SET @ServerURL = LEFT(@ServerURL,CHARINDEX('\',@ServerURL)-1)

SET @ServerURL = 'http://' + @ServerURL + '/Reports/Pages'

;WITH DuplicateSubscriptions AS (
SELECT C.Name AS ReportName
    ,CAST(Parameters AS VARCHAR(MAX)) AS Parameters
    ,[Report_OID]
    ,C.Path
    ,COUNT(1) AS [Count]
FROM [ReportServer].[dbo].[Subscriptions] S
JOIN  [ReportServer].[dbo].[Catalog] AS C ON [S].[Report_OID] = [C].[ItemID]
LEFT JOIN [dbo].[ReportSchedule] AS RS ON [S].[SubscriptionID] = [RS].[SubscriptionID]
LEFT JOIN [dbo].[Schedule] AS SC ON RS.[ScheduleID] = SC.[ScheduleID]
WHERE (SC.EndDate IS NULL OR (SC.EndDate IS NULL AND SC.[RecurrenceType] <> 1))

GROUP BY CAST(Parameters AS VARCHAR(MAX)),[Report_OID],C.Name,C.Path
HAVING COUNT(1) > 1
)
SELECT *
   ,@ServerURL + '/Report.aspx?ItemPath=' + REPLACE(REPLACE(Path,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS URL
FROM [DuplicateSubscriptions]
--ORDER BY [ReportName], [Count] DESC
ORDER BY [Count] DESC, [ReportName]

So when you find a possible duplicate subscription in the previous list, get the Report_OID and Parameters field and past them into the variables in this query and execute to see if it needs more investigation. I usually look at the "LastRun" field and see if there are any duplicates in that list. If you need to dig in more, the query provides links to both the subscription webpage and the report management page listing all subscriptions

USE [ReportServer]
-- VIEW ALL (NON EXPIRED) SUBSCRIPTIONS FOR A SPECIFIC REPORT WITH SPECIFIC PARAMETERS
DECLARE @ServerSubscriptionURL VARCHAR(MAX)
SELECT TOP 1 @ServerSubscriptionURL =[MachineName]
FROM [dbo].Keys
WHERE [MachineName] IS NOT NULL

IF CHARINDEX('\',@ServerSubscriptionURL) > 0
SET @ServerSubscriptionURL = LEFT(@ServerSubscriptionURL,CHARINDEX('\',@ServerSubscriptionURL)-1)

SET @ServerSubscriptionURL = 'http://' + @ServerSubscriptionURL + '/Reports/Pages'

DECLARE @ReportID sysname = 'F9999999-888G-7H77-I9I9-000000J00000' -- ReportID from previous query
 ,@ParameterList VARCHAR(MAX) = '[parameters]'-- Parameters from previous query

SELECT C.Name AS ReportName
   ,S.[SubscriptionID]
   ,S.[Description] Descr
   ,S.[LastRunTime] LastRun
   ,S.[LastStatus]
   --,SC.[EndDate]
   --,SC.[RecurrenceType]
   ,U.[UserName] AS Owner
   ,@ServerSubscriptionURL + '/SubscriptionProperties.aspx?ItemPath=' + REPLACE(REPLACE(C.Path,'/','%2f'),' ','+') + '&IsDataDriven=False&SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(MAX)) AS SubscriptionURL
   ,@ServerSubscriptionURL + '/Report.aspx?ItemPath=' + REPLACE(REPLACE(C.Path,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS ReportURL
FROM [ReportServer].[dbo].[Subscriptions] S
JOIN [ReportServer].[dbo].[Users] AS U ON [S].[OwnerID] = [U].[UserID]
JOIN  [ReportServer].[dbo].[Catalog] AS C ON [S].[Report_OID] = [C].[ItemID]
LEFT JOIN [dbo].[ReportSchedule] AS RS ON [S].[SubscriptionID] = [RS].[SubscriptionID]
LEFT JOIN [dbo].[Schedule] AS SC ON RS.[ScheduleID] = SC.[ScheduleID]
WHERE Report_OID = @ReportID
 AND CAST(S.Parameters AS VARCHAR(MAX)) = @ParameterList
 AND (SC.EndDate IS NULL OR (SC.EndDate IS NULL AND SC.[RecurrenceType] <> 1))
ORDER BY S.Description

But wait, thats not all, some additional interesting queries:
See all currently running reports

SELECT [JobID],[StartDate],[ComputerName],[RequestName],[RequestPath]
   ,[Description],[Timeout] AS Timeout, DATEDIFF(ss, startdate, GETDATE()) AS SecondsSinceRun
   , DATEDIFF(mi, startdate, GETDATE()) AS MinutesSinceRun
   ,[JobAction],[JobType],[JobStatus]
 ,'http://[reporturl]/Reports/Pages/Report.aspx?ItemPath=' + REPLACE(REPLACE(RJ.RequestPath,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS ReportURL
FROM [ReportServer].[dbo].[RunningJobs] AS RJ

See the run information for a specific report (comment out the RequestType if you want to see things other than subscriptions)

SELECT *
FROM [ReportServer].[dbo].[ExecutionLog2] AS EL
WHERE [ReportPath] LIKE '%[report name=""]' AND [RequestType] = 'Subscription'
ORDER BY [TimeStart] DESC

View a schedule for a specific Subscription

-- VIEW SCHDULE DATA FOR A SPEICIFC SUBSCRIPTION
SELECT *
FROM [dbo].[Schedule] AS S
JOIN [dbo].[ReportSchedule] AS RS ON [S].[ScheduleID] = [RS].[ScheduleID]
WHERE RS.[SubscriptionID] = '[subscriptionid]'

Ok, one last one, this lets you see all expired (past the end date) and one time subscriptions (useful if you want to clean them up)

-- EXPIRED AND ONE TIME RUN SUBSCRIPTIONS

DECLARE @ServerURL VARCHAR(MAX)
SELECT TOP 1 @ServerURL =[MachineName] 
FROM [dbo].Keys
WHERE [MachineName] IS NOT NULL

IF CHARINDEX('\',@ServerURL) > 0
 SET @ServerURL = LEFT(@ServerURL,CHARINDEX('\',@ServerURL)-1)

SET @ServerURL = 'http://' + @ServerURL + '/Reports/Pages'

;WITH ExpiredSubscriptions AS 
(
 SELECT RS.[SubscriptionID], S.[EndDate] 
 FROM [dbo].[Schedule] AS S 
 JOIN [dbo].[ReportSchedule] AS RS ON [S].[ScheduleID] = [RS].[ScheduleID]
 WHERE S.[EndDate] < GETDATE() OR S.[RecurrenceType] = 1
)
SELECT S.[SubscriptionID]
    ,C.Name AS ReportName
    ,S.[Description] Descr
    ,S.[LastRunTime] LastRun
    ,ES.[EndDate]
    ,S.[LastStatus]
    ,U.[UserName] AS Owner
    ,@ServerURL + '/SubscriptionProperties.aspx?ItemPath=' + REPLACE(REPLACE(Path,'/','%2f'),' ','+') + '&IsDataDriven=False&SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(MAX)) AS SubscriptionURL 
    ,@ServerURL + '/Report.aspx?ItemPath=' + REPLACE(REPLACE(Path,'/','%2f'),' ','+') + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab&SortBy=LastExecuted&IsAscending=false' AS ReportURL
FROM Subscriptions S
JOIN [dbo].[Users] AS U ON [S].[OwnerID] = [U].[UserID]
JOIN  [ReportServer].[dbo].[Catalog] AS C ON [S].[Report_OID] = [C].[ItemID]
JOIN [ExpiredSubscriptions] ES ON S.[SubscriptionID] = ES.[SubscriptionID]
ORDER BY C.[Name], ES.[EndDate] DESC

If you have any other good queries, please drop them in a comment below

MinneBar

Thanks to everyone who came out to see my presentation at MinneBar, it was a lot of fun meeting so many talented and energetic people.

Remember you can connect with me on Twitter: @EricZierdt or via the email address I gave out at the presentation, or my linked in Profile: http://www.linkedin.com/in/ericzierdt

New Job

Hello to anyone who follows my blog; I know I don't post on regular intervals, but I was in Job Search mode for the past few months and just started a new position last week! So it may take me some time to get back into writing posts, but I'll try to come up with something cool to post on soon.