The Belgian SQL Server User Group offers a significant 35% discount for its members (even more than the early-bird discount) for any PASS European Conference 2010 registration.

Use discount code BEC15Y and enjoy your savings on the registration.

For more information check out the SQLUG website.  PASS European Conference 2010 is Europe’s premier conference for SQL Server technical education and business networking.  Meet top SQL Server experts from Europe and around the world.  Learn about best practices, effective troubleshooting, how to prevent issues, save money, and build a better SQL Server environment for your company or clients.

A while ago I already blogged about the sessions that I’m planning to see (which reminds that I should have another look at the agenda and update my list there )

Have fun!

Valentino.

If you’ve been using SSIS for a while you’ve probably noticed that the Management Studio doesn’t like to delete Integration Services folders that are not empty.  In fact, it will politely ask you if you’re sure that you want to delete the folder on which you’ve just selected the “Delete” option through the right-click menu.

So you click the Yes button.  But then it shows you the following message:

SSIS folder ‘FolderWithSubfolders’ contains packages and/or other folders. You must drop these first. (Microsoft SQL Server Native Client 10.0)

Graphically it looks like this:

And this message can be really annoying if you’ve got a main folder with, let’s say, five subfolders, and each subfolder contains about 20-30 packages.  If you want to delete this folder you first need to delete each package separately and then delete the five subfolders, and then you can finally delete the main folder.  And all that through the right-click pop-up menu because you can’t just select the object in the Object Explorer and hit the Delete button on the keyboard – it doesn’t have an action on SSIS objects…

So, I wasn’t planning on doing such a job manually and came up with the following stored procedure.

It’s probably a bit long but don’t run away just yet, I will explain what’s going on down below the code, and there are some comments in the code as well.

/*
DESCRIPTION: Deletes all folders and packages under, and including, specified folder.
WRITTEN BY:  Valentino Vranken
CREATED:     2010-02-28
VERSION:     1.0
USAGE:
  -- mind the forward slash
  EXEC dbo.SSIS_RecursiveDeleteFolder '/FolderWithSubfolders'
  -- to delete a subfolder
  EXEC dbo.SSIS_RecursiveDeleteFolder '/FolderWithSubfolders/ASubfolderWithPackages'

COPIED FROM: http://blog.hoegaerden.be

Note 1: folder names are not case-sensitive
Note 2: uses system tables and (undocumented) stored procedures located in MSDB.
Note 3: this code was written for SQL Server 2008. For 2005:
  o sysssispackagefolders -> sysdtspackagefolders90
  o sysssispackages -> sysdtspackages90
  o sp_ssis_deletefolder -> sp_dts_deletefolder
  o sp_ssis_deletepackage -> sp_dts_deletepackage
*/
CREATE PROCEDURE dbo.SSIS_RecursiveDeleteFolder
    @Folder varchar(2000)
