Calculate Subscription Expiration Threshold

While I was piecing together a few queries for a report intended to deliver subscription information about subscribers that are nearing the expiration threshold for replication I found myself needing to calculate warning threshold levels. In one case I found not only do I need to calculate the threshold warning criteria but I also needed to round the outcome to the nearest whole integer.

As you probably know the default retention period for replication defaults to 14 days; however, in replication monitor you can set up warnings to indicate if a subscriber is nearing the threshold. Typically this is 80% so in short I am going to take the retention value and multiple it by .8 and round to the nearest whole integer. For this example I really only need to query data from one table [MSreplication_monitordata] which resides within the distribution database. If you have replication setup then you will have a distribution database, but if you don’t then the distribution database will not be available. Also note this example is based on a single publication so if you have multiple publications then you will see more than one entry per subscriber per publication.

Step 1: Simple Query

I just want the retention period…

SELECT retention 
FROM [Distribution]..[MSreplication_monitordata] 

Now we know what the retention period is set to and that is 14 days. Now I need to multiply the retention value by .8 and the result will be 11.2. So 80% of 14 = 11.2.

Step 2: Simple Calculation

SELECT retention
      ,(retention * .8) 
FROM [Distribution]..[MSreplication_monitordata]

Side Note:

Keep in mind if you want to get the threshold value of the metric being monitored from the database JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id and pull the t.[value] which will return the value of 80 which is the default. If it was changed then of course the value will differ. Just note that this is a sql_variant data type so you will need to CAST or CONVERT it to perform the calculation.

SELECT	m.retention 
		,t.[value]
		-- These steps are just for illustration
		,(CAST(t.[value] AS DECIMAL(10,2))/100) '1'
		,(m.[retention] * (CAST(t.[value] AS DECIMAL(10,2))/100)) '2'
		,CAST((m.[retention] * (CAST(t.[value] AS DECIMAL(10,2))/100)) AS INT) '3'
		-- The following is faster and easier
		,(m.retention * CAST(t.[value] AS INT)/100) '4'
FROM [Distribution]..[MSreplication_monitordata] m
JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id

What this means in terms of Replication Monitor is that any subscriber that has not synchronized for 11 days with the retention set to 14 days will have a subscription status of “Expiring Soon/Expired“. If the retention was set to 30 days which is also common and the threshold set to the default of 80% then the subscribers would show the same the status when they reach 24 days without synchronizing.

Step 3: Round the Results

11.2 is a good start, but I need a whole number so I will wrap line 2 from (Step 2) inside a ROUND function and supply 0 for the length or precision as it needs one argument.

SELECT retention
      ,ROUND((retention * .8) ,0)
FROM [Distribution]..[MSreplication_monitordata]

Step 4: Remove the Decimal

The above statement returns a value of 11.0 which is rounded, but now I need to get rid of the decimal place so that 11 is the only thing returned. Again I will take line 2 this time from (Step 3) and wrap it in a CAST function and CAST it as INT.

SELECT retention
      ,CAST(ROUND((retention * .8) ,0) AS INT)
FROM [Distribution]..[MSreplication_monitordata]

Or you could have saved yourself a few steps and done this…

 
SELECT retention 
      ,CAST((retention * .8) AS INT) 
FROM [Distribution]..[MSreplication_monitordata] 

Final Results

When I apply the rounded value to my query as part of the WHERE clause only the records that fall within the expiration threshold (retention * .8) will be returned. Meaning subscriptions that are close to expiration and subscriptions that are expired.

The results are exactly what I wanted. Now I just need to take it a bit further… think email delivery!

I have wrapped this into a stored procedure and added some logic that will check for expiring records and if there are records found it will then tie the results into a nice html formatted message delivered by database mail. Then I setup a sql job which runs the stored procedure daily including weekends and fires off an email only when records are found. Technically I could fire off a report subscription which can be formatted very nicely and just fire off the job using exec sp_start_job @JobName = ‘Report Subscription ID goes here’ when ever records are detected.

For more information about using ROUND please visit: http://msdn.microsoft.com/en-us/library/ms175003.aspx

Download the code

SELECT.GetExpiringSubscriptions_v0.01.sql

/*****************************************************************
**	Author	:	Samson J. Loo (justsamson.com | @just_samson)
**	Created : 9/26/2010
**	Intent	: Notification of subscriptions that are nearing the
**						the expiration threshold
**	Notes	: Requires db mail
**	Version	: 0.01
*****************************************************************/

