Author Archives

Mark

Program Manager on the Power BI Customer Advisory Team at Microsoft.

My Top Ten Lessons From SQL Saturday #31 Chicago

I attended SQL Saturday Chicago this weekend. I had an awesome time and attended great sessions. While I got something good out of every one of the sessions, I decided to distill the entire experience into a top ten list with some of the highlights.

SneakersOnWire

10. According to Google Maps, the distance from Grand station on the CTA Blue Line to Navy Pier is 2.1 miles. I learned this after #8 below.

9. According to Google Maps, the distance from Navy Pier to Grand station on the CTA Blue Line is 2.1 miles. I learned this after #8 below.

8. Just like with shrinking databases, the fact that you CAN walk the distance in #10 above and then walk the distance in #9 above (all in under 2 hours, to ensure you will make it back to the airport in time to catch the last shuttle back to the hotel) does not make it a good idea to do so. AND, just like shrinking a database can affect its future performance, the walk mentioned above created soreness that is definitely affecting my current walking performance. But hey, while I was walking I did what anyone else would do: I asked myself, "What would Forrest Gump do?" Keep on goin’ is what. 

7. A good tip from Jeremiah Peschka ( Twitter | Blog ) is to create variables for commonly used strings (like creating new lines, or escaping single quotes) when building dynamic SQL to keep your code as readable as possible.

6. Kevin Kline ( Twitter | Blog ) pointed out that mixing DDL and DML within a stored procedure will cause recompiles, which can add significantly to resource consumption and execution time.

5. If it is a chilly Friday night in April around 8pm and you buy a churro from the little churro hut on Navy Pier, they may just offer you a second one for free as they are closing up anyway. Sweet. A little fuel for #9 above.

4. Before Andrew Karcher’s ( Twitter | Blog ) presentation on MDX, I knew jack about MDX (I could usually spell it properly, but that’s about it). Now, thanks to him, I don’t know jack anymore. Wait. I mean I now understand the basics that were a mystery to me before. Yeah. The second one.

DevilsTower3. Arie Jones ( Twitter | Blog ) pointed out something I had not read concerning SQL Server 2008 R2 Reporting Services. When exporting to multiple worksheets in Excel, R2 allows you to name those worksheets without sacrificing black cats or running off to Devil’s Tower with Richard Dreyfuss. Hazzah to the product team for adding this. Hazzah, I say!

2. When doing Karaoke in a room full of geeks, requesting Barry Manilow’s Copacabana and substituting Weird Al Yankovic’s Star Wars Cantina lyrics can go over well.

1. Wendy Pastrick ( Twitter ), Ted Krueger ( Twitter ), Jes Borland ( Twitter ), and many others worked hard to put on a great SQL Server event. They succeeded quite well, indeed. Thanks so much to all organizers, volunteers, and presenters.

Making Your SSIS Configuration Files More Readable

When you first create a configuration file in your Integration Services project, the XML contents of the file are just laid out in one long line. This makes reading and/or modifying this file more challenging than it needs to be.

There is an easy way to format your file to alleviate this issue. In BIDS, go to File/Open/File, as in the image below:

image

Browse to your configuration file and select it to open within BIDS.

My brand new configuration file looks like this image below:

image

You see that the contents are all on one line. Bleh.

To fix that, you can go to Edit/Advanced/Format Document, as shown below:

image

I have done that to my config file and now it looks like this:

image

As you can see, this is much easier to deal with. Just save this document and close it. Now, even when you open it in Notepad, that formatting persists.

image

This little trick can make dealing with your configurations files a bit easier.

A Hint on Using Indexed Views For Read Performance

Some time ago, I was on a project in which we implemented a report model. The data was located in several different databases, none of which were very conducive to reporting. The consequence was that the views upon which we based our entities for the model required lots of joins and performed poorly. We decided to implement indexed views in order to boost the read performance and improve the experience for the users. With indexed views, the data returned by the view is persisted in a clustered index, just like a clustered table, which can have great benefits in read performance.

NOTE: You should research and understand the requirements and cost of using Indexed Views before implementing them in your environment. Indexed views incur a cost in terms of write performance, so you will want to test extensively to make sure the gain on reads is worth that write cost.

You can read details about Creating Indexed Views here.

