You are currently browsing articles tagged TSQL2sday.

TSQL-Tuesday#050: The automation storyAutomation, whoah, what a subject for our monthly T-SQL Tuesday party!  This month’s event is hosted by SqlChow.  Head over to his post for more details on this month’s topic and to find out what T-SQL Tuesday party actually is, if needed:

T-SQL Tuesday #050: Automation, how much of it is the same?

Automation, what would the world be without it?  I have no idea but it would definitely not be the same!  And what would my job be without it?  Again I don’t know but it would definitely not be the same!

As a Business Intelligence consultant automation is a big part of the task list!  How else would we be able to build a data warehouse (DWH), if it wasn’t through automation?  Our ETL processes are all about automation: retrieving data (Extract), converting and remodeling it into something useable (Transform) and pushing it into the DWH (Load).  Without automation we would never be able to go home at 5PM and still couldn’t get it all done!

And it doesn’t end there, even for report delivery we use automation now and then: reports can get exported to a shared folder or emailed to a bunch of people through subscriptions.  You just set it up once and user-friendly output gets generated as scheduled!

Before I switched to the BI world – regular readers know that I used to be a software developer – automation was a big part of my job too: automating the import of flat files into an accounting application, ensuring money transactions got approved (or rejected) automatically, …  Automation is a big part of a software developer’s life too!

So as you can see it’s not only DBAs who would/could benefit from automation, practically everyone active in our IT domain can use it here and there!

Let’s see if I can share something more about automation, something which you might find useful in your daily job.  Ow yeah, do you see that Must-Have Downloads title in the right bar on this site, right underneath my tag cloud?  It mentions two of my favorite tools: SSMS Tools Pack and BIDS Helper!

SSMS Tools Pack

This great SSMS add-on, developed by Mladen Prajdic, should contain something useful for everyone using SQL Server.  My most favorite functionality is the Script Grid Results item when right-clicking the results of a query:

Script Grid Results

Using that functionality you can generate a script that inserts data into a table, real handy if you quickly want to move some data over into another table or obviously if you just want to script out your data!  Here’s your typing automated for you!

BIDS Helper

This is a must-have add-on for Visual Studio (BIDS) that adds a lot of additional functionality for SSIS and SSAS (both multidimensional and tabular) and even a bit for SSRS projects.

My favorite of them all: Smart Diff!  Using the regular Compare function on an SSIS package or SSRS report doesn’t produce the most readable result.  Since these are XML files they are a bit hard to read to humans.  And they contain item positioning info which obfuscates it all even more.  Smart Diff is smart enough to only highlight the parts in which you as developer are actually interested.  Have a look at the following documentation page and the screenshots will immediately clarify what I mean: Smart Diff

Other really useful additions are:

Sort Project Files: how annoying if your SSIS packages are not displayed alphabetically, right?  With this BIDS Helper functionality you can just right-click the SSIS Packages node and choose Sort by name!

Expression and Configuration Highlighter (SSIS): adds colored triangles on connection manager icons to indicate that they are configured through package configuration.  Same for tasks and connection manager which have an expression on them in a property somewhere.  Very useful while debugging packages you’re not familiar with!

And there’s much more, have a look at the main documentation page for the full list.

I hope the above info helps you in getting a couple of your daily tasks improved, or perhaps even some of your daily annoyances removed.  In my case it surely did!

Have fun!




T-SQL Tuesday #46 Rube Goldberg MachineHere’s my participation in this month’s T-SQL Tuesday.  T-what you say?  T-SQL Tuesday, the monthly blog party started by Adam Machanic!  Head over here to this month’s invitation by Rick Krueger: T-SQL Tuesday #46 Rube Goldberg Machine for the longer story.

So, a story about SQL Server-related contraptions, let’s see…  Yeah, I can do that!

Some years ago I was asked to help out in finding a solution to a certain problem, the life of a consultant, right?  The problem was performance related.  No, this is not a story about a database without any indexes.  Instead, it involves a certain .NET web application.  The end user would fill out a page with some metrics and the application would then perform certain calculations.  And that’s where we get to the issue: the calculations would take way too many minutes (yes, not seconds, minutes!) to finish.  That was the challenge.