IF OBJECT_ID('sp_RP_GetExpiringSubscribers') IS NOT NULL
DROP PROCEDURE sp_RP_GetExpiringSubscribers
GO
CREATE PROCEDURE [dbo].[sp_RP_GetExpiringSubscribers]
AS

DECLARE @cnt INT
SET @cnt = 0
SELECT	@cnt = COUNT(a.[subscriber_name])
FROM [Distribution]..[MSmerge_sessions] s
	JOIN [Distribution]..[MSmerge_agents] a ON s.agent_id = a.id
	JOIN [Distribution]..[MSreplication_monitordata] m ON s.agent_id = m.agent_id
	JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id
WHERE s.end_time IN
(
	SELECT TOP 1 s1.end_time 
	FROM [Distribution]..[MSmerge_sessions] s1
	WHERE s.agent_id = s1.agent_id
	ORDER BY s1.end_time DESC
) AND DATEDIFF(d,s.[start_time],getdate()) >= CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT)
AND t.[isenabled] = 1
GROUP BY s.[start_time]

IF @cnt > 0
	BEGIN
		
		DECLARE @tableHTML  NVARCHAR(MAX) ;

		SET @tableHTML =
			N'<H1><font color="#FF0000">Expiring Subscription Report</font></H1>' +
			N'<table border="0" cellspacing="2" cellpadding="2">' +
			N'<tr><th bgcolor="#BDBDBD">Subscriber</th>' +
						N'<th bgcolor="#BDBDBD">Status</th>
							<th bgcolor="#BDBDBD">Delivery Rate</th>
							<th bgcolor="#BDBDBD">Last Sync</th>' +
						N'<th bgcolor="#BDBDBD">Duration</th>
							<th bgcolor="#BDBDBD">Conn Type</th>
							<th bgcolor="#BDBDBD">Result</th>
							<th bgcolor="#BDBDBD">Days Behind</th>					
						  <th bgcolor="#BDBDBD">Subscriber Status</th></tr>' +
			CAST ( ( 

		SELECT
		td = CASE
					WHEN CHARINDEX('\',a.[subscriber_name]) > 0 THEN LEFT(a.[subscriber_name],CHARINDEX('\',a.[subscriber_name])-1)
					ELSE a.[subscriber_name]
				END	
			,''
			,td = CASE
				WHEN s.[runstatus] = 3 THEN 'Synchornizing'
				WHEN s.[runstatus] = 5 THEN 'Retrying failed command'
			ELSE 'Not Synchronizing'
			END 
			,''
			,td = CAST(s.[delivery_rate] AS VARCHAR) + ' rows/sec'
			,''
			,td = s.[start_time]
			,''
			,td = CAST((s.[duration]/86400) AS VARCHAR) 
			+ '.' + CAST(REPLACE(STR(((s.[duration]/3600) - ((s.[duration]/86400) * 24)),2),SPACE(1),0) AS VARCHAR)
			+ ':' + CAST(REPLACE(STR((s.[duration] % 3600/60),2),SPACE(1),0) AS VARCHAR)
			+ ':' + CAST(REPLACE(STR((s.[duration] % 60),2),SPACE(1),0) AS VARCHAR)	
			,''
			,td = CASE 
				WHEN s.[connection_type] = 1 THEN 'LAN'
				WHEN s.[connection_type] = 2 THEN 'Dialup'
				WHEN s.[connection_type] = 3 THEN 'Web Sync'
			END
			,''
			,td = CASE
				WHEN s.[runstatus] = 1 THEN 'Start'
				WHEN s.[runstatus] = 2 THEN 'Succeed'
				WHEN s.[runstatus] = 3 THEN 'In Progress'
				WHEN s.[runstatus] = 4 THEN 'Idle'
				WHEN s.[runstatus] = 5 THEN 'Retry'
				WHEN s.[runstatus] = 6 THEN 'Error'
			END
			,''
			,td = DATEDIFF(d,s.[start_time],getdate())
			,''
			,td = CASE 
				WHEN (DATEDIFF(d,s.[start_time],getdate()) < CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT)) THEN 'Good'
				WHEN (DATEDIFF(d,s.[start_time],getdate()) <= m.[retention]) THEN 'Expiring Soon'
				WHEN (DATEDIFF(d,s.[start_time],getdate()) > m.[retention]) THEN 'Expired'
			END
			--,m.[retention]
		FROM [Distribution]..[MSmerge_sessions] s
			JOIN [Distribution]..[MSmerge_agents] a ON s.agent_id = a.id
			JOIN [Distribution]..[MSreplication_monitordata] m ON a.id = m.agent_id
			JOIN [Distribution]..[MSpublicationthresholds] t ON m.publication_id = t.publication_id
		WHERE s.end_time IN
		(
			SELECT TOP 1 s1.end_time 
			FROM [Distribution]..[MSmerge_sessions] s1
			WHERE s.agent_id = s1.agent_id
			ORDER BY s1.end_time DESC
		) AND DATEDIFF(d,s.[start_time],getdate()) >= CAST((CAST(t.[value] AS DECIMAL)/100)* m.[retention] AS INT)
		AND t.[isenabled] = 1
		ORDER BY s.[start_time]    
		    
			FOR XML PATH('tr'), TYPE 
			) AS NVARCHAR(MAX) ) +
			N'</table>' ;

		EXEC msdb.dbo.sp_send_dbmail 
			@profile_name = 'WorkingNotifier',
			@recipients='you@yourdomain.com',
			@copy_recipients='someone@somewhere.com',
			@subject = 'Expiring Subscription Report',
			@body = @tableHTML,
			@body_format = 'HTML' ;
		
		
	END