One thing I noticed is that when I first queried the indexed views, the performance didn’t really change. The reason is that the query optimizer typically defaults to expanding the view definition to access the underlying objects directly. I will demonstrate this in a moment. I did find, however, if my index on the view covered all the columns necessary to satisfy my query, then the optimizer did in fact go ahead and use the index on the view instead of expanding.

You can read details about Resolving Indexed Views here.

Let’s demonstrate this behavior I mentioned above where the indexed view does not cover the query.

CREATE TABLE dbo.Musketeer

(

      MusketeerID int NOT NULL 

    , MusketeerName varchar(20) NOT NULL

)

 

CREATE TABLE dbo.Lackey

(

      LackeyID int NOT NULL

    , MusketeerID int NOT NULL

    , LackeyName varchar(20) NOT NULL

)

 

CREATE TABLE dbo.Sword

(

      SwordID int NOT NULL

    , MusketeerID int NULL

    , SwordName varchar(20) NOT NULL

)

 

INSERT INTO dbo.Musketeer VALUES(1,'Athos')

INSERT INTO dbo.Musketeer VALUES(2,'Porthos')

INSERT INTO dbo.Musketeer VALUES(3,'Aramis')

 

INSERT INTO dbo.Lackey VALUES(1,1,'Grimaud')

INSERT INTO dbo.Lackey VALUES(2,2,'Mousqueton')

INSERT INTO dbo.Lackey VALUES(3,3,'Bazin')

 

INSERT INTO dbo.Sword VALUES(1,1,'Cutlass')

INSERT INTO dbo.Sword VALUES(2,2,'Rapier')

INSERT INTO dbo.Sword VALUES(3,3,'Epee')

 

In the code above, we create our tables and insert some data. I chose The Three Musketeers today. NOTE: I made up the data regarding the Sword preferences.

Let’s create our view.

CREATE VIEW dbo.vMusketeerInfo WITH SCHEMABINDING

AS

SELECT

      m.MusketeerID

    , m.MusketeerName

    , l.LackeyName

    , s.SwordName

FROM dbo.Musketeer m

INNER JOIN dbo.Lackey l

ON l.MusketeerID = m.MusketeerID

INNER JOIN dbo.Sword s

ON s.MusketeerID = m.MusketeerID

 

Let’s query our view, returning the Actual Execution Plan:

SELECT 

      MusketeerID

    , MusketeerName

    , LackeyName

    , SwordName      

 FROM dbo.vMusketeerInfo

 

/* RESULTS

MusketeerID MusketeerName  LackeyName  SwordName

----------- -------------- ----------- --------------------

1           Athos          Grimaud     Cutlass

2           Porthos        Mousqueton  Rapier

3           Aramis         Bazin       Epee

 

(3 row(s) affected)

*/

 

The plan returned is the following:

image

 

Notice that the Optimizer is doing a Table Scan on each table referenced.

Now let’s create an index on our view:

CREATE UNIQUE CLUSTERED INDEX ix_vMusketeerInfo_MusketeerID

ON dbo.vMusketeerInfo (MusketeerID)

GO

 

Great. Now that we have an index on our view, we should be all set, right? Let’s query the view again.

SELECT 

      MusketeerID

    , MusketeerName

    , LackeyName

    , SwordName      

 FROM dbo.vMusketeerInfo

 

/* RESULTS

MusketeerID MusketeerName  LackeyName  SwordName

----------- -------------- ----------- --------------------

1           Athos          Grimaud     Cutlass

2           Porthos        Mousqueton  Rapier

3           Aramis         Bazin       Epee

 

(3 row(s) affected)

*/

 

OK. We get the same results, as one would expect. How about the plan?

image

 

The plan is the same. The Optimizer is still performing Table Scans on each table. SQL Server has expanded the view and accessed the underlying objects directly. If you want to make sure that you are going to use the unexpended view, you can add the WITH (NOEXPAND) hint.

SELECT 

      MusketeerID

    , MusketeerName

    , LackeyName

    , SwordName      

 FROM dbo.vMusketeerInfo WITH (NOEXPAND)

 

/* RESULTS

MusketeerID MusketeerName  LackeyName  SwordName

----------- -------------- ----------- --------------------

1           Athos          Grimaud     Cutlass

2           Porthos        Mousqueton  Rapier

3           Aramis         Bazin       Epee

 

(3 row(s) affected)

*/

