Troubleshooting Dynamic SSRS Queries

Standard

Reading Time: 3 minutes

The Problem

In my daily work I have to work with a lot of SSRS (SQL Server Reporting Services) reports that have either stored procedures, queries or dynamic queries to get all the results.
Troubleshooting dynamic SSRS queries can sometimes be difficult. That’s is especially the case when you’re dealing with multiple hundreds of lines of code all generated dynamically.

An example of such an error is:

Error Dynamic Query

Because the query is generated during execution using all kinds of parameters, you don’t know how the query looks like exactly. Debugging this query would be pretty difficult and tedious, but there is an easy way to get the compiled query.

How does a dynamic query look like

If you never used dynamic queries in SSRS you might not know how that looks like and when it’s used.

A dynamic query in SSRS is actually an expression in a data set. The expression is build upon execution and can include TSQL code, SSRS fields etc.
The reason I use this in my reports is because I sometimes need to implement complicated filtering in my reports using the report parameters.

You can filter the data using the “Filters” tab in the data set but that would cause me to generate to extensive amount of data during execution which I’d like to avoid.

When you open a data set with an dynamic query it looks like this

Dataset Window Dynamic Query

You don’t see much of the query because you needto click the “fx” button on the right to get the following screen:

Expression Window Dynamic Query

The “=” sign indicated the start of the expression. For readability reasons I join every new line with the “&” sign. I could have used one big line of code but this makes it easier to read.

During execution I use several parameters to filter out values. That can be seen on the last two lines in the expression the image above. Using this method of filtering cuts down the amount of data I retrieve during execution.

The solution

How can I look at the query after it’s executed? The solution is to create a text box and enter the following expression:

Solution Dynamic Query

This expression will display the query of the data set in the text box during execution.

This doesn’t solve the problem yet because we’re still dealing with the problem that the query fails during execution.

How to display the query without executing it? The solution is really simple but you it requires some understanding how SSRS compiles the expression during execution.
The fact is that the query expression is compiled as a single line of code.

To display the code we can use the “–” to create a comment.

Commentec Expression Dynamic Query

This will render the query but it will not execute out query:

Compiled Query Dynamic Query

There you have it, your compiled query.

The next step would be to copy this code and paste it to SSMS or SQL OPS to further debug the query.

 

Looking back at my first lightning talk

Standard

Reading Time: 2 minutes

I had the opportunity to speak at the PASS Summit 2017 with a lightning talk.

This was the first time I ever did a lightning talk and it was different than a normal session.

It all boils down to the fact that you only have about 10 to 15 minutes for the entire talk.
This brings a couple of complications because suddenly you have to make sure your content fits within that time frame. You also have to make sure with the amount of content that you don’t go too fast. Going to too fast in a lightning talk is disastrous because attendees will not be able to follow you.

I normally do 60 minute sessions where I have the time to send to dig a little deeper than originally planned.

So how can I do such a short session and still make sure that the audience gets a bang for their buck?

After thinking about it I made a couple of steps:

  1. Write down the subjects I wanted to talk about
  2. Make the content for the subject and made sure it was short and to the point
  3. Present the content out loud and record it

During the recording I would watch the timer in PowerPoint to see when I would hit the 10 minute mark.

If I had gone over I would go into the content again and try to adjust it.
If I made it within those 10 minutes I would watch the recording and pay attention to how fast I was talking. I had to adjust multiple times to make sure I wasn’t going too fast.

After a couple iterations I was satisfied with the preparation and I could go into the presentation with confidence.

How did the session go?

This was actually really funny. I noticed on my itinerary for my flight that I would have to leave to the airport before 10:00 AM. The lightning talk sessions were from 9:30 AM to 10:30 AM so I had to make sure I was the first one to present because otherwise I wouldn’t be able to make it to my flight.

Fortunately the other presenters were so kind to let me go first and I thank them for it.

Because I prepared this session pretty well everything went smooth. I was able to do my talk and show some a couple of demos and finish within the 10 minute frame.

