FOR XML a la Mode –a study on Modes, especially using For XML Path Mode to Concatenate Data

Hello folks, Julie here with the as promised weekly Datachix blog—

Hup Hup—did you say just say  “Julie” here?

Yes I did.

You specifically said last week that you and Audrey would blog weekly and ALTERNATELY.  You posted last week. Isn’t it Audrey’s turn?

Yes, but Audrey is fiercely battling Reporting Services at a new client site–she can’t post this week.   Also, I think there were Ninjas…. and maybe some Bear-Sharks……… she’s really really….. busy.


 Hello folks, Julie here with the as promised weekly Datachix blog.  😉   Recently a buddy of mine left a comment for us which went like this:

 Can you post an example of putting data from multiple rows into a single column?

 For example you have this:

Cat | meow

Cat | eat

Dog | bark

Dog | fetch


And you want to display it as this:

Cat | meow, eat

Dog | bark, fetch

It’s hard to find a good example of this online.

Thanks, Ben

  Continue reading FOR XML a la Mode –a study on Modes, especially using For XML Path Mode to Concatenate Data

Debugging SSIS Variables—and a Happy Announcement from the Datachix.

Audrey and I have decided to blog regularly and alternately on Tuesdays. We’ve each had good runs of things and had months where we posted a lot, but we’ve never had great consistency. So now we’ve committed to a schedule.

From the Book of Bloggering Chapter 23, Verse 9: The Datachix shall blog weekly. The day of the week on which they shall post shall be Tuesdays. Mondays will not be the day the postings shall occur, unless it is only to load and schedule a post for the following day–Tuesday. Neither shall Wednesdays be the day of the posting, unless there be a time zone situation where the reader is residing in Wednesday time whereas the Datachix (the said aforementioned poster/bloggers) are residing in Tuesday, and therefore posting their blog. Thursdays are right out, as are Fridays. Don’t even mention Saturdays.

Continue reading Debugging SSIS Variables—and a Happy Announcement from the Datachix.

#sqlsat48 Datachix1’s Wrapup. Columbia SC/October 2, 2010

I had a great time at this past Saturday’s event in Columbia, SC where I  was lucky enough to be presenting.  I forgot to mention during my presentation –“Cool Tricks to Pull from your SSIS Hat” –that Carolina is my alma mater, and the Columbia area was home to me and my parents for 8 years. 

This was my first time back in 15, and besides the great professional event, I got to see several dear dear, friends from my previous life which was in Theatre.  I stayed at the lovely Hampton Inn in downtown Columbia (in the newish Vista area).  My mom came with me and we caught Rent at Trustus Theatre, which was only a few blocks from the hotel.  What an amazing show!  Shout out to the folks at Trustus for 26 years of theatrical awesomesauce. 

My mother happens to be a smart lady who is also interested in the SQL Server lifestyle, so she also came with me to the speaker’s dinner and the event itself.  It was really nice to share a little of my career with her.  I’m pretty sure she loved it.

This event was swarming with MVP’s and VIP’s and I got a chance to meet quite a few great folks and cavort with people I already knew. 

Where should I start?  Maybe with my some of my homies from Atlanta…..

Mark Tabladillo.  He’s a super smart guy with whom I shared a great conversation at the Speaker’s Dinner about Data Mining and ways to test the trustworthiness of your results and predictions.  My conversation with Mark illustrated to me the beauty of SQL Saturdays and events like them—you can not only learn cool and useful things, but enjoy great conversations and build relationships from which you can continue learning. 

Bob Langley.  Bob sat next to me at the speaker’s dinner and I was pretty sad to learn that we were scheduled to speak in the same time slot, because his presentation on Column Level Encryption really interested me.  This is a request I have received before, and I want to see what the possibilities are.  This was Bob’s first speaking engagement—kudos for speaking.

Stuart Ainsworth.  Stu did 3, yes 3 presentations.  I was not able to make any of them because of the restraining order, (just kidding), but I did hear that he did a great job.  He reported that his lunchtime demo was slightly hindered by a runaway process which ate his laptop, but I don’t think anyone noticed because