AS
BEGIN
    set nocount on;

    declare @foldersToDelete table
    (
        folderid uniqueidentifier,
        Lvl int
    );

    declare @packagesToDelete table
    (
        PackageName sysname,
        folderid uniqueidentifier,
        Lvl int
    );

    --retrieve list of folders to be deleted
    with ChildFolders
    as
    (
        select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
            cast('' as sysname) as RootFolder,
            cast(PARENT.foldername as varchar(max)) as FullPath,
            0 as Lvl
        from msdb.dbo.sysssispackagefolders PARENT
        where PARENT.parentfolderid is null
        UNION ALL
        select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
            case ChildFolders.Lvl
                when 0 then CHILD.foldername
                else ChildFolders.RootFolder
            end as RootFolder,
            cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
                as FullPath,
            ChildFolders.Lvl + 1 as Lvl
        from msdb.dbo.sysssispackagefolders CHILD
            inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
    )
    insert into @foldersToDelete
    select F.folderid, F.Lvl
    from ChildFolders F
    where F.FullPath like @Folder + '%';

    --retrieve list of packages to be deleted
    with ChildFolders
    as
    (
        select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
            cast('' as sysname) as RootFolder,
            cast(PARENT.foldername as varchar(max)) as FullPath,
            0 as Lvl
        from msdb.dbo.sysssispackagefolders PARENT
        where PARENT.parentfolderid is null
        UNION ALL
        select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
            case ChildFolders.Lvl
                when 0 then CHILD.foldername
                else ChildFolders.RootFolder
            end as RootFolder,
            cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
                as FullPath,
            ChildFolders.Lvl + 1 as Lvl
        from msdb.dbo.sysssispackagefolders CHILD
            inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
    )
    insert into @packagesToDelete
    select P.name, F.folderid, F.Lvl
    from ChildFolders F
        inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid
    where F.FullPath like @Folder + '%';

    --use cursor to loop over objects to be deleted
    declare objectsToDelete_cursor cursor
    for
        select P.folderid, P.Lvl, P.PackageName, 'P' as ObjectType
        from @packagesToDelete P
        UNION ALL
        select F.folderid, F.Lvl, null, 'F'
        from @foldersToDelete F
        order by Lvl desc, ObjectType desc;

    open objectsToDelete_cursor;

    declare @folderid uniqueidentifier;
    declare @lvl int;
    declare @packageName sysname;
    declare @objectType char;

    fetch next from objectsToDelete_cursor
    into @folderid, @lvl, @packageName, @objectType;

    while @@FETCH_STATUS = 0
    begin
        if @objectType = 'F'
        begin
            print 'exec msdb.dbo.sp_ssis_deletefolder '
                + cast(@folderid as varchar(max));
            exec msdb.dbo.sp_ssis_deletefolder @folderid;
        end
        else
        begin
            print 'exec msdb.dbo.sp_ssis_deletepackage '
                + @packageName + ', ' + cast(@folderid as varchar(max));
            exec msdb.dbo.sp_ssis_deletepackage @packageName, @folderid;
        end

        fetch next from objectsToDelete_cursor
        into @folderid, @lvl, @packageName, @objectType;
    end;

    close objectsToDelete_cursor;
    deallocate objectsToDelete_cursor;
END

Before trying to dismantle this stored procedure, I recommend you to read my previous article on retrieving the list of packages.  That already explains half of the code, if not 75%.

Our mission is to find a way to recursively delete packages and folders contained in a specified folder.  To be able to loop over those objects in the correct order (from the deepest level up until the level of the folder specified), the SP creates two table variables: one to hold all folders under the specified folder (@foldersToDelete) and one to hold the packages under the specified folder, including all subfolders (@packagesToDelete).

Based on those two lists I create a cursor that joins these two together, taking their level and object type into consideration.  That’s important because we first need to delete the packages in the lowest level folder, followed by their containing folder, then move one level up and do the same.

We then use the cursor to loop over the packages and folders and use two undocumented system stored procedures – one for each object type- to delete the package or folder.  These system SPs are located in the MSDB.  Here’s how they are defined:

ALTER PROCEDURE [dbo].[sp_ssis_deletefolder]
  @folderid uniqueidentifier
AS

ALTER PROCEDURE [dbo].[sp_ssis_deletepackage]
  @name sysname,
  @folderid uniqueidentifier
AS

As you can see, the parameters for these procedures are not that complicated.  Both of them expect a uniqueidentifier as identification for the folder.  That’s okay, these IDs are stored in the msdb.dbo.sysssispackagefolders table and retrieved by our queries to create the list of to-be-deleted objects.

Furthermore, the sp_ssis_deletepackage SP expects the name of the package to be deleted.  Not a problem either, those names are obtained from the msdb.dbo.sysssispackages table.

Note for SQL Server 2005 users: this code was written for SQL Server 2008.  The system stored procedures and system tables exist in 2005 as well, but they have different names.  See the comment header of my SP for more details.