ELSE
	BEGIN
		PRINT 'No Records Found!'
	END
Advertisements

Your mailbox is over the limit!

Where I work we have a very small storage alottment per our exchange profiles. We are limited to roughly 256 mb (give or take), which you can imagine can and will fill up very quickly. Especially with such high volume of email exchanges nowadays. In order to circumvent the “nasty graham” reminders I setup two client-only outlook rules after a suggestion from a colleague of mine.

I created two folders nested in a backup folder. One called Inbox and the other Sent. Keep in mind that I only check work email on one laptop but I do have a blackberry which the Sent Route Filter does not apply to. So any reponses from my blackberry will be found in the Sent Items folder. So keep that in mind. If you check mail from multiple pcs using the Outlook client (not including the web client) you will have issues with your emails scattered between systems so also be aware of that.

Step 1: Disable saving to Sent Items

Open up Outlook then go to Tools –> Options

In the Options dialog click the E-mail Options… button

Uncheck Save copies of messages in Sent Items folder then click OK

Click OK to close the Options dialog

Step 2: Setup the Rules

Go to Tools –> Rules and Alerts…

Click on the New Rule… button

Select Check messages after sending then click Next

Select on this machine only then click Next

Select move a copy to the specified folder, then click on the specified link in step 2

Select your new destination folder, then click OK

Click Next on the Rules Wizard dialog

Click Next again on the Rules Wizard dialog

Finally specify a name for the rule and click Finish

Side Note

If you receive this message simply click OK to proceed

Click Ok to close the Rules and Alerts window

Step 3: Test the Rule

Send a message from within Outlook and it should end up in your new destination folder

Success!

Final words

Now I have walked you through the process of creating a rule that moves all your sent mail to a custom destination folder, its your turn to do it for the inbox. Now create a rule for your inbox following similar procedures from (Step 2) with a slight variation of selecting a different inbox folder and specify a different rule name.

Good luck!

Date Parameters and Things to Consider

I touched on Reporting Services Date Parameters, but what I did not cover is the underlying importance of understanding how TSQL works in terms of date parameters. You need to know that there is an implicit midnight time-stamp of 00:00:00 when a date is only supplied. So when you are dealing with date parameters in terms of FROM and TO dates, the TO will need or should include all records for that date as well. However, since a date is only supplied your chances of including all records for that end date are very slim. I was able to correct this by adding a modifier in stored procedures that takes the input date and increments it by one day.

For illustration purposes here are some TSQL scripts that will allow you to test a few queries to see what is actually begin returned when you only pass a date to a DATETIME field. This will help you understand what the results of your report will be as well.

Step 1: Create Table

CREATE TABLE #TestDate
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[StandardDT] [datetime] NOT NULL,
	[MyKey] [int] NOT NULL,
	[Notes] [nvarchar](100) NULL,
)

Step 2: Populate Table

INSERT INTO #TestDate (MyKey, StandardDT, Notes) 
SELECT 1, '1/1/2010', 'Note 1'
UNION ALL
SELECT 2, '2/1/2010', 'Note 2'
UNION ALL
SELECT 3, '3/1/2010', 'Note 3'
UNION ALL
SELECT 4, '4/1/2010 10:42:22', 'Note 4'
UNION ALL
SELECT 5, '4/1/2010 22:42:22', 'Note 5'
UNION ALL
SELECT 6, '6/1/2010', 'Note 6'
UNION ALL
SELECT 7, '7/1/2010', 'Note 7'
UNION ALL
SELECT 8, '7/1/2010 01:10:19', 'Note 8'
UNION ALL
SELECT 9, '7/1/2010 02:11:20', 'Note 9'
UNION ALL
SELECT 10, '7/1/2010 21:21:22', 'Note 10'

