List All Analysis Services Databases

Well today was my first attempt outside of a classroom setting to dig my heels into SSAS DMX. I have an idea that I want to put into action (automating a process) and I believe it will benefit my organization. So fire up SSMS and connect to instance that has Analysis Services.

1) Start a New DMX Query

2) Execute Script


3) Returning Specific Columns

Similar to Transact-SQL you just specify the columns you want; however, you need to wrap up the column names with brackets [column].


Pretty simple. Now let’s try with XMLA, which is XML for Analysis Services.

Just like before connect to an instance which has Analysis Services and published cubes.

1. Start a New XMLA Query

2. Execute the Script

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<Restrictions />
<Properties />


Get Last Backup Date

There are many ways to go about getting the last backup date for any and all database but I tend to lean towards set based methods over using the GUI for many reasons. One in particular is for the fact that I can turn set based into an actionable report since monitoring backups is important after all.

The method I use the most is the following because it only returns information about databases that currently exists. I really do not need to see information about what used to exist, but I will also show that example as well. Before I go on you might want to understand the anatomy of both the master.sys.databases catalog view and msdb.dbo.backupset table.

SELECT, MAX(b.backup_finish_date) 'Last Backup Date'
FROM master.sys.databases d
	JOIN msdb.dbo.backupset b ON = b.database_name
	WHERE (b.type = 'D' OR b.type = 'I')

The results indicate (104 row(s) affected). So I have 104 databases on this particular instance.

This is the example of returning the history for the last backup date which may contain information about past databases which depends on your maintenance for backup history. So this may vary.

SELECT database_name, MAX(backup_finish_Date) 'Last Backup Date'
FROM msdb.dbo.backupset
WHERE (type = 'D' OR type = 'I')
GROUP BY database_name
ORDER BY database_name

The results indicate (108 row(s) affected) so between the two statements you can see there is a difference of four rows which is why I avoid using the msdb.dbo.backupset as the only source of record.

The easiest way to determine which databases no longer exists is to execute the following.

SELECT DISTINCT database_name 
FROM msdb.dbo.backupset
		database_name NOT IN
				SELECT name FROM master.sys.databases

The results indicate that (4 row(s) affected) and the names of the database that are not current.

Query LinkedServer and Provider Information

First off I modified the (master.dbo.sp_MSset_oledb_prop) stored procedure just a bit and incorporated my own query to get the results I needed. Which you can find from lines 78 to 104 of the query at the bottom of this post. I cannot take all the credit, I received a lot of help from the sql community. Thanks again all, I appreciate it. Also note I am using SQL Server 2008 R2 Developer Edition.

You’ll notice I changed most of the temp tables to table variables with the exception of the #paramlist. I suspect the table variable loses it declaration when using it within a cursor (not 100% positive on that). The oddity was that the results were still returned but I received the “Msg 1087, Level 15, State 2, Line 1 Must declare the table variable @paramlist” error repeatedly. So I changed it back to a temp table and now all is well.

The Thread

The point of this was to return linked server information as well as the provider properties, specifically Allow Inprocess. I blurred out some details to protect the innocent.

The Results

The Query

DECLARE @provider_name AS SYSNAME, @property_name AS SYSNAME, @property_value AS BIT

DECLARE @providers TABLE
	,guid NVARCHAR(100) NULL
	,description NVARCHAR(100) NULL

INSERT INTO @providers EXEC sys.sp_enum_oledb_providers  

CREATE TABLE #paramlist
	,property_value INT

INSERT INTO #paramlist (property_name) VALUES ('AllowInProcess')  
--INSERT INTO #paramlist (property_name)  VALUES ('DisallowAdHocAccess')  
--INSERT INTO #paramlist (property_name)  VALUES ('DynamicParameters')  
--INSERT INTO #paramlist (property_name)  VALUES ('IndexAsAccessPath')  
--INSERT INTO #paramlist (property_name)  VALUES ('LevelZeroOnly')  
--INSERT INTO #paramlist (property_name)  VALUES ('NestedQueries')  
--INSERT INTO #paramlist (property_name)  VALUES ('NonTransactedUpdates')  
--INSERT INTO #paramlist (property_name)  VALUES ('SqlServerLIKE')  

DECLARE @oledbprop TABLE
	,allow_in_process BIT
	,disallow_adhoc_access BIT
	,dynamic_parameters BIT
	,index_as_access_path BIT
	,level_zero_only BIT
	,nested_queries BIT
	,non_transacted_updates BIT
	,sql_server_like BIT

DECLARE @regpath NVARCHAR(512)  
SET @regpath = N'SOFTWARE\Microsoft\MSSQLServer\Providers\' + @provider_name  
DECLARE @value int, @sql NVARCHAR(300), @param NVARCHAR(300)  
	SET @sql = 'EXEC sys.xp_instance_regread N''HKEY_LOCAL_MACHINE'', @regpath, @property_name, @value OUTPUT, @no_output = N''no_output'' ' +  
	'update #paramlist SET property_value = IsNull(@value, 0) where property_name = @property_name'  
	SET @param = '@regpath NVARCHAR(512), @property_name SYSNAME, @value int'  