So, let’s give it a little test.  Following screenshot shows the setup.  What I will do is use the stored procedure to delete the FolderWithSubfolders folder.  If you’ve been paying close attention, that is the same folder which I tried to delete manually through the Management Studio’s right-click menu (see first screenshot above).

After creating the SP, I ran following command:

EXEC dbo.SSIS_RecursiveDeleteFolder '/FolderWithSubfolders'

And that gave me the following output in the Messages pane:

exec msdb.dbo.sp_ssis_deletepackage AnotherPackage, 7F38288D-4370-40A8-80E3-E92283033E4C

exec msdb.dbo.sp_ssis_deletepackage Package, 7F38288D-4370-40A8-80E3-E92283033E4C

exec msdb.dbo.sp_ssis_deletefolder 4102ED59-ED75-4D93-BBAE-0A162447BF02

exec msdb.dbo.sp_ssis_deletefolder 7F38288D-4370-40A8-80E3-E92283033E4C

exec msdb.dbo.sp_ssis_deletefolder C156B436-8C78-4BF9-99F9-5ABFAB10C405

I have deliberately put a couple of print commands in the stored procedure to dump the commands that are actually being executed.  This gives us a good idea of what’s going on.

That’s it for now folks.  Thank you for reading this, and if you found it useful or you’ve got some questions about it: post a comment!

Have fun!

Valentino.

select geometry::STPolyFromText
('POLYGON((1 1, 1.5 1.85, 2.5 1.85, 3 1, 2.5 0.15, 1.5 0.15, 1 1),
(1.5 1.5, 1.5 0.5, 1.7 0.5, 1.7 0.9, 2.3 0.9, 2.3 0.5, 2.5 0.5, 2.5 1.5, 2.3 1.5,
2.3 1.1, 1.7 1.1, 1.7 1.5, 1.5 1.5))', 0);

Running that in the Management Studio produces something like this on the Spatial results tab:

I’m using the STPolyFromText function to produce a six-sided polygon with a letter H in the center.  To get to the result, I’m passing a bunch of XY coordinates to the function.  A line gets drawn between two coordinates following each other.

The first list of coordinates (the first list of numbers enclosed in round brackets) creates the hexagon while the second list produces the letter H.  Each list of coordinates thus represents one polygon.   The starting XY coordinate must be equal to the ending coordinate to form a closed shape.  The shape is then filled with a color.  Overlapping shapes will be filled using different colors, as shown above.

Producing these coordinates was quite interesting: to be able to calculate the angled sides of the hexagon I used the following formulas.

With S being the length of one side:

Width = 2 * S

Height = S * SQRT(3)

Note: these formulas are only applicable when the hexagon is rotated as shown above.

I needed to know these distances to be able to calculate how much I should increase the X/Y coordinates to get to the next connector.

Okay, enough theory, back to the story now.

Q: “So, why are you creating a helipad platform?”

A: No, it’s not supposed to look like a helipad platform.  The shape resembles the beer coasters for the beer called Hoegaarden.  And those coasters always have a funny drawing or comment on them.  Let’s see if I can find one that’s understandable for non-native Dutch-speakers…

(And half an hour later – turns out it’s really not easy to find one without text – luckily they’ve also created some in English!)

Initially I was planning on writing a longer article about the spatial data types but hadn’t found the time to do it yet.  And then a couple of days ago I came across a challenge that made me think of this query and so now I’m publishing it in this post.

The challenge to which I’m referring was started by Itzik Ben-Gan and is called Geekiest Sig Challenge.  The point is to use the new SQL Server spatial data types to create a signature for yourself.  Well, a perfect description for my query!

