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
SELECT * FROM $system.DBSCHEMA_CATALOGS
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].
SELECT [CATALOG_NAME] FROM $system.DBSCHEMA_CATALOGS
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"> <RequestType>DBSCHEMA_CATALOGS</RequestType> <Restrictions /> <Properties /> </Discover>
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.
/* GET LAST BACKUP DATE FOR ALL EXISTING DATABASES */ SELECT d.name, MAX(b.backup_finish_date) 'Last Backup Date' FROM master.sys.databases d JOIN msdb.dbo.backupset b ON d.name = b.database_name WHERE (b.type = 'D' OR b.type = 'I') GROUP BY d.name ORDER BY d.name
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.
/* GET LAST BACKUP DATE FROM BACKUPSET */ 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.
/* RETURN NON EXISTING DATABASE NAMES */ SELECT DISTINCT database_name FROM msdb.dbo.backupset WHERE ( 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.
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: http://support.microsoft.com/kb/2315727
Here is the specific error I received when I attempted to edit an agent job.
Related Connect feedback
So I downloaded CU3 from Microsoft.
- Cumulative Update package 1 for SQL Server 2008 R2
- Cumulative Update package 2 for SQL Server 2008 R2
- Cumulative Update package 3 for SQL Server 2008 R2
- Cumulative Update package 4 for SQL Server 2008 R2
- Cumulative Update package 5 for SQL Server 2008 R2
- Cumulative Update package 6 for SQL Server 2008 R2
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.
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.
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
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
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
USE [msdb] EXEC sp_send_dbmail @profile_name = 'DB Admin', -- Can be whatever you specify @recipients = 'firstname.lastname@example.org', /* @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)