Again, the results are the same. However, we have a new plan.

image

 

The WITH (NOEXPAND) hint dictates to SQL Server that the view is not to be expanded to the underlying objects. Thus, in our implementation, we achieved a tremendous gain in READ performance. We did incur a cost on write performance, but in our implementation, the benefits to reads greatly outweighed that write cost.

Finding Foreign Keys

No, this is not a new initiative by United States Immigration and Customs Enforcement. I am talking databases.

I recently found myself in a situation where I wanted to find all the tables that referenced a particular table via foreign key relationships. I had heard that relying on the built-in dependency tracking (right-clicking the object in SSMS and choosing View Dependencies) was not always reliable. Indeed, SQL Server MVP Aaron Bertrand (Twitter) wrote a great blog post about ways in which sysdepends, sp_depends, and sys.sql_dependencies can lose touch with reality.

I figured I could find the info I wanted by querying the system catalog views. Here is the script I wrote using AdventureWorks as the sample DB.

USE AdventureWorks

GO

 

DECLARE 

      @ReferencedSchema varchar(1000)

    , @ReferencedObject varchar(1000)

 

SET @ReferencedSchema = 'HumanResources'

SET @ReferencedObject = 'Employee'

 

SELECT 

      rs.name + '.' + ro.name AS ReferencedObject

    , rc.name AS ReferencedColumn

    , ps.name + '.' + po.name AS ParentObject

    , pc.name AS ParentColumn

    , co.name AS ConstraintName

FROM sys.foreign_key_columns fkc

INNER JOIN sys.objects co

ON co.object_id = fkc.constraint_object_id

INNER JOIN sys.objects ro

ON ro.object_id = fkc.referenced_object_id

INNER JOIN sys.schemas rs

ON rs.schema_id = ro.schema_id

INNER JOIN sys.columns rc

ON rc.object_id = ro.object_id

AND rc.column_id = fkc.referenced_column_id

INNER JOIN sys.objects po

ON po.object_id = fkc.parent_object_id

INNER JOIN sys.schemas ps

ON ps.schema_id = po.schema_id

INNER JOIN sys.columns pc

ON pc.object_id = po.object_id

AND pc.column_id = fkc.parent_column_id

WHERE rs.name = @ReferencedSchema

AND ro.name = @ReferencedObject

ORDER BY 

      ps.name

    , po.name

 

Here are the results I get.

image

You can see that there are 7 foreign key constraints that reference the HumanResources.Employee table in my copy of Adventureworks. You can see that Parent object to which each of these constraints belong. And you can see the actual name of each constraint.

Feel free to take that query and tweak it to add information you would find useful. Also feel free to post it in comments here. I really want my blog to be a two-way street and would love to encourage debate.

I also encourage you to play around with querying the system catalog views. You can find some really cool stuff in there.

Saving Indiana Jones (and your SSIS packages) From “Bad Dates”

I have been working on a project modifying the ETL for a data warehouse. The source is a Pervasive database which, through SSIS 2005 packages, populates a SQL Server 2005 data warehouse and associated SSAS 2005 cubes. I encountered an issue which reminded me of a scene in the first Indiana Jones film, Raiders of the Lost Ark. If you had a nickel for every time you heard of SSIS reminding someone of Indiana Jones….you’d now have….a nickel. Yeah. I’m kind of different.

It was a scene in Cairo where Indy and Sallah had taken the headpiece to the Staff of Ra to an old man who they asked to translate the markings on the object. While they are talking, Indy tosses a date into the air to catch it in his mouth. Sallah, noticing a victim of the dates (poisoned moments before by a nefarious character) laying dead, snatches the date from the air just above Indy’s mouth with the line, “Bad dates.” Sallah saves Indy’s life by preventing the bad date from being consumed. Sounds a lot like an SSIS data flow to me, and you may be starting to see my connection.

In my situation, the source system had allowed invalid dates to populate columns with Date data type. These dates had a year of 0000. My client wanted me to route the records with invalid dates to a separate table for later analysis. Ok. Cool. I would just use the ISDATE() function in SSIS to separate out the records with “bad dates.” Easy.