I was happy to just get an H on there, getting those coordinates right without first putting it on paper is really a challenge   And on top of that, I was never any good at drawing.  I remember in secondary school we got an assignment to draw a tree.  Any tree.  So at home I looked out the window and started drawing the tree.  I ended up with the most atrocious thing I’d ever seen (well, that may be a bit exaggerated), it could have starred in a Hitchcock movie straight away, really spiky, and probably spooky when encountered in the dark and with the right background noises playing.

Anyway, what I wanted to say, some people are just talented: check out this submission by Michael J. Swart!  No further words needed.

Right, enough geeky stuff to close the week, and remember: have fun!

(Hmm, now I’ve got something to build my favicon from…)

Valentino.

When a cell (or a range of cells) is selected, you see a thicker black border around the selection and the bottom-right corner has got a small square attached.

You can drag that squared corner down to get copy-like behaviour. More precisely, when one cell is selected and you drag it down then the value of the selected cell gets copied over into the cells further down, up until the cell where you stop dragging.

Note: the same effect also works horizontally but in real-life circumstances you probably won’t need it much.

When multiple cells are selected and you drag the bottom-right corner down, Excel will apply some logic to continue the series that it possibly detects.

For instance if you have two cells selected with the values 2 and 4, the next cells will get 6, 8, and so on. Or when the first cell contains 2010/10/30 and the second 2010/10/25, the next cells will get date values going down by 5 days per cell.

If you don’t like the way the series gets applied, there’s a dropdown poping up at the bottom-right of the new selection at the moment that you stop dragging. In that list you get several different Auto Fill Options, depending on the data type of your selected cells.

I’m sure this is no news to you so far.

But do you know what happens when you just double-click that small bottom-right corner instead of dragging it?

I didn’t, until I just tried it out this past week. The reason that I tried this was because I needed a formula copied down in about 20,000 rows (hey, I’m a data guy, remember?) and I didn’t want to waste my time waiting for Excel to scroll down just to the bottom of the list while dragging the corner. So I double-clicked and there came the discovery of the week! It applied the same function as what you get when you drag the corner down, all the way down to the last row of data! Isn’t that great? From now on I think I’ll always just double-click instead of drag, much faster!

And you even get the same popup to select another Fill Option.

Have Fun!

Valentino.

Especially if you want to save time maintaining all those links in your blogrolls.

You can synchronize your blogrolls on your blog with folders in your Google Reader. Which means zero maintenance for your blogrolls: they’re automatically updated when you update your Reader subscriptions.

I actually found this solution on another site so I’ll just point you there: http://www.adashofbitters.com/2009/01/02/blogroll-google-reader-wordpress-easy/

You can see it in action in my sidebar on the right. Several of those link lists are being fed from Google Reader, such as the one called "SQL Blogs @Microsoft".

Happy blogging!

Valentino.

Update: I’m aware that Vista already contained this tool.  But I’m one of those guys who managed to stay away from Vista so to me it’s new

Let’s see if I can capture what it looks like by using the tool itself.  Hmm, I can’t…  Which is probably quite logical as it shouldn’t get in the way when you want to capture a screenshot of something.  What you want to see then is anything but the actual tool used to do it, right??

I’ll go for the old-fashioned way then: ALT + Print Screen (it captures the active window).

In that screenshot I’ve demonstrated a couple of its features.  It comes with a Pen that you can customize a bit into several colors and thickness.  You’ve also got a marker tool called Highlighter – that’s the yellow part.  And there’s an Eraser tool to remove any markings or pen editions you’ve made previously.

Another feature that’s not shown but that’s really useful is that the screenshots are automatically copied to the clipboard.  But you can switch that off through the options if you don’t like it.

I would have hoped that a couple more features had been included, such as the ability to draw arrows without needing to use a freehand tool (as shown in screenshot above), and a Rectangle/Ellipse tool for some extra markings.

Anyway, what this means as far as I am concerned is that I no longer need to install my favorite screen capture tool (I’ve used a couple over the years but the last one was Screen Hunter).  But for editing some screen captures I’ll still need to resort to my favorite image editor (paint.net).