The first step was to examine the .NET code that was performing those calculations.  But there were so many lines of code, complicated code, that the final conclusion was: hmm, rewriting this contraption is not an option right now…

After some discussion with the project lead it became clear that the end user would not necessarily need to wait for the calculation to finish.  It didn’t need to get calculated online.  Aha!  So if we would be able to schedule these calculations and set up a batch process then that means problem solved, right?  Now, how would we schedule this?  How about our favorite scheduler, the SQL Server Agent?  Yes, that’s it!

So I ended up building an assembly that was to be called from SQL Server: a CLR-enabled assembly!  The web application used web services so the new assembly would also call a couple of (reworked) "calculate" methods.  This meant we had to install the assembly with increased permissions: EXTERNAL_ACCESS to be precise.

Furthermore I didn’t want to hardcode the path to the web service.  There were different environments, you know, DEV, UAT, PRD, so it had to be configurable.  Now, as the assembly is getting called from SQL Server, I needed to provide the configuration through sqlservr.exe.config which gets loaded when SQL Server starts.

As you can probably guess, this little project required a lot of investigation, trial and error, and so on.  But I learned a lot from it and that’s what I like!  And it also made me write the following article: Calling A Web Service From SQL Server 2005.

This article turned out to be quite popular, even today, almost five years later, it’s still in my top 10 most viewed pages of last month!

Most popular pages over one month

Was this a clean solution?  Well no, otherwise I wouldn’t have selected it for this month’s T-SQL Tuesday party, right?

Did it solve the issue?  It sure did!

‘till next time and in the meantime: have fun!




Look at that, it’s T-SQL Tuesday #42, how can I miss that?!

This month’s party is hosted by Wendy Pastrick and the topic is about change in the work life, possibly related to technology.  Well yeah, in my career of approximately fifteen years now I can write a couple of words on that subject.  Here’s the story of my career.


For those who don’t know me yet I have something to confess: I used to be a software developer!  And I’m not ashamed to admit this.  Perhaps not that surprising, fifteen years ago you didn’t finish your studies to conclude: "And now I’m going to be a Business Intelligence consultant!".  Oh no, not at all.

During my studies, out of all possibilities, I already knew what I liked most: to develop software.  And I also quickly learned that my favorite language would not be COBOL, too outdated.  It wouldn’t be Visual Basic either, feels like you’re writing a book.  I prefer the shorter C/C++ syntax style.  I also knew that I didn’t want a consultancy position.  I considered a consultant to be an expert, and how could I be an expert after just having finished my studies??

Change 1.1

The first three years of my career I was an in-house C++ programmer.  Then I got involved with a software package that was written in VB6.  They were lacking manpower to implement new functionality and I was asked to help out.  So I did some programming in VB6 because there was no other option.

Then the first version of .NET arrived and guess what I did?  Part of the package’s functionality was the import of data out of flat files.  My job was to automate this process.  SSIS would have been perfect for the job but that didn’t exist yet.  I did use DTS for a couple of things, but not here.  The existing code consisted of about 15,000 lines of VB6 code which means redesigning was not an option anyway.  So I wrote a Windows service in C# that called the VB6 code!  Process automated!

Change 1.2

I’d also been using C# to write a couple of tools to help me in my day-to-day job.  Then the opportunity arose to be part of a new project which would be written completely in C#.  I was happy to be part of this!  Compared with VB6 and VC++6, the new Visual Studio for .NET was really a joy to work with!

Change 1.3

After a good year or so, upper management decided to go on the free tour: instead of .NET we were required to use Java for any new development projects.  Sure, why not give that a try then?!  One of the subprojects on which I worked was an activity monitoring tool for our server application.  I ended up developing some stored procedures to extract statistics out of logging tables.  At that time I wasn’t familiar with the ETL acronym yet, but that’s what I was doing.  Nowadays I would use SSIS for such a task.  There was also a reporting part to the project so I coded a website, in Java, to display those numbers, with drill through functionality and all that fancy stuff.  Nowadays I would definitely use SSRS!

