ayesamson

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

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)

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

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.

SQL Server Database Mail plays a nice role in my administration. I have set up a few SQL Server Agent Jobs that calls upon stored procedures that I put together to help monitor issues that arise with the data. Sometimes its specific for tracking GIS Schema changes or incoming dirty data from interfacing systems. I like to be in the know right away as it helps me address problems quickly.

First thing to understand is the fact that in order to use DB Mail you need the SQL Server Agent running. Secondly in order for you to execute the system stored procedure sp_send_dbmail you need to be a member of the DatabaseMailUserRole which resides in MSDB.

Step 1: Show Advanced Options

use [master]
go
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go

Side note: some options require a restart of the database engine however some can be circumvented by supplying the argument “with override” to the reconfigure command. For more information please visit the following MSDN posts: Setting Server Configuration Options and RECONFIGURE (Transact-SQL).

Create the Mailer Profile

EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DB Admin', -- Can be whatever you specify
@description = 'Profile used for database mail'

Create the Mailing Account

EXEC msdb.dbo.sysmail_add_account_sp
@account_name = 'Notifier', -- Can be whatever you specify
@description = 'Notification Account of Database Changes',
@email_address = 'No-Reply@sqlsam7.com', -- can be fictitious
@display_name = 'No-Reply',
@mailserver_name = 'smtp.gmail.com',
@port = 587,
@username = 'YourAccount@gmail.com',
@password = 'your password',
--@use_default_credentials =, 
@enable_ssl = 1

Associate the Mailer Profile to the Mailing Account

EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DB Admin', -- Must be the same as above
@account_name = 'Notifier', -- Must be the same as above
@sequence_number = 1

Test

USE [msdb]
EXEC sp_send_dbmail
@profile_name = 'DB Admin', -- Can be whatever you specify
@recipients = 'samson@sqlsam7.com',
/* @copy_recipients = '[Email address protected]
@blind_copy_recipients = '[Email address protected] */
@subject = 'Test Email from SQL Server dbMail',
@importance = 'High',
@body = 'This is a test of the SQL Server dbMail.',
@body_format = 'Text' -- Can be HTML too.

Verify Mail Log and Event Log

SELECT * FROM msdb.dbo.sysmail_allitems
ORDER BY mailitem_id DESC

SELECT * FROM msdb.dbo.sysmail_event_log 
ORDER BY log_id DESC

I merely ready about policy based management (PBM) through short snippets here and there, but never really got that in-depth with it as I normally do with other subjects. I think the major factor was due to the name Policy. Mistakenly I automatically associated it with Active Directory. PBM was introduced with SQL Server 2008 and offers great benefits with administering SQL Server. I was conducting some research on the subject and found a post by Ashish Kumar Mehta titled: “Identify Databases Not in Full Recovery Model Using Policy Based Management” which steps you through the process of creating a simple policy and runs you through the evaluation process. Ashish covers the steps rather well so be sure to visit the post for more details. If you want to get deeper into the terminology, scheduling and such then visit a post by Ray Barley titled: “Using Policy-Based Management in SQL Server 2008“

After walking through Ashish’s post I wanted to make one similar but I wanted to return all Windows Accounts and such, though his use for the Recovery Model is more practical. So fire up your SSMS 2008 and let’s get started. This is just a quick run down of how to create one. This does not get into the deep details that others like have. More of a pre-primer primer if you will.

Open SQL Server Management Studio and double-click or expand Management.

Right click on Policies

Select New Policy…

Give your policy a name

Click the Check Condition and Select New Condition…

Give your Condition a name… I know it requires a lot of naming

Select the Facet drop down and Select Login

In the Expression section click on the Field column and select @LoginType

Select the Operator (=)

Select WindowsUser for the Value and hit Enter

Click Ok to create the Condition and the Policy

Policies should be expanded now and you should see your newly created policy. Expand Condition and you should see the condition you just finalized.

Now let’s test the Policy. Right click on the newly created Policy and select Evaluate.

And this brings us to the end results

The green marks are the Windows Accounts and the Red marked rows are not.

In the event you are working on a server that may or may not have policies in place Ken Simmons provided me with the following select statement that will return information about all the existing policies.

SELECT * FROM msdb.dbo.syspolicy_policies

In the event you incorrectly spelled a policy here is a method that will allow you to rename it. Again thanks to Ken Simmons with the #sqlhelp tweet.

SELECT * FROM msdb.dbo.sp_syspolicy_rename_policy @name = 'OldName', @new_name = 'NewName