A couple of weeks later I got the feedback from several people from the audience and I was excited about that. Lots of people liked the content and the overall session so that was a big win for me.

Conclusion

Looking back this was a very good experience for me. I find doing a lightning talk is way more difficult than a normal session. It all comes down to preparation and placing yourself into the audience.

If you attended the lightning talks as PASS Summit 2017, please leave a comment because I’d really like to know your opinion about what went well and what didn’t. There is always an opportunity to learn and I like to get better with every session I do.

 

Pretty up your KeePass

It's So Beautiful
Standard

Reading Time: 2 minutes

Having a password manager these days is almost required especially for people working in IT. One password manager that I’ve been using for years is KeePass. You can secure the database with a master password and additionally (and recommended) create a key file.
You can create folders to differentiate the entries and it has a ton of features to make password management easy.

To make things even easier several browsers like Firefox and Chrome support using KeePass to handle passwords for websites.

If you don’t already have some sort of password manager, and I don’t mean the Post-Its you have on your monitor, I would tell you to at least give it a try. There are other solutions out there but this one is free and has made my life a lo easier.

So back to the original subject, prettying up KeePass. You might have noticed when you use KeePass that you can assign different icons to an entry.

Entry Details

And when you click the icon button you can select many standard icons.

Icon selection

KeePass makes it possible to add a custom icon and save it into the database. You don’t have to have the icons available on your computer.

As you may see in the image above I also have a selection of custom icons that I’ve added for my entries. In example I have an entry for LinkedIn for which I have the official icon for.

These days almost every bigger companies website has it’s logo in the top bar of the browser. That’s called a “shortcut icon”. Most websites have them named like “favicon.ico” or something similar.

If you open the code of the website and look for the i.e. shortcut icon you will get an URL that points to the icon. Copy the URL, open the password entry, click the icon button, click the add button for custom icons and paste the URL into the filename textbox.

Add new icon

Click the “Open” button and give it a couple of seconds to get the icon file downloaded and imported into the database. After the import of the icon it’s selected in the icon picker window. Click “OK” and you’ll see that your entry has it’s own icon.

Icon selection result

Now what are you waiting for, go pretty up your KeePass entries.

My First Virtual Presentation

Standard

Reading Time: 1 minute

firstOn March 16 I will be presenting my first virtual session for PASS and I’m really excited!

The session is called “Documenting SQL Server with PowerShell”.

The abstract:

Documentation is mostly overlooked and only comes up when a problem arises. What if you’d have a tool or method to generate documentation for all your database servers? In this session, I will show you show how easy it is to use PowerShell to retrieve information from your servers. I’ll detail what can be used to document your servers, how to retrieve the information and what should be documented. In the end you no longer have an excuse not to document your servers.

If you’re interested you can register with the link below and I’ll see you on March 16th on the PowerShell Virtual Chapter.

Registration URL: Documenting SQL Server with PowerShell
Webinar ID: 153-105-707

 

DBA performance evaluation

Standard

Reading Time: 3 minutes

evaluationLike most people I get my yearly performance evaluation but I’ve had the experience that my peers don’t really know how to evaluate me. So how do you effectively evaluate a DBA.

Your manager/team leader/superior has the information from previous performance interviews and based on that sees if you have performed as you should, or under, or above what was expected.

What I found out is that when the IT department doesn’t have a good understanding of the DBA’s responsibilities and possible metric to measure the effectiveness of the DBA (group) the evaluations don’t work.

How are most evalutions done

Your superior should have at least the following information:

  1. Knowledge of the DBA’ s job requirements
  2. Record of classes / training attended throughout the year
  3. Attendance records
  4. Productivity reports
  5. Records of previous performance interviews

You and your superior come together and based on the information above your superior shows how you performed.
In most cases you only have little room for discussion because most things are already discussed in previous interviews.

The problem

The problem is that there are so many factors that make this so difficult. A few questions that come to mind are:

  1. There are many different types of DBA’s, which one is used for evaluation
  2. What are the metrics to measure the performance and effectiveness on
  3. When is a DBA successful and and when is his/her performance good/medium/bad