Several months and lots of code later I realized that I didn’t enjoy Java (and all involved tools, libraries, …) as much as I enjoyed C#.  Sure, I could get stuff done.  But it wasn’t always as straightforward as I would have hoped, and online info was not as good – in my opinion.

After ten years of being an in-house developer at three different companies, I decided the time had come for a bigger change!  What I haven’t mentioned so far is that practically all projects in which I was involved used SQL Server as database engine.  And I always enjoyed playing around with that.

Change 2.0

In my new job I would no longer be an in-house employee and my main activity wouldn’t be writing code: I became a SQL Server Business Intelligence Consultant!

My employer, Ordina, gave me some time to cope with the change.  I was allowed to spend some weeks studying books and even going for a week of training (SSAS).

I was also encouraged to start blogging.  Initially it was a challenge to find topics to blog about.  But only initially, nowadays I’ve got too many topics and not enough time.  I discovered it was actually interesting to write about things I’d encountered for real.

Here’s such an example.  In one of my shorter project interventions, I ended up calling a web service through SQL CLR.  This was not an easy task.  Without my developer background I would probably never have succeeded here!  When I turned this into an article on my blog it became my first real hit!  The article, Calling a Web Service From SQL Server 2005, was posted Nov 11, 2008 and has gotten over 26,000 page views to date.  Even today it is still one of the more popular pages I’m hosting.

Besides teaching others through my blog I also discovered that forums can be a really interesting way to improve skills.  So in August 2008 I created a free account at Experts Exchange.  I also found out that helping out on forums combined with blogging is a real win-win situation: it helps me to find topics to write about and I can help people out by referring to an already-written article!

In February 2012 I was delighted to read that EE had found my forum activity worth an extra credit so I was given the MVE – Most Valuable Expert – award! Since then I have managed to reach the number one position in the SSRS zone with over one million points in total and it looks like I’ll be staying there for some time.

Lately I have started presenting.  So far I have found that to be a stressing yet rewarding experience.  My next presentation will be at the Community Day where both I and my colleague Koen Verbeeck will each talk for about half an hour about SSRS visualizations.  The target audience are mainly developers, that’s going to be interesting!  And it will be my first time on a stage in a cinema room, fearing the spotlights already!

Ow, and registration is open so see you there?  Come over to say hello if you are planning to attend the conference.  We are scheduled in the first slot so once that’s over I’ll be relieved of my stress!


Did a change in technology influence my career path?  Ow yeah, I do believe it did!  SQL Server was not the only tech that influenced it but I do consider it the most significant one.  And I’m glad it did too, keeps things challenging! 

Now I’m off to install Oracle.  So long, and thanks for all the fish!

PS: one of my statements in this post is a lie.



Tags: ,

SqlTuesday T SQL Tuesday #37 – Invite to Join me in a Month of Joins

When I saw this month’s T-SQL Tuesday’s topic, hosted by Sebastian Meine, my initial thoughts were indeed:

What on earth can I still write about JOIN if Sebastian is going to write 31 posts on them?

So then I thought, let’s not write about the T-SQL JOIN, but about the Join() function in Reporting Services!

I hope you enjoy this little read as we delve into a couple of the SSRS functions!

Joining Some Stuff

You may or may not already know, but the functions in Reporting Services are actually using VB.NET functions.  These functions are located in the Microsoft.VisualBasic namespace.

And the Join() is actually a method of the Strings class.

So what does the definition of the Join() look like?