Ken Simmons (website | @KenSimmons) is also a published author on this very subject so check out his book titled: (Pro SQL Server 2008 Policy-Based Management) with was co-authored by Jorge Segarra (website | @SQLChicken) and Colin Stasiuk (website | @benchmarkIT)

I have used the UNION operator a few times in the past and during a recent Business Intelligence project I quite possibly used it more then I had in the past combined. Typically you can merge queries together simply by placing a UNION operator between two queries. Well it’s not that simple there are some restrictions.

• the number of fields must be the same in both queries
• the order of the fields must be the same in both queries
• data types must be compatible

What you don’t believe me? Well then visit the TechNet site: UNION Transact-SQL and see it for yourself.

syntax

The syntax is really nothing…

SELECT Column1, Column2 FROM table1
UNION
SELECT Column1, Column2 FROM table1

That’s the gist of the it. Kind of boring right? Yeah a little bit. When we incorporate it within a stored procedure that is used for Reporting Services it becomes a tad more interesting. I can’t speak for you but at least it is for me. Now let’s get into some scenarios where it comes handy.

Let’s say I want to offer a report that uses a dropdown list for selecting and passing the parameters. Well typically in a select list you can only choose one, but what if you wanted an option to select all? This is where the UNION comes in handy.

procedure

Typically you would construct your procedure similarly to the one below.

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_GetProspectBuyerData'))
DROP PROCEDURE sp_RP_GetProspectBuyerData
GO

CREATE PROCEDURE sp_RP_GetProspectBuyerData
(
	@id INT
)
AS
SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id)

The basics of the procedure is a simple SELECT statement. So let’s take a look at the results when I just run (lines: 12-18) only.

SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

The only problem with this procedure is with (line: 19). The choice is either one value or another. There is not option for a “show all value” to be passed.

WHERE (ProspectiveBuyerKey = @id)

This is where the UNION operators comes in. Not necessarily in the stored procedure per se but in the dataset you create to populate the select list. However you still need to modify the stored procedure to prepare for whats to come. This is a slight modification to the WHERE clause of the stored procedure.

WHERE (ProspectiveBuyerKey = @id OR @id = -1)

Here is the final base for the “sp_RP_GetProspectBuyerData” stored procedure with the added OR operator.

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_GetProspectBuyerData'))
DROP PROCEDURE sp_RP_GetProspectBuyerData
GO

CREATE PROCEDURE sp_RP_GetProspectBuyerData
(
	@id INT
)
AS
SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id OR @id = -1)

dataset

Now we need to construct the dataset stored procedure that we will use to populate the select list which will be used in our report. This is the basics of the stored procedure that we will be using to drive the select list on the report.

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_ProspectBuyerDDL'))
DROP PROCEDURE sp_RP_ProspectBuyerDDL
GO

CREATE PROCEDURE sp_RP_ProspectBuyerDDL
AS
SELECT	[ProspectiveBuyerKey] 'ID'
		,[FirstName] + SPACE(1) + 
		[LastName] 'Full Name'
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

Well the above procedure is cool and all but it does not meet our needs as we need to have an “ALL” option listed as a selectable item in the select list. In order to achieve this let’s add another SELECT statement that will correspond to the changes we made to the first procedure “sp_RP_GetProspectBuyerData” where we added the following: ( OR @id = -1 ) and use UNION operator to merge them.

To list the “ALL” in our select list we need to add it using the same data type, order and we must have the same amount of fields. So here is what we need.

SELECT	-1 AS 'ID'
		,'<ALL>' AS 'Full Name'

So now we need to merge the two SELECT statements and this is how its done.

Step 1: First we take…

SELECT	-1 AS 'ID'
		,'<ALL>' AS 'Full Name'

Step 2: Then we add

UNION

Step 3: And finally we append…

SELECT	[ProspectiveBuyerKey] 'ID'
		,[FirstName] + SPACE(1) + 
		[LastName] 'Full Name'
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

Step 4: We wrap them into a stored procedure

USE [AdventureWorksDW2008]
GO
IF EXISTS (SELECT name FROM sys.objects WHERE (name = 'sp_RP_ProspectBuyerDDL'))
DROP PROCEDURE sp_RP_ProspectBuyerDDL
GO

CREATE PROCEDURE sp_RP_ProspectBuyerDDL
AS
SELECT	-1 AS 'ID'
		,'<ALL>' AS 'Full Name'
UNION
SELECT	[ProspectiveBuyerKey] 'ID'
		,[FirstName] + SPACE(1) + 
		[LastName] 'Full Name'
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]

Now if we run (lines: 9-15) only here is what the results look like.

As you can see the “ALL” is on top which is what we need and for the sake of testing. Let’s run through two quick scenario of passing a value of 1 and a value of -1 to the @id parameter just to see what the end results look like.