FOR ( SELECT name FROM @providers )  
OPEN c_prov  
FETCH NEXT FROM c_prov into @provider_name  
WHILE @@fetch_status = 0  
		SET @regpath = N'SOFTWARE\Microsoft\MSSQLServer\Providers\' + @provider_name  

		FOR ( SELECT property_name FROM #paramlist )  
		OPEN c  
		FETCH NEXT FROM c into @property_name  
		WHILE @@fetch_status = 0  
				EXEC sp_executesql @sql, @param, @regpath, @property_name, @value  
				FETCH NEXT FROM c into @property_name  
		CLOSE c  

		INSERT @oledbprop (provider_name, allow_in_process , disallow_adhoc_access , dynamic_parameters , index_as_access_path , level_zero_only , nested_queries , non_transacted_updates , sql_server_like)  
		SELECT @provider_name, AllowInProcess, DisallowAdHocAccess, DynamicParameters, IndexAsAccessPath, LevelZeroOnly, NestedQueries, NonTransactedUpdates, SqlServerLIKE   
		FROM #paramlist pivot ( max(property_value) FOR property_name in ( [AllowInProcess], [DisallowAdHocAccess], [DynamicParameters], [IndexAsAccessPath], [LevelZeroOnly], [NestedQueries], [NonTransactedUpdates], [SqlServerLIKE] ) ) as p  

		FETCH NEXT FROM c_prov INTO @provider_name  
CLOSE c_prov  

	 SELECT 'linked server'
		/* ,s.data_source */
			WHEN s.catalog IS NULL THEN QUOTENAME(s.data_source)
			ELSE QUOTENAME(s.data_source) + '.' + QUOTENAME(s.catalog)
		END 'data source'
			WHEN o.allow_in_process = 0 THEN 'No'
			ELSE 'Yes'
		END 'allow inprocess'
			WHEN s.is_rpc_out_enabled = 0 THEN 'No'
			ELSE 'Yes'
		END 'rpc out'
	FROM master.sys.servers s
		JOIN master.sys.linked_logins l ON s.server_id = l.server_id
		JOIN @oledbprop o ON s.provider = o.provider_name
		JOIN @providers p ON o.provider_name =

DROP TABLE #paramlist 

Update to SQL Server 2008 R2 CU6

I got my work laptop environment finally squared away and today was the first attempt to update my instances of SQL Server 2008 R2 Developer and Express to CU3. Yes I know the post title say CU6, trust me I’ll get to that in a moment. Originally I was going to update to CU3 reason being because it addressed an issue I was experiencing with creating, adding or editing SQL Agent jobs in SSMS. Matter of fact here is the Microsoft article address:

Here is the specific error I received when I attempted to edit an agent job.

Related Connect feedback

So I downloaded CU3 from Microsoft.

When I tried to apply CU3 I encountered an error which basically suggested that I had to uninstall SQL Native Client because it was not installed via sqlcnli.msi. Here is the url that the error pointed me to.

Source: setup.rll ID: 50000 (SQL Server 10.50.1600.1) – Events And Errors Message Center: Message Details

Needless to say I decided to reboot hoping that would some how, some way correct the issue auto-magically. WRONG! So I decided (executive decision) to push forward and apply CU6. See I told you I would eventually get to it! Well I downloaded CU6 and attempted to install and ran into another error.

Here is the full Summary.txt

Well again I tried a reboot and that did not work, so I surfed around and then stumbled on a post by Jonathan Kehayias (@SQLSarg | blog) titled: SQL Server 2008 failed installation blocks Service Pack 1 (SP1) installation. He essentially performed a repair which resolved his issue so I felt inclined to do the same and BOOM… CU6 applied oh so nicely. I cannot say if this will help you, but it helped me. Both my Dev and Express editions are at CU6 so I am a happy camper.

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

	[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'
SELECT 2, '2/1/2010', 'Note 2'
SELECT 3, '3/1/2010', 'Note 3'
SELECT 4, '4/1/2010 10:42:22', 'Note 4'
SELECT 5, '4/1/2010 22:42:22', 'Note 5'
SELECT 6, '6/1/2010', 'Note 6'
SELECT 7, '7/1/2010', 'Note 7'
SELECT 8, '7/1/2010 01:10:19', 'Note 8'
SELECT 9, '7/1/2010 02:11:20', 'Note 9'
SELECT 10, '7/1/2010 21:21:22', 'Note 10'

Step 3: Verify Data


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.

SET @StartDate = '3/1/2010'
SET @EndDate = '4/1/2010'

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

SELECT @StartDate

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?

SET @StartDate = '7/1/2010'
SET @EndDate = '7/1/2010'

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

SELECT @StartDate

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.

SET @StartDate = '4/1/2010'
SET @EndDate = '4/1/2010'

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

SELECT @StartDate

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.

  @StartDate DATETIME
  ,@EndDate DATETIME


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?

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

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.


Side Note

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

SET @StartDate = '3/1/2010'
SET @EndDate = '4/1/2010'

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.

	sj.[name] AS [Job Name],
	c.[Name] AS [Report Name],

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

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

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.