Step 3: Verify Data

SELECT * FROM #TestDate

As you can see we have 10 records total with some records that have a time-stamp associated that are beyond midnight specifically April and July while the the other months are midnight.

Step 4: Test Queries

Let’s run through some test queries to help better illustrate my point. Let’s query the data using a FROM date of 3/1/2010 and a TO date of 4/1/2010. Looking at the data there is one record for 3/1/2010 and two records from 4/1/2010. How many records do you think will be returned? Well let’s just see for ourselves.

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '3/1/2010'
SET @EndDate = '4/1/2010'

SELECT * FROM #TestDate 
WHERE StandardDT BETWEEN @StartDate AND @EndDate

SELECT @StartDate
SELECT @EndDate

Interesting… there is only one record returned even though 4/1/2010 is within our query parameters. Take a look at lines 8 & 9 from the query above and pay attention to the time-stamps. You can see in the screen cast below that the TO date which is represented by @EndDate parameter is 4/1/2010 00:00:00 which is 4/1/2010 midnight. Well looking through the data proves we do not have any records that meet that specific criteria because there are no records for 4/1/2010 that have a midnight time-stamp associated.

Let’s try a few more test queries just to get a clearer picture. Let’s see how many records are returned when we search FROM 7/1/2010 TO 7/1/2010. We know that our data set contains four records for 7/1/2010, but can you guess how many records will be returned if we want to search FROM 7/1/2010 TO 7/1/2010?

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '7/1/2010'
SET @EndDate = '7/1/2010'

SELECT * FROM #TestDate 
WHERE StandardDT BETWEEN @StartDate AND @EndDate

SELECT @StartDate
SELECT @EndDate

There was only one record returned because there was only one record that met the criteria 100%. If you look at the values for @StartDate and @EndDate they both are returning 7/1/2010 00:00:00.

Now let’s run through one last query, because practice makes perfect. If I were to search our data set for all records from 4/1/2010 and supply a FROM date of 4/1/2010 and a TO date of 4/1/2010 how many records would be returned? The answer is zero, because both you and I know there are no records within the data set that have a time-stamp of 00:00:00 for 4/1/2010.

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '4/1/2010'
SET @EndDate = '4/1/2010'

SELECT * FROM #TestDate 
WHERE StandardDT BETWEEN @StartDate AND @EndDate

SELECT @StartDate
SELECT @EndDate

Perfect. No records returned just as we thought.

Step 5: Solution

The easiest way to rectify this is to ask the end users to use the next day, but that is not a practical answer because you and I both know after the first weekend or vacation the end users will return to their normal mode and begin searching through the data using the date they did before. So in order to satisfy the requirements I decided to add a parameter modifier to my procedure which takes the received input value for the @EndDate and increments the day by one. Meaning if 7/1/2010 were passed as the TO or @EndDate value it would become 7/2/2010 00:00:00 and all records from 7/1/2010 will be returned. Pretty simple.

ALTER PROCEDURE sp_RP_MyReport
(
  @StartDate DATETIME
  ,@EndDate DATETIME
)

AS 

SET @EndDate = DATEADD(d,1,@EndDate)

Line #: 9 in the above syntax is what I implemented which makes the adjustment to the @EndDate parameter.

Step 6: Validate Solution

Let’s add the modifier just below the parameter declarations and above the SELECT statement and see what we get when we execute the script. Can you guess what will be returned?

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '3/1/2010'
SET @EndDate = '4/1/2010'

SET @EndDate = DATEADD(d,1,@EndDate)
SELECT * FROM #TestDate 
WHERE StandardDT BETWEEN @StartDate AND @EndDate

SELECT @StartDate
SELECT @EndDate