<<insert cool John Williams music as I proceed to attempt to use ISDATE in SSIS only to find that it does not natively exist>>

Now what was I going to do? “Don’t know. I’ll think of something.”

I played around with using the Data Conversion transformation, planning to route the invalid dates out the Error output . That was not working for me. Instead, it was seeing the year 0000 as 2000 and just letting all the records out the Success output. Suboptimal.

So, I reached out to the SQL community on Twitter (using the #sqlhelp and #ssis tags) asking if there was an elegant way to perform ISDATE() type operations in SSIS. I was thinking I may end up doing some scripting (not my strong suit to be sure) but was trying to see if there was a cool way to do it without scripting. Looking back at that, I should have embraced the opportunity to learn more about scripting instead of trying to stick with the tools I know better. Josef Richberg (blog: http://josef-richberg.squarespace.com/, Twitter: @sqlrunner) responded by suggesting I look at VB functions. That was the final little push I needed to dig into the Script Component (transformation). And I am glad for having received it.

I started by changing my data source query to convert the date field involved to a string before bringing it into the data flow. Here is my example to illustrate.

We will create tables to play with in the Adventureworks database, in the markvsql schema.

USE Adventureworks

GO

 

--Create the markvsql schema if it does not already exist

IF NOT EXISTS

(

    SELECT * FROM sys.schemas WHERE [name] = 'markvsql'

)

EXEC sp_executesql N'CREATE SCHEMA markvsql'

GO

 

CREATE TABLE markvsql.isdatessis2005_source

(

      checkdate_string varchar(30) NULL

)

 

CREATE TABLE markvsql.isdatessis2005_destination

(

      checkdate_string varchar(30) NULL

    , checkdate datetime NULL

)

 

CREATE TABLE markvsql.isdatessis2005_errorlog

(

      checkdate_string varchar(30) NULL

    , failreason varchar(100) NULL

)

 

/*insert the good date*/

INSERT INTO markvsql.isdatessis2005_source (checkdate_string) 

VALUES('2010-01-01 00:00:00.000')

/*insert the bad date*/

INSERT INTO markvsql.isdatessis2005_source (checkdate_string) 

VALUES('0000-01-01 00:00:00.000')

You will note that the second record has a checkdate_string value with a 0000 in the year position. If we take a moment to try converting our checkdate_string field to a date, we will see the problem.

SELECT

      checkdate_string

    , CONVERT(datetime, checkdate_string) AS checkdate

FROM markvsql.isdatessis2005_source

This query leads to the following error message:

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

If we were keeping this all in T-SQL, we could just use the ISDATE() function and be on our way. However, SSIS lacks this function natively. Thus, I used the Script Component (transformation) to check for invalid dates, populating a new boolean Output column with the result of the VB ISDATE() function. I then used the Conditional Split to route the valid dates to my destination table and the invalid ones to my errorlog table based on the new boolean field’s value. This worked quite well for me and I was astounded at how easy it was to implement.

Here is a screenshot of my data flow, which is pretty much self documenting.

image

For those unfamiliar with the Script Component (transformation), I was, before doing this, you must choose whether the component will be used as a Source, Destination, or Transformation when you add it to your data flow. For the purposes of this process, you must choose Transformation. Here are screenshots of the Script Component (transformation) properties.

image

The only input column in our example is the checkdate_string field. The Usage Type defaults to read only and I left it that way.

image

The checkdate_string_isdate field was added as a new column (via Add Column button) and assigned the Boolean Data Type since the VB ISDATE() function returns a boolean result.

image

Notice the single line of code:

Row.checkdatestringisdate = IsDate(Row.checkdatestring).

That is seriously the only line of code I had to write. And, thanks to Intellisense, I bagged it on the first try. All it does is set that value of our new boolean output field to the result of the IsDate() function.

When we run our package, we see that our Script Component (transformation) and Conditional Split work just fine indeed.

image

That is really all there is to it. This same technique also works in SSIS 2008, although to use the VB scripting, you will need to change the scripting language in the Script transform from C# (the default) to VB. I poked around with doing this in C# for this post and soon decided that, for the moment, I will leave that to others.

When there are bad dates to be dealt with in a house in Cairo, then Sallah is your man. For SSIS, you can use the Script Component (transformation).

I have included a zip of my SSIS 2005 solution which also includes the .sql file featuring my code snippets shown above. Note that you will need to change the SQL instance in the Adventure Works data source to match your own.

ISDATE Functionality In SSIS Solution Download

Linked Reports: Getting the Montagues and Capulets To Share

Two households, both alike in dignity,

In fair Verona, where we lay our scene,

From ancient grudge break to new mutiny,

Where civil blood makes civil hands unclean.

          ~ Romeo and Juliet, William Shakespeare

 

Those Montagues and Capulets don’t do so well with the sharing thing. They fight. A lot. This causes a lot of trouble in Verona, often resulting in various Montagues and Capulets winding up in jail.

Let’s suppose that Prince Escalus, essentially the mayor of Verona, decides to commission a report detailing what members of each family have been jailed. Escalus has decreed that an Excel attachment of this report is to be distributed to each of the families via subscription from SQL Server 2008 Reporting Services (Standard Edition). Each report will contain only the information for that family and the file name of the Excel attachment must include the name of the family as well. The definition of each report is identical, merely the records and report name (and thus the Excel attachment) must be personalized for each family. This is based on a solution I implemented for a client a while back. I think this solution is fine when there are not a lot of different Family values. For example, if you have 100 different families, then perhaps creating 100 different linked reports and their subscriptions may not be ideal. In such a scenario, you may want to create an SSIS solution that will perform the exports and emails using package variables for the personalization of each ( have not tried it but it seems like it should work). But for now, I will instead recount the process I used that worked well for the client. If you have solved this a different way, I would love to see it and possibly even shamelessly steal it should I encounter this situation again. 🙂

One could accomplish the report personalization goal by creating two reports, one for each family. This would get the job done. However, from a manageability standpoint, this is not ideal. Suppose there were 20 families involved? Do you really want to maintain 20 all but identical reports, propagating changes to all 20 reports when they are required? Instead, why not create a single report with a Family parameter? That would take care of the manageability issue since you would have only one report definition to maintain. However, this would not solve the personalization goal. This is because the name of an Excel attachment exported from SSRS defaults to the the name of the report itself. For sooth!

Enter Linked Reports! (MSDN How To: Create A Linked Report) This link is for SQL 2008 but 2005 linked reports are essentially the same as far as I have scene. And it was actually in SQL 2005 that I implemented this solution for a client (not Prince Escalus) and it behaved the same in 2008 when I did it for this post.

A linked report is one that is based on the report definition of another report. Changes to the definition of the base report are propagated to all linked reports. But the linked reports have their own name (Aha!) and subscriptions (Aha! again), as well as some other properties. Zounds!

Let’s create our table. I am keeping this in the Adventureworks database for simplicity, but creating my own schema. I added the IF EXISTS for the schema since I am SURE you implement all of the code in my posts and therefore already have a schema named markvsql in your copy of the Adventureworks database. 🙂

USE Adventureworks

GO

 

--Create the markvsql schema if it does not already exist

IF NOT EXISTS

(

    SELECT * FROM sys.schemas WHERE [name] = 'markvsql'

)

EXEC sp_executesql N'CREATE SCHEMA markvsql'

GO

 

CREATE TABLE markvsql.VeronaJailing

(

      PrisonerName varchar(10)

    , Family varchar(10)

)

GO

 

INSERT INTO markvsql.VeronaJailing (PrisonerName, Family) VALUES('Romeo','Montague')

INSERT INTO markvsql.VeronaJailing (PrisonerName, Family) VALUES('Benvolio','Montague')

INSERT INTO markvsql.VeronaJailing (PrisonerName, Family) VALUES('Balthasar','Montague')

INSERT INTO markvsql.VeronaJailing (PrisonerName, Family) VALUES('Juliet','Capulet')

INSERT INTO markvsql.VeronaJailing (PrisonerName, Family) VALUES('Tybalt','Capulet')

INSERT INTO markvsql.VeronaJailing (PrisonerName, Family) VALUES('The Nurse','Capulet')

Now that we have some data, we will create the stored procedure to return our report dataset.

CREATE PROC markvsql.GetVeronaPrisoners

      @Family varchar(10)

AS

SELECT

      vj.PrisonerName

FROM markvsql.VeronaJailing vj

WHERE vj.Family = @Family

GO

Now let’s give our fabulous sproc a spin around the block.

Let’s meet our Capulets <<applause>>:

EXEC markvsql.GetVeronaPrisoners @Family = 'Capulet'

GO

 

/* Capulet RESULTS:

PrisonerName

------------

Juliet

Tybalt

The Nurse

*/

And our Montagues <<applause>>:

EXEC markvsql.GetVeronaPrisoners @Family = 'Montague'

GO

 

/* Montague RESULTS:

PrisonerName

------------

Romeo

Benvolio

Balthasar

*/

And now it’s time to play the Family Feud! …not really. That just seemed to fit here.

Now that the database code is all set to go, let’s take a look at our report, the Verona Jailing Report. I kept it very simple. It just pulls the list of prisoners for the Family selected.

For the Capulets:

image

For the Montagues:

image

I set up a subscription to the Verona Jailing Report which passes in Capulet for the Family parameter and emails an Excel copy of the report. Here is what the email looks like:

image

Notice that the name of the Excel file (and the report name in the subject line) are just the generic Verona Jailing Report. Until you open the attachment, there is no indication which Family this report is for. You could hard code the family name in the subscription so that it appears in the Subject line, but that still would not meet the requirement of having the Family name part of the Excel file name. Verily, ‘tis true.

The contents of the above Verona Jailing Report look like this:

image

Notice that the worksheet name has also taken on the name of the report. I just used an expression to bring the value of the Family parameters into textbox reading Verona Jailing Report: Capulet.

We therefore create a linked report derived from Verona Jailing Report called Verona Jailing Report – Capulet. We then modify the properties of the Family parameter of the Capulet report in Report Manger so that the user is no longer prompted and set its default value to Capulet. The parameter properties are shown below.

image

We then create a subscription for the Verona Jailing Report – Capulet report just like the one we created for the base report above. Behold:

image

Via our linked report, we have killed two of the birds with one of the stones. Both the subject and the Excel attachment now indicate this report is for the Capulet family. Hazzah!

DISCLAIMER: No birds were harmed during the creation of the post.

The contents of the Verona Jailing Report – Capulet file look like this:

image

We then create a linked report and subscription for the Montague family following the same steps and we have one happy Prince Escalus. And as a bonus, he LOVES the fact that the Montagues and Capulets are unknowingly sharing the report from which their own reports are derived.

And the Montagues and Capulets lived happily ever after….um….wait….

<theater soapbox rant>

While I am here, I need to get this off my chest. One famous line often quoted from Romeo and Juliet is Juliet asking “O Romeo, Romeo, wherefore art thou Romeo?” I just have to make it clear that Juliet is most certainly NOT asking where Romeo is (“Wherefore” means “Why”). Nor is she calling out to a voice-activated tracking system for the homing device she discretely planted on his lapel. Rather, she is calling attention to the lamentable tragedy that she, a Capulet, just fell for Romeo, a Montague, in a world where the Capulets and Montagues are essentially at war. Alas, poor Juliet, if only one of you could be from a different house! It makes more sense when you include more than just the one line:

O Romeo, Romeo, wherefore are thou Romeo?

Deny they father and refuse thy name.

Or if thou wilt not, be but sworn my love

And I’ll no longer be a Capulet.

          ~Romeo and Juliet, William Shakespeare

</theater soapbox rant>

NOTE: I have uploaded the rdl for the my report.

Verona Jailing Report

Jinkies! Passing MultiValue SSRS Parameters To Stored Procedures

SQL Server Reporting Services provides a great way to allows report users to select more than one value from the available values list in parameters, creating a multivalue parameter.

SSRS dynamically creates a comma separated list of the values selected by the user to pass in to the data source.

Great.

It also dynamically creates the Select All option at the top of the available values list.

Also great.

And, it is really easy to enable this feature.

Also also great.

But this great functionality does have a limitation right out of the box. You cannot pass a multivalue parameter to a stored procedure (see Using Single-Value and Multivalue Parameters).

Ruh roh. Not great.

In the past, I have had the data source sproc return all of the records and then used a filter on the data region to only show the records matching the values selected in the parameter. It got the job done, but returning records you are not going to use is suboptimal, to be sure.

Now, you may be thinking, dear reader, that I am a moron for choosing the above title for this post and then almost right away saying that you cannot do it. Well, provided that your database source is SQL 2005 or later, you actually CAN do it through the beauty of XML. And what is really really cool to me is that the amount of work you must perform to achieve this is miniscule. AND, it does not require much XML expertise, which I must confess, I currently lack. I have seen solutions for this obstacle involving the creation of user-defined functions, but I find this XML solution far more elegant and so easy to implement.

As I often do, I must shout out some thanks to Jason Strate (blog: www.jasonstrate.com, twitter: @stratesql) for showing me this.

You may be asking why I am so fond of having reporting data sources be stored procedures instead of just writing the query right into the report. While that is not truly the scope of this post, I will say that I really like having data access code in the database. Also, the security implications are great, since with sprocs, users need only Execute permission on the sproc and none against the underlying tables. There are other reasons, too, but those two are enough for now.

Setting Up The Data

Let’s start off by creating a table to house our data. I am using AdventureWorks (to make it easy) but creating my own schema (to keep things clean). NOTE: I looked but could not find any ScoobyDoo references in AdventureWorks.

USE AdventureWorks

GO

 

CREATE SCHEMA MarkVSQL

GO

 

CREATE TABLE MarkVSQL.ScoobyDooCharacter

(

      CharacterID int not null IDENTITY(1,1)

    , CharacterName varchar(10) not null

)

 

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Fred')

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Daphne')

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Velma')

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Shaggy')

INSERT INTO MarkVSQL.ScoobyDooCharacter VALUES('Scooby Doo')

NOTE: You may be thinking that Scrappy Doo is conspicuously absent from my list. There is a good reason for that. I can’t stand Scrappy Doo. He drives me nuts. I would rather bite down on a metal fork really hard with my front teeth than watch Scrappy Doo. </Scrappy Doo>

Creating Available Values List

Let’s create the stored procedure that will supply our Character parameter with available values. This is where we start to really have some fun.

CREATE PROCEDURE MarkVSQL.GetMysteryIncMemberList

 

AS

 

SELECT

      CharacterName

    , '<Character ID="'

        + CONVERT(varchar,CharacterID)

        + '"/>' AS CharacterIDXML

FROM MarkVSQL.ScoobyDooCharacter

This stored procedure returns the CharacterName along with the CharacterID field that has been modified to return the CharacterID value formatted as an xml node instead of just the int. Here is the fabulous output of our MarkVSQL.GetMysteryIncMemberList sproc.

CharacterName CharacterIDXML

------------- -------------------

Fred          <Character ID="1"/>

Daphne        <Character ID="2"/>

Velma         <Character ID="3"/>

Shaggy        <Character ID="4"/>

Scooby Doo    <Character ID="5"/>

Creating Data Source Sproc 

Now we create the stored procedure that will supply our report with data.

CREATE PROCEDURE MarkVSQL.GetMysteryIncMembers

 

    @CharacterID varchar(max)

    

AS

 

DECLARE @CharacterIDXML xml

 

SET @CharacterIDXML = REPLACE(@CharacterID,',','')

 

SELECT

      c.CharacterName

FROM MarkVSQL.ScoobyDooCharacter c

WHERE c.CharacterID IN

    (SELECT c.value('@ID','int') FROM @CharacterIDXML.nodes('//Character') AS x(c))

There are several things to note about this proc:

  • The parameter is a varchar(max) to allow as much space as we can for the string of comma separated XML nodes that SSRS will pass in.
  • We then declare an XML typed variable, @CharacterIDXML.
  • We remove the commas (which SSRS automatically adds as value separators) from the contents of the @CharacterID parameter and put the resulting list of XML nodes in the @CharacterIDXML variable.
  • Our WHERE clause uses the XML “nodes” method to shred out the CharacterID values from the XML.

Let’s run our MarkVSQL.GetMysteryIncMembers sproc with various parameters as they would be passed in from SSRS.

In this example, we just want to return Fred.

EXEC MarkVSQL.GetMysteryIncMembers '<Character ID="1"/>'

We get the following results:

CharacterName

-------------

Fred

 

(1 row(s) affected)

Now let’s get Fred and Daphne together. I think it is no secret that there is a spark between them.

EXEC MarkVSQL.GetMysteryIncMembers '<Character ID="1"/>,<Character ID="2"/>'

Here they are.

CharacterName

-------------

Fred

Daphne

 

(2 row(s) affected)

Now let’s get all of those meddling kids and their dog.

EXEC MarkVSQL.GetMysteryIncMembers '<Character ID="1"/>,<Character ID="2"/>,<Character ID="3"/>,<Character ID="4"/>,<Character ID="5"/>'

Here they are, in all of their mystery solving glory.

CharacterName

-------------

Fred

Daphne

Velma

Shaggy

Scooby Doo

 

(5 row(s) affected)

The configuration of the parameter in SSRS is nothing out of the ordinary, so I don’t feel it is necessary to go into the step by step process here. You can go here if you need more guidance on the SSRS piece, you can go here: Adding Parameters to Your Report. The article in this hyperlink is for 2008 but includes a hyperlink to the 2005 version as well. The configuration goals are the same, but interface differences between 2005 and 2008 make the exact steps a little different.

You just configure the @CharacterID parameter of the report to do the following:

  • Allow multiple values
  • Get its available values from the MarkVSQL.GetMysteryIncMemberList sproc.
  • Use the CharacterName field as the “Label” field so that the user is presented with the list of names.
  • Use the CharacterIDXML field as the “Value” field so that our XML nodes in a comma separated string are passed to the stored MarkVSQL.GetMysteryIncMembers sproc, just like in our examples above.

That’s really all there is to it. Roh boy!

Find All Deployed SSRS Reports That Reference A Particular Field

This summer I built an SSRS 2008 Report Model for a client. The end users report against the model using Report Builder 2.0.

I shall pause here for a rave.

<rave>

Report Builder 2.0 is just outstanding, in my humble opinion. I built a SQL 2005 Report Model for a different client a few years ago that is still using Report Builder 1.0 and the difference is astounding.

</rave>

We recently needed to change the data type of a field used in the model. The field in question is an integer in the source database but is essentially similar to an account number. The users preferred to have it be a string in their reports so that Report Builder 2.0 would stop insisting on trying to SUM it.

Making the change to the model was no big deal. However, I thought about what would happen to reports that were already created that may be using the field in question and how we could find those reports with minimal effort in case they would need to be updated.

Then I thought to myself, self, the reports are really just files stored in the reportserver database. You should be able to write a query against that db to return the names of any reports which utilize the field in question pretty easily.

After a quick search on the Interwebs, I found this:

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/58bc9be2-cc37-4fde-8a94-de3c7a00c613/

Hazzah! The reports actually reside in the Catalog table in the Content field (Image data type). I ended up creating a script similar to the following with my search string being the name of the field as it appears in the model:

SELECT [name]

FROM reportserver.dbo.catalog

WHERE CONVERT(varchar(max), CONVERT(varbinary(max), content)) LIKE '%fieldname%'

 

This worked like a champ. And it is really easy to come up with other uses for querying the reportserver database. I will be continuing to play with this for sure.

Cool Tool For Formatting Code

Long time no blog.

A colleague showed me this cool code formatting tool the other day. It is somewhat configurable and is right within my price range. I still end up making a few edits for my preference, but the vast majority of the formatting I care about can be done with the click of a button. I have been working with SQL code that has never really followed any standard so this provides a great head start.

It costs $0 to use it right on the website. With the exchange rate as of July 8,2009, that translates to 0 Euros, too, I think.

It appears that you can also purchase a downloadable desktop version, if you wish.

Here is the link: http://www.dpriver.com/pp/sqlformat.htm

Happy formatting.

Incorrect Information in SSIS 2005 Send Mail Task Tooltip For Attachments Property

I was trying to dynamically send multiple attachments via the Send Mail Task in a package.

In the Send Mail Task Editor, when clicking on the Attachments property field, the tool tip tells you that the file names must be separated by semicolons:

“Specifies the files to include as attachments to the message. Separate the file names with semicolons.”

Alas, a semicolon did not work for me. It was not until I separated my file names with a | (pipe) that my package ran successfully and emailed the files.

So, use a | to separate files in the Attachments property of the Send Mail Task in SSIS 2005; not a semi-colon.