Where is it located? Well, Start Button > Snipping Tool.  At least, that’s where I found it in my Windows 7 Enterprise edition.

What’s the hotkey? It’s CTRL + Print Screen.

How do you use the hotkey?? From the moment that you start up the application, it wants to make a screen capture.  What this means is that your mouse pointer changes into a crosshair whenever it goes outside the Snipping Tool window.  All you now need to do is hit the ESC button.  Then switch to the application that you’d like to capture, possibly opening up a menu.  With everything in place just as you want it captured, hit CTRL + Print Screen.  There’s your crosshair again.

Have fun!

Valentino.

And all it takes is checking a checkbox, just one checkbox!

That’s the solution to a problem with which I’ve been struggling yesterday.  What problem?  Let me tell you.

I’ve been installing Master Data Services (MDS) on my SQL Server 2008 R2.  This feature will not only create a database but also a website which you can use to manage MDS.  And it’s the website part that I had a problem with.  Everything installed just fine but when I tried to load the site in Internet Explorer 8 on my Windows 7 64-bit machine, I got the following error:

HTTP Error 401.2 – Unauthorized

You are not authorized to view this page due to invalid authentication headers.

Great, an authentication problem – aren’t those our favorite ones?  And like any decent developer, I didn’t waste my time reading all the text on the error page and started investigating the issue.  I had a look at the settings of the Application Pool and those of the Default Web Site.  I also tried changing the security settings in IE.  At the end I think I have tried every possible setting in IIS 7.5 (which is the version that ships with Windows 7), but I kept getting that same error.

I let some time pass (not on purpose but because we were going to visit my parents-in-law) and in the evening I decided to have another look.  This time I took the effort of reading everything mentioned in the error:

Do you see that blue link down at the bottom, which I’ve marked with a red rectangle?  That’s where I got the answer from!  Clicking it opened up the following Microsoft Support page: Error message when you try to visit a Web page that is hosted on IIS 7.0: "HTTP Error 401.2 – Unauthorized".

Sounds familiar doesn’t it?  Okay, it’s meant for IIS 7.0 but works for 7.5 as well.

I solved my problem by applying Resolution 1.  In short: IIS was running without the Windows Authentication module installed!  Apparently that is not installed by default when you activate Internet Information Services through the “Turn Windows features on or off” window.  Here’s a screenshot showing what needs to be added:

Once that was set up I was able to load the MDS application:

If you’re looking for instructions on how to install and configure Master Data Services, have a look at this article at the Master Data Services Team blog.

(Do I need to mention that this was the first-ever web application that I tried running on Windows 7? )

Have fun!

Valentino.

On my freshly-installed Windows 7 64-bit machine I have both SQL Server 2008 SP1 and SQL Server 2008 R2 (NOV CTP) running (both 64-bit as well).  And I just installed the new Contoso data warehouse and OLAP DB.

After setting up the data source connection of the Contoso_Retail OLAP database and processing it successfully, the next logical step is to try and browse the cubes, through the Management Studio.  So that’s what I tried, but unfortunately it gave me the following error:

Hmm, okay, that doesn’t look good!  “Invalid class string”, what does that mean??

So I tried the link that says “Click here to see detailed error information”, which gave me this:

I’m sorry, but where exactly are the details in this message?

Luckily a quick internet search gave me the solution (and reminded me of something which I actually knew but just didn’t think of because it’s all running in a totally new environment).

To browse cubes, SSMS is using a component from the “Microsoft Office 2003 Web Components” package, whatever that that means.

And the solution (as described here) is to:

• Open the list of installed programs (Windows Key + R > appwiz.cpl > enter)
• Locate the Office 2003 Web Components, right-click and select Change
• Follow the wizard to repair/reinstall the components

And now I am able to browse the cube without any issue!

Have fun!

Valentino.

This post is one of the latter.