Success! Three records returned. By adding the modifier (Line #: 5) the value was accepted, incremented by 1 and supplied back to the SELECT statement and the records which fell into the criteria were returned.

Step 7: Cleanup

Now that we are all squared away, let’s try not forget that we still need to drop the temp table.

DROP TABLE #TestDate

Side Note


You can always use the DATEADD in the BETWEEN statement like below in line 06.

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '3/1/2010'
SET @EndDate = '4/1/2010'

SELECT * FROM #TestDate
WHERE StandardDT BETWEEN @StartDate AND DATEADD(d,1,@EndDate)

Delivering Subscriptions Outside of the Ogranization

Recently I faced an issue with reporting services 2005 when attempting to deliver subscriptions to addresses outside of the organization. Internal addresses received the email based subscription deliveries
without any questions. I must have checked and re-checked the settings using RSConfigTool about million times, looking for anything I might have overlooked. The error message I received was, “The e-mail address of one or more recipients is not valid”. After some research (which lead me to a lot of dead ended forums) I read the phrase “email relay”, that’s when the gears started spinning.

I realized that the issue had nothing to do with the configuration of SQL Server Reporting Services; rather, the SMTP server! In order for the messages to be delivered outside of the organization the Reporting Services Server needed to be authorized so-to-speak. Unfortunately I don’t have access to Exchange 2003 so I cannot provide screen shots, but for 2007 all you need to do is add the server’s IP Address to the SMTP server’s receiver group in the HUB Transport configs.

Then to test your subscription without tweaking the schedule execution time just run the SQL job! To find out the name of the job use the attached sql script. If you have a named instance append $instancename to all three of ReportServer occurrences (i.e. ReportServer$InstanceName) for MSSQL 2005. I believe for MSSQL 2008 you would append _InstanceName (i.e. ReportServer_InstanceName), but I am not certain. You should get the results similar to the screen shot attached.

SELECT	
	sj.[name] AS [Job Name],
	c.[Name] AS [Report Name],
	c.[Path],
	su.Description,
	su.EventType,
	su.LastStatus,
	su.LastRunTime

FROM msdb..sysjobs AS sj 
JOIN ReportServer..ReportSchedule AS rs ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128)) 
JOIN ReportServer..Subscriptions AS su ON rs.SubscriptionID = su.SubscriptionID 
JOIN ReportServer..[Catalog] c ON su.Report_OID = c.ItemID
 
/*
USAGE:

USE [msdb]
EXEC sp_start_job @job_name = 'AF015D8B-D80D-4D2A-9808-CD1D519B3332'

NOTE:
If using a named instance use ReportServer$Instance_Name for 2005
For 2008 I believe you only need to change the $ to _ when using 2008 (i.e. ReportServer_Instance_Name)
*/

Repeating Tablix Column Headers with SSRS 2008

There seems to be an issue with repeating column headers using SQL Server 2008 Reporting Services. Typically you would highlight the row, right-click and select properties then you would be able to set the property for RepeatOnNewPage to True. However this is not the case with SSRS 2008. I tried it and it does not work and I cannot speak for SSRS 2008 R2, but I will give it a whirl and post my findings here to confirm if the behavior is the same.

I tried selecting both options in the Row Headers and Column Headers sections without any luck.

I tried selecting all options and a combination of options then tested the report to see if the headers would repeat and much to my dismay they did not.

As you can see the headers did not repeat. So I cursed a little and decided to hit the web to see if I could under cover the reason why and at the very least find a solution to resolve this.

So I scoured the web in search for answers and came across a blog post by Nick Olson titled: Repeating Tablix Header in SSRS 2008. I followed his instructions, but couldn’t determine where the group pane arrow was located because I was not as familiar with the BIDS layout as I would have liked to be. Nonetheless after some intense yet careful screen staring to the point of nearly going cross-eyed I finally found the little bugger. I was able to make the header repeat and this is how I did it.

Step 1: Click Tiny Black Arrow

The very tiny and almost unnoticeable arrow that sits atop the Group Pane, close to the properties windows on the right hand side of the report designer. You would have thought this would have been more apparent and noticeable.

Step 2: Enable Advanced Mode

There is only one item and that is Advanced Mode

Step 3: Select Static Field

I selected the Static field that was nested above the Details field in the Row Group section of the Group Pane.

Step 4: Set Tablix Member Properties

In this step I had to change two settings, but for whatever reason Nick was able to get by with only making one setting change. I had to change the KeepWithGroup and RepeatOnNewPage in order for the headers to repeat. When I just changed the RepeatOnNewPage the headers did not repeat.

There are three options for KeepWithGroup: (None, Before and After). Of course None did not work nor did before, then I tried After and wahlah! it worked. RepearOnNewPage only has two available attributes and those are: (False and True).

Step 5: Test Report

Shortly after testing all of the KeepWithGroup attributes and finally selecting After I was able to breathe a sigh of relief.

TADA! Repeating Headers

Side Note

When I selected one of the Static fields from within the Column Groups section and made the same Tablix Member Property changes I received the following error. I just wanted to make sure I made this known.