public static string Join(
    Object[] SourceArray,
    string Delimiter

(Yes, I prefer C# style.)

It takes two parameters: an array of objects which contains the strings to be joined and a string that will be used as delimiter for the output.

So where in SSRS do we have an object array?  Well, in the case of a multi-valued parameter!  I’ve actually already mentioned this briefly in my article on combining multiple-value parameters with stored procedures about three years ago.

As per the definition, the delimiter is a string, not just one character.  Which means the following would be an interesting expression to display the selected values on the report:

=Join(Parameters!SomeMultivalueParam.Value, ", ")

That’s right, we can use more than one character as delimiter.  Assuming the report has got a multi-value parameter called SomeMultivalueParam, this could result in the following:

Joining several values from a multi-value parameter together in a comma-separated string

Even better, because our parameter is an array of strings, we can also get a count out of it using the following expression:

    & " items selected: "
    & Join(Parameters!SomeMultivalueParam.Value, ", ")

That generates the following output:

Displaying number of items selected=

Unjoining Some Stuff?

Okay, that’s enough about Join, what if we need the opposite?  To just get a particular selected value out of a multi-valued parameter, you can simply use the following array syntax:


Beware that the array indexes are zero-based, so the above expression gives us the second selected value from the object array.  In our example it would thus return “second val”.

But what if we’ve got a character-separated list of values coming out of the database?  In that case we don’t have an object array, right?  But that doesn’t stop us from creating one!

Which brings us to the opposite of the Join(): the Split()!

Here’s what the Split function looks like:

public static string[] Split(
    string Expression,
    string Delimiter,
    int Limit,
    CompareMethod Compare

Now let’s look at its usage by trying the following expression:

=Split("val1,val2,val3", ",").GetValue(1)

What do you think it returns?

That’s right: “val2”!

Would this also work for longer delimiters?  Well, the MSDN page contains some contradictory information in that perspective.  It mentions the following as description for the Delimiter parameter:

Type: System.String

Optional. Any single character used to identify substring limits. If Delimiter is omitted, the space character (” “) is assumed to be the delimiter.

Hang on, single character??  But some of the examples on that same page are using longer delimiters!

Hmm, let’s try that out!  What do you think the following returns?

=Split("val1,;val2,;val3", ",;").GetValue(1)

Yep, you guessed right, once more “val2”!

BTW: the following syntax works as well but the SSRS editor complains about it so better not use it:

=Split("val1,;val2,;val3", ",;")(1)

To be really sure that we’re actually looking at the right definition, let’s do one more test.

Here’s what I did.  I opened Visual Studio 2010 and created a new C# Windows Forms Application project.  Then I added a reference to the Microsoft.VisualBasic assembly.

Yes, you can do that!  Just right-click the References node in the Solution Explorer and select Add Reference…

Adding an assembly reference to the C# project

Switch to the .NET tab and select the Microsoft.VisualBasic component:

Adding the Microsoft.VisualBasic assembly to the C# project

To continue, I double-clicked the Windows form’s grey background to get to the code behind the form and added the following using:

using Microsoft.VisualBasic;

And then I typed the following code in the Form1_Load method:

MessageBox.Show(Strings.Split("val1,;val2,;val3", ",;").GetValue(1).ToString());

To conclude I hit F5!

What do you think that returned?


Output of the Split function using a multi-character delimiter

So the Split function does actually support a multi-character delimiter!

Have fun!



Tags: , , ,

First Timer at PASS Summit 2012Even though I’m not new in the SQL Server world, last week I had the pleasure of being a First Timer at the PASS Summit in Seattle.  Many thanks to my employer Ordina for making this possible!

And not only was I a First Timer at the conference, at the same time it was my first trip to the US!

I believe I had prepared myself quite well so that I was ready to take maximum profit from all that was to be experienced in Seattle.  I attended Denny’s interesting First Timers webcast and I read many of the First Timers blog posts available through the PASS Summit site.

And yet, still some things took a different direction once I was actually experiencing all the great stuff at the conference.

Here’s a write-up of some of my encounters over there.  Not being an American I believe my post will contain some info which hasn’t been written down yet in the posts mentioned above.  On the other hand, I’d like to think it also contains good info for all first timers in the years to come, American or not.

Experiencing The PASS Summit

The Jetlag Issue

Never having been to America, I thought the jetlag would be okayish.  Well, it wasn’t!  In my case I arrived Monday evening and I was attending a preconference session the day after.  THIS IS NOT A GOOD IDEA!

Being at the conference means long days, really long days.  Breakfast starts at 7AM, the sessions start at 8AM and continue until 6PM.  And then the evening activities begin, you surely don’t want to miss out on those.

So, if you need to cope with a significant time difference and a long flight – in my case the trip took about 15 hours and there’s a 9-hour time difference with Belgium – leave at least one day earlier than I did so that you’ve got two nights in between arrival time and conference start.

Doing that also gives you a good chance to get to know the city a bit before it gets dark, and do some shopping if that’s what you’re after.  Don’t forget the gifts for the kids and wife/husband who got left alone in your home country.

In my case I chose to skip the two keynotes to get at least some jetlag worked away.  My reasoning was it’s better to skip the shows so that I could at least concentrate a bit more in the learning part of the conference.  Okay, some new stuff was announced but you can, in fact you WILL, always find out about that later.

And I was successful in getting over my jetlag, by the time it was Friday… And on Friday, I kissed Seattle goodbye to get started on yet another, even worse, jetlag! :/

Getting Money

If you don’t have any American dollars yet, don’t worry about exchanging money in your home country.  You can do that using Maestro.  However, give your bank a ring before getting on the plane because Maestro needs to get activated specifically for the US.  By default it is not activated because apparently 75% of fraud transactions is sourcing from the US.

At the same time, check with your bank if your VISA or MasterCard is activated for the US.  You may also want to increase it’s limit, depending on what that limit is currently set to and what you’re expecting to pay for with it.

Once you’ve arrived in the airport, use your Maestro card to get some dollars out of one of the available ATMs.  Also, insert your credit card and enter your pin code.  You don’t need to do anything with it, but entering it into the machine makes sure it’s registered as “being located in the US”.  At least, that’s what I was told to do and also what I eventually did.  I’m not sure if this step is absolutely necessary, but better be safe than don’t have any access to your credit card, right?

Using Money

I’m sure we all know how to use money, right?  So this chapter is mainly about the tipping, which is really different in the United States compared to Belgium (and several other European countries).

Here’s what I was told.  When going out to eat or drink something, you should always tip.  How much depends on the service.  The regular tipping amount is 18% and if you really liked it you should tip 20%.  If it wasn’t any good, you should still tip 15%.  That may sound weird to Europeans but apparently that’s how it works.  Well, unless they’ve been rude without cause, I guess in that case it’s okay to walk away without tipping.  Luckily I’ve only seen situations were I should tip at least 18%.

The last day I left my luggage with the bell boys.  A general tipping rule here is one dollar per bag, though some people give 5 dollars in total.

I’m not sure if the above applies to the whole of the US.  Next year the conference will be on the East Coast, Charlotte more precisely.  So if someone could let me know if tipping in Charlotte uses the same percentages as in Seattle, that would be great!

Meeting People

Thanks to my activity in the SQL community, which has been going on for several years now, I knew some people who were going to be present at the conference as well.  The Who’s attending list surely helped a lot here!

To make sure I didn’t forget anyone, I had made a list of names.  However, if you’re thinking that you’ll meet them by just running into them, forget about it!

The convention center is huge and there are more than 4000 people through which you need to search.  And as we all know, searching without an index can take a long time.  If you’re lucky then you run into them.  If you’re not, well, you don’t.  So if you really want to ensure that you’ll be able to meet someone, get their phone number!  This way you can text them to arrange a meeting location and time.  I say “text” instead of “call” here on purpose: calling abroad using my Belgian provider is really not an option unless I want to go bankrupt quickly.

If you didn’t get a phone number, get at least their email address.  Get linked with them on LinkedIn and start following them on Twitter, prior to the conference.  Also, let them know in advance that you’ll be there as well and that you’re looking forward to meeting them!  Ow, and find out if you’re staying in the same hotel.  That can surely help to meet them during the evening hours.

Having done all the above will increase your chances of meeting your friends significantly.

Was I successful in this?  Well, partly.  I met some, I didn’t meet others.  Better luck next time!  I believe the more you’ve been there, the easier it will become.  The reason for that is that your friends have connections as well.  So the more connections you have, the higher the chances become that your connections know the ones you haven’t met yet.  In that case, they can easily introduce you!

So far we’ve only mentioned peers whom we’ve already been in contact with somehow before.  How about the new ones?

To make sure you remember who you’ve met, you should ensure that you have a stack of business cards with you.  That’s the fastest way to give someone your contact details.  Don’t try memorizing names, unless you’re really trained for that.  You’ll meet so many people in such a short amount of time that it will become really challenging.

If you run out of cards, note down their name on your smartphone.  Or add them on Twitter and LinkedIn.

Be Smart, Get A Smartphone!

No, I don’t work for a hardware reseller.  But really, if you don’t have a smartphone yet, now’s your time to get one!  If the previous paragraph didn’t make that clear yet, I’ll explain why you need one right now.

A lot of SQL Server professionals are active on Twitter.  And there will be free wireless internet at the convention center.  This results in a lot of Twitter activity during the whole conference period.  You’ll be able to gain a lot of additional info which you wouldn’t have without a smartphone and access to Twitter.

Important hash tags this year were #sqlpass and #summit12.  Another one is #sqlfamily and you may also want to look into #sqlhelp, as explained in this nice article by Sarah Strate.

In fact, Twitter is used so widely that now and then you’ll even see your friends asking where you are, through Twitter!  Don’t forget to install the Twitter app too, much easier than using a browser!

To give you an idea, my Twitter Followers count has increased with at least 25 in one week time!

Finding A Room

Don’t worry too much about this.  A convention center is used to having to deal with lots of people unfamiliar with its layout.  There are signs everywhere.  And if you’re in a rush, every entrance door is guarded by people who know their way around there so just ask them in what direction you should walk.

Also, if you followed my previous tip, you should have a smartphone.  This year, PASS used an app called Guidebook.  Get it installed and you have the maps (and much more) in your pocket, real easy to reach!

Getting Into A Room

Do worry about this!

I missed out on two good sessions (well, three actually but one of them is not a regular session) because the room was stacked with people sitting on the floor even before it started!  So if there’s a particular session that you really don’t want to miss, go there early!

There’s only 15 minutes in between the sessions and if you need to switch floors, take into account that 15 minutes is really, really short.  Don’t start a conversation with people you meet on your way, tell them to meet later (lunch, evening) because you don’t want to miss the session.  Don’t worry too much about hurting their feelings, they’ll probably have the same on their mind.  To avoid any misunderstanding, just tell them what you’re up to.  Who knows, if you’re lucky they’re on their way to the same room!

If really needed to ensure you can get into the room, leave the previous session a little early.  But only if needed and the speaker is going over the end time.

How do you know to which sessions you should go super early?  That’s a bit difficult to predict, but certainly don’t look at room size.  The two I missed were located in the larger rooms.  Or maybe do look at room size: if the speaker is in a larger room, that may indicate it’s a popular one!  If you know that the speaker has a popular blog, or has written popular blog: another good indicator!  And if the speaker is a regular I’m sure that’s a good indicator as well!

Then there are the special sessions, such as the Lightning Talks and the BI Power Hour.  I didn’t have a problem with the Lightning Session that I attended, but the Power Hour was a totally different story.  Even though it was the last session on Thursday evening, the room was packed half an hour before it even started!

Travel Bags

Ensure you’ve got some space left when you close up your bags at home.  You’ll be getting your hands on some swag and I’m sure you don’t want to leave part of that at the hotel because it won’t fit in your bags!

In my case I was travelling with a Swissgear backpack as hand luggage and a compact trolley for regular luggage.  At the conference entrance we were given a nice backpack and I didn’t want to walk around at the airport with two backpacks.  The new backpack was too large to fit in my other one so only one option remained: it had to fit in my trolley!

I was also lucky enough to get my hands on a free, signed copy of the Professional Microsoft SQL Server 2012 Integration Services book by Brian Knight and co, which didn’t help either. Smile

Long story short: I went home with a trolley zipper that wasn’t far from bursting and a heavy backpack because of the book!


T-SQL TuesdayThe conference is not only about improving your SQL-fu.  It is about much more!  Meeting people you know but have never seen before, meeting new faces and minds, and of course also seeing your friends again!  Be well prepared and you’ll certainly have your hands full at the conference.

Coincidentally, my post seems to fit right in with this weeks T-SQL Tuesday topic, so here’s my participation!  See all of them through the #tsql2sday hashtag.

Looking forward to seeing my SQLFamily again!

And as always: remember to have fun!



Tags: , , ,

© 2008-2019 BI: Beer Intelligence? All Rights Reserved