Here I set the @id variable to 1…

USE [AdventureWorksDW2008]

DECLARE @id INT = 1

SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id OR @id = -1)

Results

Now let’s see what happens when we pass a value of -1…

USE [AdventureWorksDW2008]

DECLARE @id INT = -1

SELECT	ProspectiveBuyerKey
		,FirstName
		,MiddleName
		,LastName
		,CONVERT(varchar,BirthDate,101) 'DOB'
		,EmailAddress
FROM [AdventureWorksDW2008].[dbo].[ProspectiveBuyer]
WHERE (ProspectiveBuyerKey = @id OR @id = -1)

Results

Boom! All records are returned. Exactly what I needed. Now let’s run through the report! I already set the available values for the parameter properties so we can just jump right into the report and see it in action.

Business Intelligence Development Studio

Step 1: Select All

Step 2: Run Report

Results when value other than All is selected

Being the guy that I am I often try to implement pure TSQL solutions especially when working with parameters via Reporting Services. I know it seems like they go hand-in-hand and to a great extent they do, but there are some cases where it is best to use expressions for the report parameters over a stored procedure. Don’t get me wrong my TSQL approach worked fine but when I used the expression I was able to accomplish the exact same results with slightly less syntax to write and possibly less overhead. I stumbled upon the blog post “Calculating the first and last day of the month” by datageek on blogspot which illustrates the expressions I used for my report.

TSQL Syntax

SELECT DATEADD(Month,DATEDIFF(Month,0,getdate()),-1)+1 'StartDate'
SELECT DATEADD(Month,DATEDIFF(Month,0,getdate())+1,-1) 'EndDate'

RS Expression Syntax

Note: The following can be used for Access as well to get the first and last days of the month from what I understand.

First Day of the Month

=DateSerial(Year(Now), Month(Now), 1)

Last Day of the Month

=DateSerial(Year(Now), Month(Now) + 1, 0)

So for illustration purposes I put together a report based on the AdventureWorks database and used the RS Expression Syntax to populate the date fields, but first let’s create the stored procedure.

Stored Procedure

CREATE PROCEDURE sp_RP_GetEmployeeData
(
	@StartDate datetime
	,@EndDate datetime
)

AS

SELECT	e.EmployeeKey
	--, e.ParentEmployeeKey
	--, e.EmployeeNationalIDAlternateKey
	--, e.ParentEmployeeNationalIDAlternateKey
	--, e.SalesTerritoryKey
	, e.FirstName
	, e.LastName
	--, e.MiddleName
	--, e.NameStyle
	, e.Title
	, e.HireDate
	--, e.BirthDate
	--, e.LoginID
	--, e.EmailAddress
	, e.Phone
	--, e.MaritalStatus
	--, e.EmergencyContactName
	--, e.EmergencyContactPhone
	, e.SalariedFlag
	--, e.Gender
	--, e.PayFrequency
	, e.BaseRate
	--, e.VacationHours
	--, e.SickLeaveHours
	--, e.CurrentFlag
	--, e.SalesPersonFlag
	--, e.DepartmentName
	--, e.StartDate
	, e.EndDate
	--, e.Status
	--, st.SalesTerritoryCountry
	--, st.SalesTerritoryRegion
	--, st.SalesTerritoryGroup

FROM AdventureWorksDW2008.dbo.DimEmployee AS e
JOIN AdventureWorksDW2008.dbo.DimSalesTerritory AS st
ON e.SalesTerritoryKey = st.SalesTerritoryKey
WHERE (HireDate BETWEEN @StartDate AND @EndDate)
ORDER BY e.HireDate

Now that we have the sProc in place building the report is pretty much the same for the most part; however, the report parameters have relocated. They are now found on the left hand side under the report data section. In 2005 you would have found the parameters under the Report menu.

Now to set default values for the parameters

1) Right click on the StartDate parameter and select “Parameter Properties”

2) At the Properties screen click on “Default Values”

3) Then select “Specify Values”

4) Click on the “Expression” button

5) Use the RS Expression Syntax for the “First Day of the Month”

6) Now follow the steps 1-5 and set the default value for the EndDate parameter but this time use the RS Syntax for the “Last Day of the Month”

7) Let’s preview the report, there won’t be any data though but the parameters are defaulting as intended

To see the data simply change the date range to reflect 1/1/1996 to 7/1/1999 then press “View Report”

1) If you just want to see the date only and not the time just right click on the field and select “Text Box Properties”

2) In the Text Box Properties dialog select Number

3) From within the Category section select “Date”

4) Then select the format under “Type”

And here is the outcome…