In a SQL Server Integration Services project, have you ever wondered how on earth you can get files into that Miscellaneous folder?  When you right-click on the folder in the Solution Explorer, nothing happens, no pop-up menu.

Well, the answer is simple, once you know it.

All you need to do is go one level up in the tree and right-click the Project node in the Solution Explorer.  In the menu that appears, select Add > Existing Item….

In the pop-up window, navigate to any file that you’d like to add to the project.  Files of a different type than the usual SSIS files such as .dtsx and .ds are automatically added under the Miscellaneous folder.  When adding files, they will be automatically copied to the SSIS project folder, no matter where they were stored originally.  See, not that complicated… once you know it!

In the following screenshot I’ve added three different file types to the folder, just to prove that it’s working.

I like using this folder to store files that belong with that particular project.  Examples of those are configuration files, XSLT files for complex XML conversions, and also the SQL scripts that create my databases.  Each time when I make schema changes, I update the scripts.  And as I’m using TFS integration, I can rest assured that I always have a backup of my files.  (At least, assuming the TFS team is doing their job – this is usually beyond my responsibility )

Speaking about TFS, watch out if you use Business Intelligence Development Studio 2008 to connect to Team Foundation Server 2005!  There’s an interesting setting in the Options screen (through the Tools menu), located in the Source Control > Visual Studio Team Foundation Server page.  This setting is called Get latest version of file on check out.

However, there’s one caveat!  On TFS2005 it doesn’t do anything!  If you’re not aware of that, you may get an annoying surprise when you’re trying to check in your changes because you may have been working on an outdated version of your package!  And as you probably already know: merging two versions of an SSIS package is, well, what shall I call it, a challenge?

Another setting that helps you to avoid the issue described above is located under Source Control > Environment and is called Get everything when a solution or project is opened.  Activate this setting and each time when you open your project, you’ll get a popup window which allows you to Get the latest version of the files.

That leaves one more possible conflict situation.  If someone changes a package on the same day as you, the second person will need to explicitly do a Get Latest Version or he/she will be working on an outdated version.  So, communicate with your team mates so that you know if someone has gotten an assignment that collides with yours.  Of course, this last problem is just a theoretical possibility.  In teams, work is usually divided so that developers do not need to work with more than one person on the same piece of code.  The same logic applies to SSIS packages.

And remember, have fun!

Valentino.

This event will take place at 21-23 April 2010 in Neuss, Germany.

I’m really looking forward to this, I’m sure it will be three days with a lot of learning, a lot of networking and a lot of fun!

Sessions I’m hoping to see (in no particular order):

• Tackling Top Reporting Services Issues – Adam Saxton (Microsoft CSS) (Full-day pre-conf seminar)
• ETL 3 Ways – Allan Mitchell (Konesans)
• Blazing Fast Queries: When Indexes Are Not Enough  – Davide Mauri (Solid Quality Mentors)
• Index Age 3—Dawn of the Filtered – Bodo Michael Danitz (sqlserver.de)
• Building a Dashboard with PowerPivot, Reporting Services and PerformancePoint using PowerPivot in SharePoint – Kasper de Jonge (ADA ICT)
• Importance of Master Data Management – TBD (Microsoft)
• The Dirty Dozen: 12 Ways to Write Poorly Performing MDX Queries – Steve Simon (State Street Corporation)
• Adaptive BI Best Practices – Davide Mauri (Solid Quality Mentors)
• Spatial Data in SQL Server 2008 and SQL 2008 R2: A Real World Application – Javier Loria (Solid Quality Mentors)
• Implementing Common Business Calculations in DAX (Spotlight) – Chris Webb (CrossJoin Consulting)

Sessions are currently still being added to the agenda, so I’ll update my list here as well when needed.

If you’re going too and you’re from Belgium: have a look at the Belgian SQL Server User Group site for a discount code!

Ow, and post a comment here so we that can have a chat over there!

Have fun!

Valentino.