The problem with this is that most managers I had (don’t get offended if you’re one of them this is just my experience) only look for certain aspects that can be found in other IT related jobs like how many incidents you solved or changes that are made.

You can look at the amount of incidents that were registered and fixed during that year but that’s only the case when you talk about real incidents like a server going down, low disk space or performance related problems.

This can be very offending towards other people who could do a tremendous amount of work in the background but is never really visible.

A DBA is a “jack of all trades” kind of person but to clearly evaluate a DBA you have to have a clear understanding what he/she does.

The solution

To address the first question in Problem-paragraph Craig Mullins wrote a nice article about this which gives a good overview of what kind of DBAs there are. Of course there are different versions and flavors especially with the DevOps who are hot nowadays.

An article that helped me out and for most still applies is an article written also by Craig Mullins called “Measuring DBA Effectiveness“. It helped my manager and me to create an evaluation metric.

The last thing, and I surely hope that’s in place, make reports from your service management application like Topdesk or SysAid. The reports give a clear understanding of the workload that’s been done.

To have another metric for the workload I used Brent Ozar’s “sp_Blitz” to create a health-check with all the items that need to be addressed. When an item of the list is done, remove it or give a status done, to keep track about what’s been done. When the manager asks for the progress of the list you can hand over the list.

I hope this helps you out for your next evaluation.

 

Visio Stencil for SQL Server: Updated

Standard

Reading Time: 1 minute

When I look at the statistics of my blog a lot of people seem to search for the Microsoft Visio stencil I once made somewhere in 2010. Unfortunately I haven’t updated the stencil for the new SQL Server versions.

I updated the stencil and created some new objects, adjusted some coloring.

visiostencil_sqlserver

If you’re interested, you can download it from here

Lorem Ipsum feature in Word 2007/2012

Standard

Reading Time: 1 minute

Every webdeveloper or programmer is familiar with the Lorem Ipsum text used to fill up pages or output text.

When I write documents I start with creating the chapters and fill them up with random text. The use of Lorem Ipsum generators makes this easier, but wouldn’t it be easier if there was a feature that did this for you instead of copying and pasting the text from generators?

It turns out that Word 2007 and Word 2012 have an undocumented feature for creating lorem ipsum text parts.

All you have to do is use the following code:

and hit the enter key

After hitting the enter key you’ll have the lorem ipsum text inserted:

The feature has some extending parameter which makes it easy to generate large amounts of lorem ipsum text.

You could do the following to create 5 paragraphs with 14 sentences:

I really like this feature and maybe you too.

DPM error Volume missing

Standard

Reading Time: 3 minutes

Have you’ve ever had a Monday where you thought everything would be unicorns and rainbows? I thought I had one this week but unfortunately I got the following error message in DPM “Volume missing”.

My good mood buzz was gone and I was not amused. I’ve dealt with this kind of errors before and I know this can be a hell of a job to fix.

The steps to fix this issue is twofold. First you need to rescan the disks on the DPM server and second you need to rescan every disk in DPM.

1. Rescan the disks on server

Go to Start, click on run and type in “diskmgmt.msc”

Click in the top menu on “Action” and click “Rescan Disks”

Just wait and relax while you see the screen below. When the screen disappears the action is complete.

2. Rescan the disks in DPM

In the DPM Administrator Console click on the management tab.

Click on the tab “Disks” which will show you a screen similar to the one below.

Right-click on the first disk in the list and click on “Rescan”.

Just sit back and relax while the screen below appears.

When the screen disappears repeat the action for the next disk. Unfortunately Microsoft hasn’t thought of a bulk action to finish this task so if you have a lot of disks as it was the case in my situation, get a cup of coffee and start scanning disks for about an hour.

TIP
De details screens below shows the protected data sources  which are present on the disk. You don’t have to rescan all the disks if you know which disk which holds the datasource which failed.