— the LUNCH was AWESOME!

                And FREE!  Thanks Sponsors

Special thanks also to Brian Kelley and Bobby Dimmick (and all the volunteers, especially Paul Waters who was my AV helper and the lovely man who watched our stuff all day in the speaker’s room).  The day really did go beautifully. 

Now the Outliers:

David Taylor from Alabama by way of Boston.   David was doing a new presentation on DBA tasks which my mother loved.  David is a recent convert to SQL Server whose enthusiasm for our community is palpable.  I always enjoy seeing David.

Jose Chinchilla.  Jose is a super nice guy that  I got to sit with at the Speakers Dinner as well.  He is running a SQL Saturday event in Tampa in January of 2011, which I hope to make.  Mom sat in on his certifications session, and she is really excited about getting some certifications now. 

Andy Leonard. (Blog | Linkedin | Twitter)  The Man . The Myth. The Moustache  :{>.  Andy is one of those Rock Star names in BI that I was super excited to get a chance to meet and hear present and he didn’t disappoint.  He loves his work and he loves his family and he loves sharing the tools of our trade with people.  I watched both of his presentations and got to learn how bigger shops can standardize large deployments and solutions, with clever safeguards in place.  He took time to talk with me about my presentation, sharing with me some additional xml tools I could use and giving me praise which made me all giggly.

Andy Warren. (Blog | Linkedin | Twitter) Co-founder of SQL Saturday, SQLServerCentral and a Director at Large of PASS.  Andy’s presentation on developing yourself professionally was well received and given that bio, I think he knows of what he speaks.  He encouraged us to think of ourselves as a product or business, and to develop a business plan accordingly.  I concur. 

Jessica Moss.  (Blog | Linkedin | Twitter) So glad I got to meet Jessica and see her presentation on Reporting Services.  Jessica is a great speaker—people were engaged and learning and she has a great time up there, which I love to see.

My presentation went very well.  I had 20 folks and I felt like most of my group was really enjoying it.  Several folks said they learned at least one or two very useful things.  I realized when it was over that I didn’t get very many questions, but people were definitely engaged, so I hope I wasn’t bulldozing them.  Got an average score of 4.6 (out of 5)—the large majority of people liked the presentation a lot. 

My advice to first time speakers:  (Robert Cain also gave me this advice):  practice with 1024×768 resolution.  You would be amazed how much the low resolution will throw you off, and how frequently you encounter it.  Also, practice your presentation with people in an audience—I inflict it on my coworkers.  I did practice runs on Tuesday and Thursday.  Tuesday really kind of sucked (thanks dear coworkers for suffering through it), on Thursday I felt much better and by Saturday I was pretty happy.

Can you trust your foreign key? (Or, feature #821 I didn’t already know about SQL Server)

Hey friends! Long time, no see. I know, you’re wondering where I’ve been. Lately, I’ve had the luxury of taking some time to work on Microsoft certifications. I just got through 70-448, Microsoft SQL Server 2008, Business Intelligence Development and Maintenance. Now, I’m working on 70-433, Microsoft SQL Server 2008, Database Development. I’ll admit… I thought the Database Development exam would be a no-brainer. Heck, I’ve been doing this for years. Much to my chagrin, I’ve learned a few things I should have already known. (Isn’t that how it always ends up?)

Anyway, I was working my way through the Self-Paced Training Kit, and stumbled across one thing that I hadn’t known before and am so excited about that I want to share it with all 12 of our readers. Here we go…

First, because good bloggers give credit where credit is due, major props to the team that wrote the training kit for exam 70-433. They include: Tobias Thernstrom, Ann Weber, Mike Hotek, and GrandMasters. It’s a well-written book, and they obviously snuck some things in there that were more about good design and development and less about answering test questions. Kudos, gentlemen and lady! I’m basically re-writing something that you’ve already covered in your book, but I think it’s really, really cool and want everyone to see it regardless of whether they’ve bought the book or not. Given that, buy the book, dear readers. Even if you’re not studying for the exam. I promise you’ll learn something new.

Okay, to the point… Do me a favor, get on your local database and run this query. Go ahead, I’ll wait for you.

SELECT name, is_not_trusted

FROM sys.foreign_keys;

Good to have you back. I missed you. I stared pensively into the horizon while I awaited your return. I even wrote a poem and a folk song. Sorry, I digress… one too many rom-coms lately. Did you see any “1” values in the is_not_trusted column? Did you even know that foreign keys could be trustworthy? Nope, neither did I. What does it mean? It means that your foreign key hasn’t been verified by the system. How does this happen? Well, remember that optional clause called WITH CHECK | NOCHECK when you create a foreign key constraint? Yup. That did it.

So why does this matter? Well, it actually has an effect on your query execution plan in some cases. You know, that Query Optimizer is pretty darn smart. Let’s look at an example from the trusty old AdventureWorks database. I’m using AdventureWorks2008R2, but it should work with the older AdventureWorks databases. The scenario is this: I want to know if I have any sales orders that have invalid customers. CustomerID is a NOT NULL column in Sales.SalesOrderHeader. I know that if I count all the rows in Sales.SalesOrderHeader and the number of rows returned in the query we’re about to run, I should get the same number of rows back each time. But, sometimes rogue values slip in, especially when the database design has been refined over time. This could happen for any number of reasons: constraints that were added after the fact, legacy data, disabled constraints, etc.

Run this query, but turn on Include Actual Execution Plan (Ctrl+M) before you do.

SELECT soh.*

FROM Sales.SalesOrderHeader soh

WHERE EXISTS (SELECT * FROM Sales.Customer c WHERE soh.CustomerID = c.CustomerID);

Note that I’m using WHERE EXISTS rather than an IN clause with a subquery. This is because in this business scenario, I just want a boolean result (true or false), and I want it to run FAST.

Check out the execution plan. Note that the Customer table was never accessed. Why? Because that foreign key is trusted! Since it was verified on creation, we know that no rogue CustomerID’s snuck into the Sales.SalesOrderHeader table. It doesn’t even need to look at it.

Now, let’s muck with the foreign key and make it un-trusted. We’re disabling the foreign key with this statement. Books Online has a good article about what this means.

ALTER TABLE Sales.SalesOrderHeader

NOCHECK CONSTRAINT FK_SalesOrderHeader_Customer_CustomerID;

Verify that the foreign key is disabled by checking sys.foreign_keys again:

SELECT name, is_disabled, is_not_trusted

FROM sys.foreign_keys

WHERE name = ‘FK_SalesOrderHeader_Customer_CustomerID’;

If we run our query again, we see a completely different execution plan. Now, the query optimizer has to go look at the Sales.Customer table to get us an answer:

SELECT soh.*

FROM Sales.SalesOrderHeader soh

WHERE EXISTS (SELECT * FROM Sales.Customer c WHERE soh.CustomerID = c.CustomerID);

The execution plan had to change because SQL Server cannot guarantee that a CustomerID wasn’t entered while the foreign key constraint was disabled.

Here’s where it gets interesting. Enable the foreign key by executing the following:

ALTER TABLE Sales.SalesOrderHeader

CHECK CONSTRAINT FK_SalesOrderHeader_Customer_CustomerID;

Check out your sys.foreign_keys table again.

SELECT name, is_disabled, is_not_trusted

FROM sys.foreign_keys

WHERE name = ‘FK_SalesOrderHeader_Customer_CustomerID’;

What? It’s enabled, but it’s still not trusted! If we execute our query, we’re still going to get the execution plan that looks at Sales.Customer. Why? Well, that CHECK keyword up there just said to enable the foreign key, it didn’t say verify it. We have to issue this statement (Of course, if an invalid CustomerID snuck in while your FK was disabled, this ALTER is going to fail):

ALTER TABLE Sales.SalesOrderHeader

WITH CHECK —> this clause will make your FK trustworthy again

CHECK CONSTRAINT FK_SalesOrderHeader_Customer_CustomerID;

If we run our query now, we’ll get the sleeker, more efficient plan, because now our foreign key is enabled and trusted.

SELECT soh.*

FROM Sales.SalesOrderHeader soh

WHERE EXISTS (SELECT * FROM Sales.Customer c WHERE soh.CustomerID = c.CustomerID);

Cool, huh? The point is, this one teensy-tiny flag in the foreign key metadata makes a huge difference in how the query optimizer handles your query. It might not be much, but why not make sure that you can get as many trusted foreign keys as possible? You might just end up looking like a rock star for improving performance without having to modify any actual queries.

Now if I could just figure out put to get a is_not_trusted flag on people…

Why you wanna treat me this way? (Or Extra Special Super Secret Security in SS 2008 Reporting Services)

I agree with Audrey.  I too, heart Microsoft.  For me Microsoft’s SQL Server has been the cornerstone of a very rewarding career.  But in every relationship there are moments of great disappointment.  I will now share one of those moments in this thing me and SQL Server got going. 

I have a lot of SSIS experience, a bit of SSAS and practically no SSRS experience.    I have written many reports in “Access”, and have “heard” that of the three tiers in the BIDS stack SSRS is the easiest, but I wanted more than theory and hearsay.  Since no “real” projects came my way, I decided to do the genuine geek thing and just play with it myself.  The simple plan:  I would set up a reporting server on my laptop, a lovely ASUS Notebook U80V loaded with Windows Vista Home Edition.

I got started.  I did an install of SSRS. It’s just a series of clicks through the SQL Server Install wizard and I would be on my way to deploying reports on my very own server.  I took all the defaults.  Everything I looked up online said that would be ok to do.  Once I had my green success icon on the install I knew what to next (now that I had looked up what to do next).  I went to internet explorer, typed in http://myservername/reports to open up the Report Manager.  This is where I would be greeted with a GUI which would gracefully guide me through deploying some reports.  Everything in windows is intuitive, right?  Now that I knew how to open the report manager  , I would be able to figure out the rest easily.  (I’m pretty good with these computer thingies).  (Stop laughing at me). 

Here is what I was greeted with:

Awesome!  No errors! 

Now I just upload an RDL (report) file from the Contents Tab using the Upload File button and page.   …….   Um …… just , wait, it’s got to be here somewhere…… let me check the links —-Home.  (already home).  Um, My subscriptions ——(don’t have any).  Help——  (too much help). 

Well that’s weird.  I can’t “do” anything.  This doesn’t look like all those pictures of “functional” pages of Report Manager I’ve seen online:

Looking at it, it seems like a permissions problem but ….it can’t be a permission problem because this is my personal, stand alone laptop.  There is only me as the user and I’m an administrator.  The server is installed under my user (an administrator btw).  Every user, every service, every application I touch should have absolute God rights on this machine.  Can’t be permissions.

I took a deep breath.  I knew what I was in for now.  Dedicating a portion of my life to fishing online for the (probably annoying) solution.  From topics on MSDN/BOL  I started with “report manager” .   I was optimistic that the answer was close.  And it was about halfway down the page I saw this note:

If you are using Windows Vista or Windows Server 2008, you must configure the report server for local administration before you can use Report Manager to manage a local report server instance. For instructions on how to configure the server, see How to: Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008.


Hmm.  I was indeed using Windows Vista.  But wasn’t I already configured for local administration?   After all, this is my personal, stand alone laptop.  There is only me as the user and I’m an administrator.  The server is installed under my user (an administrator btw).  Every user, every service, every application I touch should have absolute God rights on this machine.  Can’t be permissions.

What the heck, I’m only ten minutes in at this point.  I followed the link.  

There was the answer.  Right at the top. 


Deploying Reporting Services on Windows Vista and Windows Server 2008 requires additional configuration steps if you want to administer a report server instance locally. Both Windows Vista and Windows Server 2008 limit the overuse of elevated permissions by removing administrator permissions when accessing applications. Because the operating system removes permissions, members of the local Administrators group run most applications as if they using the Standard User account.

While this practice improves the overall security of your system, it prevents you from using the predefined, built-in role assignments that Reporting Services creates for local administrators. However, with additional configuration on your part, you can effectively manage report server content and operations using standard user permissions if you do the following:


Folks,  I have to say that the verbiage above is so unbelievable to me as to not need any embellishment for comic effect.  It’s like the SNL skit when Tina Fey played Sarah Palin in the Katie Couric interview and all she did was read from the transcript of the actual interview.

My personal favorite tidbits from the above:

  • “limit the overuse of elevated permissions”  you mean like permissions to execute all designed functionality?  Without a warning, flag, or tool tip in sight?
  • “requires additional configuration steps IF you want to administer report server instance locally”.  Maybe just assume that if a user installs it then that user may want to use it, “locally” being a popular choice for “how”.

Anyway I got my server working, using the below instructions.  I post them here now from MSDN/BOL for the sake of the children everywhere:


  To configure local report server administration on Windows Vista and Windows Server 2008

  1. Open a browser window with Run as administrator permissions. From the Start menu, click All Programs, right-click Internet Explorer, and select Run as administrator.
  2. Click Allow to continue.
  3. In the URL address, enter the Report Manager URL. For instructions, see How to: Start Report Manager.
  4. Click Tools.
  5. Click Internet Options.
  6. Click Security.
  7. Click Trusted Sites.
  8. Click Sites.
  9. Add http://<your-server-name>.
  10. Clear the check box Require server certification (https:) for all sites in this zone if you are not using HTTPS for the default site.
  11. Click Add.
  12.  Click OK.
  13.  In Report Manager, on the Home page, click Properties.
  14. Click New Role Assignment.
  15. Type your Windows user account in this format: <domain>\<user>.
  16. Select Content Manager.
  17. Click OK.
  18.  Click Site Settings in the upper corner of the Home page.
  19. Click Configure Site-wide security.
  20. Click New Role Assignment.
  21. Type your Windows user account in this format: <domain>\<user>
  22. Select System Administrator.
  23. Click OK.
  24. Close Report Manager.
  25. Re-open Report Manager in Internet Explorer, without using Run as administrator.


So, even though the server was my personal, stand alone laptop, even though there was only me as the user and I was an administrator, even though the server was installed under my user (an administrator btw) and even though every user, every service, every application I touched should have had absolute God rights on the machine, it was the Permissions.

But I’m over it now.  Things are good again between me and SQL Server.  If anything, I think it’s made our relationship stronger. 😉

SSIS 2008 – Annotations are Word-wrap-less (is that a word?)

I just have to throw this rant out there… I’m trying to be a good little developer and put a comment about a weird update we’re making based on a new business rule.  I add an Execute SQL task, but my pretty update statement in there, and connect all the lines back up.  Go to put in an annotation, (right-click on design surface, click “Add Annotation”), and start typing away.  In case you don’t know me well, I tend to be a little, um, wordy at times.  I see the text box getting longer and longer, and I’m assuming that when I click off, it’ll word wrap for me.  You know… word wrap.  Even Notepad has word wrap!

Lo and behold, no wrapping.  Just one loooong line of text.  I Google, and here’s what MSDN has to say:  “The text block does not provide word wrap, but instead scrolls the text as the text exceeds the width of the text block. Press Ctrl-Enter to add a new line to the text block.”  Ladies and gentlemen, welcome to SSIS 2008, where we can handle complex, enterprise-level ETL processes, but we can’t wrap some text in a little box.  Forgive me, Microsoft.  I heart you, even if you did just waste 15 minutes of my day.  Even sadder, I spent 5 minutes repeatedly hitting “Enter”, trying to figure out what was wrong with it before I decided to go to the Google. 

Ctrl-Enter, my friends.