SQL DB Pros

SQL Server Tips and Scripts from and for SQL Consultants

SSRS Sort Legend: It can be done!

While working with a stacked bar chart in SQL Server Reporting Services (SSRS), I needed to change the sort order for the legend without changing the sort order of the stacked bar. Despite the fact I personally think stacked bar charts are a poor way to visualize data people still want to use them. Didn't they read my post on charting best practices?!? One of the tricks with a stacked bar chart is that only the bottom value can actually be measured. Thus, we frequently want to force the most important measure to sort to the bottom of the stacked bar. Uh oh! Once you sort the most important measure to the bottom of the stacked bar, the legend will now be sorted with the most important measure on the far right, most likely where the least important value would be described. I was starting to think it wasn't going to be possible to correct this annoyance until I stumbled on a clue in a thread on another site (On SSRS 2008, Why Is The Legend Sorted In Reverse Order?) Most of the suggestions didn't actually change the sort order of the legend without changing the sort order on the chart (again, stacked bar for my example). Buried among the suggestions which didn't work were a few sentences, apparently from Sean Boon of the SSRS Team (Boon Blog), which did the trick.

Instead of right clicking on the legend and looking at the "Legend Properties" as Continue reading →

Use F5 To Execute A Query In Visual Studio

If you're coming to Visual Studio 2010 from SQL Server Management Studio you're probably getting annoyed by the fact that pressing the F5 does not execute your SQL when using the T-SQL Editor window. Do not fear! Like the $6 Million Dollar Man, "we have the technology… Better… Stronger… Faster." Instead of the three buttons required by VS 2010 (CTRL+SHIFT+E) a single button can do the job. Behold! F5!

Okay, sorry about that. Here are the steps:

  1. From the Tools Menu select "Customize".

  1. In the Customize window click the "Keyboard" button.

 

 

  1. Now here's where the action is! Type "exec" into the "Show commands containing" box.
  2. Select "Data.SqlEditorExecuteSql" in the list box below where you typed "exec".
  3. Select "Transact-SQL Editor" in the "Use new shortcut in:" dropdown.
  4. Set the cursor in the "Press shortcut keys:" box and press "F5".
  5. IF YOU SKIP THIS STEP IT WON'T WORK! Click the "Assign" button. (Yes, I'm speaking from experience).
  6. Click "OK".
  7. Click "Close" in the "Customize" window.

That's it! Now, in addition to CTRL+SHIFT+E, you can press F5 to execute your SQL queries allowing you to use the same shortcut between SQL Server Management Studio (SSMS) and Visual Studio 2010.

Note: This does mean you will no longer be able to press F5 to run the data generator but come on, how often are you really going to use that thing compared to executing SQL?!?

SSIS: No column information was returned by the SQL command

Are you trying to use a stored procedure as the source of a SQL Server Integration Services (SSIS) data flow task?  If your stored procedure contains a temp table you may run into the message "No column information was returned by the SQL command" (complete error message below).  Although there are a number of solutions to this problem many of them have real performance and maintainability implications.  Luckily, there's a very simple solution which should work well for most situations. Continue reading →

The Four Database Design Principles You Should Be Using

"Obey the principles without being bound by them."
-Bruce Lee

 

Do you have some guiding principles you use when designing a new feature or when implementing a new requirement?  I do.  And you should too.  I put them to paper a few years ago while we were undergoing a hiring binge and I wanted to make sure our entire team was on the same page with regard to a few core concepts.  These are the guidelines I use when evaluating my own work, before presenting it to someone else.  When there's a fork in the proverbial road I use these design principles, in the order listed, to help guide my decision.

 

  1. Simplicity over complexity
  2. Maintainability
  3. Willing to sacrifice flexibility for simplicity and maintainability
  4. Scalability and performance

 

Disagree?  Would you order them differently? Have your own principles? Let's hear it.

Free Zip Code, City, County, State, CSV

Free Download of the zip code CSV file available at the bottom of this post.

Why this is so hard to track down I don't know.  We were looking for a comma
delimited list which would map zip code to county since county is the lowest
grain available in the out of the box SQL Server Reporting Services (SSRS) maps.  There are plenty of vendors offering this data for a fee, but who wants to pay a fee for a proof of concept project?  Not I. Continue reading →

Execute Stored Procedure From Excel With Temp Table

Do you want to execute a stored procedure from Excel?  No problem!  Does your proc contain a temp table? Problem!  Actually, don't panic.  The fix is incredibly simple.  Tracking down this post was probably the most difficult part.  When executing a SQL Server proc with a temp table you must remember to "SET NOCOUNT ON" at the beginning of the procedure.

Shout out to The NYC DBA for solving this issue for me!

The Three Best Tips For Effective Code Reviews And Preventing Knife Fights!

Bring a group of senior business intelligence professionals or SQL Server pros together to review each others code and you have all the potential for a good knife fight.  As our team is getting ready to make design/code reviews a regular part of our development process we're looking to make sure we get off on the right foot.  This led me to a great post by Ben Kamens of the Khan Academy.  Ben provides six tips for effective code reviews. Of those six there are a few in particular I want to make sure my team and I focus on: Continue reading →

Is Your Team Defending The Status Quo? (Are you?)

Saw this blog post from Seth Godin.  It outlines a number of points that might indicate you are defending the status quo.  Don't get me wrong, in many cases there are benefits to the status quo.  It's proven, it's understood, you know the pros and you know the cons.  In many cases sticking with the status quo may be the right choice.  One thing I can guarantee though is that sticking with the status quo simply because it's the status quo is never the right choice.

According to the post the following are some warning signs that you are defending the status quo: Continue reading →