Twitter Updates

    follow me on Twitter

    Wednesday, December 20, 2006

    SQL Server 2005 SP2 December CTP

    Microsoft released a new December CTP of SP2 for SQL Server 2005. Along with this refresh came some other nice downloads:

    Microsoft SQL Server 2005 Data Mining Add-ins for Office 2007 - Community Technology Preview (CTP) December 2006
    Microsoft SQL Server 2005 Data Mining Add-ins for Office 2007 (Data Mining Add-ins) allow you take advantage of SQL Server 2005 predictive analytics in Office Excel 2007 and Office Visio 2007. The download includes the following components:
    • Table Analysis Tools for Excel: This add-in provides you with easy-to-use tasks that leverage SQL Server 2005 Data Mining under the covers to perform powerful analytics on your spreadsheet data.
    • Data Mining Client for Excel: This add-in allows you to go through the full data mining model development lifecycle within Excel 2007 using either your spreadsheet data or external data accessible through your SQL Server 2005 Analysis Services instance.
    • Data Mining Templates for Visio: This add-in allows you to render and share your mining models as annotatable Visio 2007 drawings."
    SQL Server Reporting Services Add-in for SharePoint Technologies CTP
    Microsoft SQL Server 2005 Reporting Services Add-in for SharePoint Technologies (Reporting Services Add-in) allows you to take advantage of SQL Server 2005 Service Pack 2 (SP2) report processing and management capabilities within Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007. The download provides the following functionality

    SQL Server 2005 Books Online SP2 CTP (December 2006)
    Updated BOL.

    SQL Server Database Publishing Wizard 1.0 RC
    SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window. If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider."

    And there is even more .... A full list of recent SQL related downloads can be found here.

    Monday, December 11, 2006

    The Cowboy Way of upgrading to SQL Server 2005

    One of the scenarios I showed during my presentation on upgrading to SQL Sever 2005 was "The Cowboy Way of upgrading". Here's the situation. A SQL 2000 database containing a user called sys, cannot be upgraded to SQL Server 2005. If you run the Upgrade Advisor, you will get a warning on this show stopper. So, the best practice here is to rename the user on SQL Server 2000 and afterwards upgrade to SQL 2005.

    In "The Cowboy Way of upgrading" I restored the SQL Server 2000 database with the sys user on my SQL 2005. The restore generated an error because of the sys users and the database was marked suspect.

    Remember that is definitely not the best way of upgrading. But since quite a few people asked about the script I decided to post it. Have fun but be careful.
    Connecting to localhost\sql2005...
    15 percent processed.
    23 percent processed.
    30 percent processed.
    46 percent processed.
    53 percent processed.
    61 percent processed.
    76 percent processed.
    84 percent processed.
    92 percent processed.
    100 percent processed.
    Processed 104 pages for database 'sys_database', file 'sys_database_Data' on file 1.
    Processed 1 pages for database 'sys_database', file 'sys_database_Log' on file 1.
    Converting database 'sys_database' from version 539 to the current version 611.
    Database 'sys_database' running the upgrade step from version 539 to version 551.
    Warning: User "sys" (principal_id = 5) in database "sys_database" has been renamed
    to "_ID_0x00000005_RENAMED_FROM_sys". "sys" is a reserved user or schema name
    in this version of SQL Server.
    Warning: Database "sys_database" was marked suspect because of actions taken during
    upgrade. See errorlog or eventlog for more information.
    Use ALTER DATABASE to bring the database online. The database will come online in
    restricted_user state.
    Database 'sys_database' running the upgrade step from version 551 to version 552.
    Database 'sys_database' running the upgrade step from version 552 to version 553.
    Database 'sys_database' running the upgrade step from version 553 to version 554.
    Database 'sys_database' running the upgrade step from version 554 to version 589.
    Database 'sys_database' running the upgrade step from version 589 to version 590.
    Database 'sys_database' running the upgrade step from version 590 to version 593.
    Database 'sys_database' running the upgrade step from version 593 to version 597.
    Database 'sys_database' running the upgrade step from version 597 to version 604.
    Database 'sys_database' running the upgrade step from version 604 to version 605.
    Database 'sys_database' running the upgrade step from version 605 to version 606.
    Database 'sys_database' running the upgrade step from version 606 to version 607.
    Database 'sys_database' running the upgrade step from version 607 to version 608.
    Database 'sys_database' running the upgrade step from version 608 to version 609.
    Database 'sys_database' running the upgrade step from version 609 to version 610.
    Database 'sys_database' running the upgrade step from version 610 to version 611.
    Msg 3167, Level 16, State 3, Line 1
    RESTORE could not start database 'sys_database'.
    Msg 3167, Level 16, State 1, Line 1
    RESTORE could not start database 'sys_database'.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Here's how I fixed this one. First of all I forced the database online (ALTER DATABASE sys_database SET ONLINE). SQL Server will put the database in single user. In the next step I checked the system catalogs to find out that SQL Server created a new schema called _ID_0x00000005_RENAMED_FROM_sys. My table was moved from the sys schema to this new schema.

    In the next step I created a new schema and used ALTER SCHEMA to move the table from _ID_0x00000005_RENAMED_FROM_sys to my new schema.
    CREATE SCHEMA system

    ALTER SCHEMA system
    TRANSFER _ID_0x00000005_RENAMED_FROM_sys.myTable

    In the last step I put the database in multi user mode.
    ALTER DATABASE sys_database

    Here's the full script.
    USE master

    --Force online
    ALTER DATABASE sys_database

    --Now in single user, show object explorer first
    USE sys_database

    SELECT name, SCHEMA_NAME(schema_id) as schema_name, *
    FROM sys.objects
    WHERE NAME = 'myTable'

    SELECT * FROM sys.schemas

    SELECT * FROM sys.database_principals

    SELECT * FROM sys.server_principals

    CREATE SCHEMA system

    ALTER SCHEMA system
    TRANSFER _ID_0x00000005_RENAMED_FROM_sys.myTable

    SELECT * FROM system.myTable

    INSERT INTO system.mYTable (col1) VALUES ('Frederik')

    ALTER DATABASE sys_database

    Wednesday, November 29, 2006

    Upgrading to SQL Server 2005

    Microsoft Belgium launched an Upgrade to SQL Server 2005 campaign. One of the cornerstones of the campaign is the brand new website The website is loaded with links to documents, webcast, hand-on labs and much more.

    On December, 6th I will be presenting a TechNet Evening on Upgrading to SQL Server 2005. I will demonstrate different upgrade scenarios and show you the pros and cons of each upgrade path. I will pinpoint potential pitfalls and show you the tools which can help you during the upgrade process.

    Full details and free registration can be found here.

    As a Sinterklaas present all attendees will receive a copy of the SQL Server Upgrade Resource kit for free.

    See you next week.

    Tuesday, November 21, 2006

    30 minutes of fun with Bart

    Make sure to catch Bart's funcast on Wednesday.

    During this 30min demo session we will show and explain you how to create your own blog and wiki site with Sharepoint 2007.

    Tuesday, November 14, 2006

    SQL Server Logon triggers part 2

    In April I wrote an article entitled "SQL Server 2005 logon triggers". The article will be published in the forthcomming book "The best of SQL Server Central - Volume 4".

    In the article I described how to mimic Oracle logon triggers using event notifications in SQL Server 2005.

    That was part one. Now comes part 2. Last week Microsoft released the CTP of SQL Server 2005 SP2. Here's a list of all new features in SP2. And one of them is logon triggers. Great, finally we have them.

    So can we do everything with logon triggers? No, there are some differences with the method I descibed in April using event notifications.

    • Logon triggers are part of the "logon transaction", this means you can rollback or cancel a logon. Event notifications go on the queue, after the logon. So, no easy way to cancel a logon.
    • triggers are raised synchronously with events, whereas event notifications are asynchronous.
    • Logon triggers only fire if authentication succeeds, event notifications can also capture failed logons and logouts.

    Anyway it is a nice new feature. I rewrote my logon trigger and it's now a lot easier to understand (keeping in mind the differences mentioned above). You can find the script at the end of this post.

    Ever wanted to prevent those Excel or Access users from reading your SQL tables directly? Well these 7 lines of codes will do the trick:

    CREATE TRIGGER logon_trigger_not_from_excel
    IF APP_NAME() LIKE '%excel%'

    Here's a complete sample script:

    -- Frederik Vandeputte -

    --TESTED ON SQL 2005 SP2 CTP

    --Clean up old logging db
    IF DB_ID ('Logging_demoSP2') IS NOT NULL
    DROP DATABASE Logging_demoSP2
    --Create Logging DB and table
    CREATE DATABASE Logging_demoSP2

    USE Logging_demoSP2

    CREATE TABLE Logging (

    LoggingID int IDENTITY(1,1),
    EventTime DATETIME,
    EventType VARCHAR(100),
    LoginName VARCHAR(100),
    HostName VARCHAR(100),
    AppName VARCHAR(255),
    FullLog XML

    --The LOGON Trigger
    CREATE TRIGGER logon_trigger

    DECLARE @message_body XML

    SET @message_body = eventdata()

    INSERT INTO Logging_demoSP2.dbo.Logging (
    FullLog )
    CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
    CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
    CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
    CAST(@message_body.query('/EVENT_INSTANCE/ClientHost/text()') AS VARCHAR(100)),



    --Open demo connections

    select * from logging

    --Prevent acces from Excel
    CREATE TRIGGER logon_trigger_not_from_excel
    IF APP_NAME() LIKE '%excel%'

    --Try to connect from Excel

    --Clean DB
    DROP DATABASE Logging_demoSP2
    DROP TRIGGER logon_trigger ON ALL SERVER
    DROP TRIGGER logon_trigger_not_from_excel ON ALL SERVER

    biTunes - The Source code

    As promised last week on Teched... the source code for biTunes.

    This zip files contains all the scripts, database backup, and BIDS solution for running your own biTunes BI solution.

    Scripts were created with SQL Server 2005 SP1.

    I included a small sample iTunes music library file. Copy your own iTunes Music Library.xml file to c:\biTunes\iTunes Music Library.xml to analyze your own MP3 collection.

    Have fun!!

    (Update, June 29h 2007 - new download location)

    Tuesday, October 31, 2006

    iTunes 7 vs. Mediaplayer 11

    During the dry-runs of my biTunes presentation people often asked me why iTunes? iTunes happened to be the MP3 player I used on Mac and Windows. So I answered: "It doesn't matter, it's just an MP3 player and it's very similar to Media Player, Winamp or other players".

    Today, Microsoft released Windows Mediaplayer 11. I decided to give it a try, and found out there are indeed some similarities. Have a look at the screenshots below or this little movie comparing both.

    So again, why iTunes? Well biTunes sounds better than biMediaPlayer ;-) See you next week!

    Wednesday, October 11, 2006

    Por Dentro SQL

    At last .... Peter - the president of SQLUG.BE - started his blog. His first post is an interesting one on clustering Integration Services.

    Success with your blog, Peter !!!

    His blog can be found on the following URL:

    Other interesting blogs on SQLUG.BE are Geoffrey blog called DrivenBySQL and the SQLUG.BE Resource Blog.

    Wednesday, October 04, 2006

    MSDN Evening - Introducing Data Dude

    There is an interesting MSDN evening comming up. Gunther Beersaerts, Senior Technical Specialist @ Microsoft will talk about Data Dude.

    November 15, 2006: Introducing Visual Studio Team Edition for Database Professionals

    Visual Studio Team Edition for Database Professionals is a database development product designed to manage database change and improve software quality through database testing. Additionally this product brings the benefits of Visual Studio Team System and life cycle development to the database professional. During this session you'll get an overview of this product, including the integration with source code control, the schema and data compare functionalities, rename refactoring, the new T-SQL editor and database testing.

    Full Details are on

    Thursday, September 28, 2006

    Speaking at Teched - biTunes

    iTunes meets BI!! I will be speaking at Teched : Developers in Barcelona. The session is called "biTunes - Building an end-to-end Business Intelligence (BI) application with SQL Server 2005 and … iTunes!"

    This is the abstract.

    SQL Servers 2005 gives you all the tools you need for building end-to-end BI applications. In this 100% demo driven session we walk through the complete SQL Server 2005 BI stack. We'll use SQL Server Integration Services to import the iTunes (yes, that's right) music library. We'll build an OLAP cube on top of our MP3 library with SQL Server Analysis Services. Finally we'll use SQL Server Reporting Services and Excel 2007 to reveal information you thought you could never retrieve from iTunes. Come and take a walk on the BI-side...

    The session is planned for Wednesday, Nov 8 13:30 - 14:45 (UPDATED) . I am looking forward to meet you there.

    I am planning a couple of dry runs during the next weeks. If you would like to attend one of these , drop me an e-mail.

    Sunday, September 24, 2006

    AS 2005 - RTFC - bulk change calculated members

    I was playing around with MDX and calculated measures in AS 2005. One of the nice things in AS 2005 is the script view. The Script view gives you all the calculations in a "text editor". This is really great if you need to do bulk changes to your calculated members. E.g. change the format for all uour calculated measures.

    Have you tried that in AS 2000? Well I did, it takes some DSO programming Been there, done that during the last weeks.

    So I was playing around in the script view, copy/pasting, global replacing ...and all of a sudden my cube was empty... How come? Well here's what I did wrong.

    Open the sample solution that ships with SQL Server 2005 Samples (C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Tutorials\Lesson4). Deploy the cube and play arround in the cube Browser. You should see some data.

    Now, go to the Calculations tab, and choose the Script view. You will see something like this:

    The CALCULATE command controls the aggregation of leaf cells in the cube.
    If the CALCULATE command is deleted or modified, the data within the cube is affected.
    You should edit this command only if you manually specify how the cube is aggregated.


    If you create calculated measures (or other calculations) they will come in here. If you create a new dummy calculated measure the script view might look as follows:

    The CALCULATE command controls the aggregation of leaf cells in the cube.
    If the CALCULATE command is deleted or modified, the data within the cube is affected.
    You should edit this command only if you manually specify how the cube is aggregated.

    AS [Measures].[Reseller Sales-Discount Amount]*2,
    VISIBLE = 1 ;

    After you deployed the cube you will see the dummy calculated measure in the cube browser.

    Now let's go back to the Calculations tab and just delete the line "CALCUATE;"

    Redeploy, go back to the browser and what will you see: NADA!!

    So well, what have we learned today:
    1. This Script View thing is great!
    2. But be carefull!
    3. RTFC (Read The F* Comments): If the CALCULATE command is deleted or modified, the data within the cube is F*.

    Monday, September 18, 2006

    Best Practices Analyzer 2.0 comming soon

    I just finished watching Paul Mestemaker's webcast on the SQL Server Upgrade Advisor and the New SQL Server Best Practices Analyzer tool. Paul gives a demo of the upcomming Bast Practices Analyzer for SQL Server 2005.

    Some highlights of this upcomming tool:
    • Full support of DB engine, SSIS and Analysis Services on SQL 2005
    • Lmited (DB engine) support for SQL Server 2000
    • UI and "way of working" (including command line option) is similar to the Upgrade Advisor
    The great news: First public version is expected late October, early November.

    More Best Practices Analyzer news can be found on the SQL Server Relational Engine Manageability Team Blog

    Monday, September 11, 2006

    SQL Server 2005 on Mac

    Sometime ago I spotted this KB article stating that SQL Server 2005 was not supported on Intel-based Apple Macintosh computers running Microsoft Windows operating system.

    One of the things I learned with Ingres was that "not supported" is not the same as "it does not work".

    So, last week I spend some evenings/nights pushing my luck and managed to get Vista and SQL Server 2005 running on an intel based iMac. During those installations I saw quite some freaky eror messages, warnings, aborted installations, and more funny stuff. Which again reminded me of ... ;-)

    At the end of the week I had SQL Server 2005 running on Vista on an intel based iMac. It's not supported ... but it seems to work.

    Friday, September 08, 2006

    SSMS Reports - RDL files

    Do you like those fancy reports in the SQL Server 2005 management studio? Well if you do, you probably searched your hard disk for the RDL files. Did you find them? I don't think so, neither did I.

    Well, now I found them. On the SQL Server Relational Engine Manageability Team Blog.

    Look for the ZIP file at the bottom of this post.

    Direct download:

    Wednesday, September 06, 2006

    Running Vista on an iMac

    It's funny ... Once you go Mac you want to go Vista. I have been following Hans Verbeeck's adventures trying to get Vista running on his Mac Book Pro. Last week Bart finally got OS X running on his standard PC. He posted some screenshots and videos.

    Yesterday I installed Vista Build 5536 (Pre RC1) on an Intel iMac. I used the recently updated Boot Camp 1.1 to create a Windows partition. After that the installation was a snap. No more need to delete the EFI partition.

    Next steps are the drivers and of course ... SQL Server.

    More SQL Server on Mac comming soon...

    Monday, September 04, 2006

    Don't use SELECT * in views

    We all know we should use schema_binding and always list all column names in SELECT statements and view definitions. We know we should ... but nobody does it ;-)

    Here's al little scripts which demonstrates how funny a SELECT * FROM ...view behaves when you start altering the underlying tables.

    Tip of the day ... don't use SELECT * FROM in a view.... ;-)

    /* Created by free online sql formatter */
    --Drop objects if they exists
    DROP VIEW v1

    x INT,
    y VARCHAR(5))

    VALUES (1,


    SELECT *
    FROM t1

    --This shows columns a and b
    SELECT *
    FROM v1

    --Add a third column to our table

    UPDATE t1
    SET z = GETDATE()

    --Third column is in table

    SELECT *
    FROM t1

    --Third column is NOT in view
    SELECT *
    FROM v1

    --Drop column x from table

    --Here's the freaky part
    --Column headers are x and y but data shown is from columns y and z
    SELECT *
    FROM v1

    Wednesday, August 23, 2006

    Blog moved - update your RSS feeds

    Earlier this week I moved my blog to the great guys of

    This move might cause some problems if you were reading my via an RSS reader. Just to put things straigth. These are the correct RSS feeds (both in RSS and ATOM format).

    Friday, August 04, 2006

    SQL Server is not supported ...

    ... on Intel-based Apple Macintosh computers that run a Windows operating system

    "This article explains that Microsoft SQL Server is not supported on Intel-based Apple Macintosh computers that run a Microsoft Windows operating system.";en-us;922664&sd=rss&spid=2855

    Snif, snif ;-)

    Thursday, July 20, 2006

    cohPod - The one dollar iPod

    Fact 1: I really like iTunes. It is a cool music player and it's pretty good in managing a complete MP3 library. I am importing all my CDs and LPs and vinyl singles into iTunes on a Mac. I still have some work to do but I am heading towards to an LP and CD free living room.

    Fact 2: I want an iPod. I don't have one right now. Here's why. First of all I think they are pretty expensive. Today a 4 GB iPOD nano costs 259 EUR (July, 2006 Belgian Price in Apple Store). A 4GB iPod can hold about 1000 songs. That's 0, 259 EUR per song. In real money that's more than 10 BEF per song.

    Second reason ... if I had an iPOD ... when would I use it? Well, at work, at home and in my car. Using the iPod on my MTB or while running (Nike+iPod :-)) would be nice but if I want to cut prices I have to sacrifice some features.

    So I came up with the cohPOD. The cohPod only works in my car (c), in the office (o) and at home (h).

    The cohPod is available in two editions: a 700 MB model and a 4,7 GB model.

    Here's how it works:

    Step 1: choose your cohPod model
    Pick a bank CD-RW (700 MB) or DVD+/-RW 4,7 GB), take a pen and label it cohPod.

    Step 3: Create a cohPod playlist on your main iTunes computer
    In my iTunes library on my Mac I created a cohPod playlist. I dragged all the songs I wanted on my cohPod to this playlist. Make sure the total size of the playlist does not surpass the capacity of your cohPod (700 MB/4,7 GB).

    Step 3: Burn the cohPod playlist to your CD-ROM
    To do this manually, right click on the cohPod playlist and choose Burn Playlist to disk. This method will also work if your main iTunes library is on a PC.

    I am working on an Apple Script which will automate this step. However the script did not make it in the RTM version of the cohPod. It might be included in cohPod SP1 or in the long rumoured cohVideoPod.

    Step 4: Listen to the cohPod in my car
    My Car CD player plays MP3 CDs, unfortunately it does not support DVDs. In other words, the 700MB cohPOD works fine in my car without any cables. The 4,7GB model is currently not supported.

    Step 5: Listen to the cohPod at work
    My Office PC runs Windows XP (no SQL Server on Mac yet ;-(), so I wrote this little VB-script to import the cohPOD playlist. All I need to do is launch the ImportCohPod script (via Launchy) and I there you go ...

    Here's the importcohPod.vbs

    'Frederik Vandeputte

    'Change to match your CD drive
    FolderToAdd = "e:\"

    'Set objects
    Set objApp = CreateObject("iTunes.Application")
    Set colSources = objApp.Sources
    Set objLibrary = colSources.ItemByName("Library")
    Set colPlaylists = objLibrary.Playlists

    'Are you sure?
    answer = MsgBox("Are you sure you want to import " & FolderToAdd & " into you cohPod Playlist?", vbYesNo, "cohPod")
    If answer = vbNo Then
    End If

    'Delete old cohPod Library if exists
    Set objPlaylist = colPlaylists.ItemByName("cohPod")
    If Not (objPlaylist is Nothing) Then
    End if

    'Create new cohPOD playlist
    Set objPlaylist = objApp.CreatePlaylist("cohPod")

    'Add files to cohPod playlist

    answer = MsgBox(Cstr(objPlaylist.Tracks.Count) & " songs added to your cohPod Playlist (" & CInt(Cstr(objPlaylist.Size/1024/1024)) & " MB)", vbYes, "cohPod")

    The 700MB cohPod holds about 175 songs, the 4,7 GB model holds about 1200 songs. A rewritable DVD costs about 1 EUR these days. This means the cohPOD costs less than 0,001 EUR per song. Compared to the 0,259 EUR per song for the 4GB iPod Nano, this is really cheap.

    Secondly the cohPod does not require any cables. Not for synchronizing, nor for playing it in the car. Which makes it a true wireless xPod.

    Third plus: battery life. Have you ever seen a DVD run out of batteries?

    The cohPod doesn't look as flashy as Apple's iPods. It doesn't have all the cool features of the iPod (color screen, Photo's/Video's, multiple playlist, ....). People always talk about features ... but all they do with an iPod is playing songs, clicking next and previous and I can do all this with the cohPod.

    Two more pics of the cohPod connected to a Mac and to a PC.

    Some interesting resources:
    iCame, iPod, iScripted: Scripting iTunes
    iTunes COM for Windows SDK

    Monday, July 17, 2006

    Installing SQL Server SP1 and hotfixes with a single click of the mouse

    Installing SQL 2005 hotfixes requires quite some mouse clicks: .... Click Next, Next, Yes, Yes, Finish, ....

    This little batchfile will upgrade all your SQL 2005 instances and all componens to build 2153 with a single mouse click.

    REM install hotfixes
    start /wait sql2005-kb918222-x86-enu.exe /quiet /allinstances
    start /wait as2005-kb918222-x86-enu.exe /quiet /allinstancese
    start /wait dts2005-kb918222-x86-enu.exe /quiet /allinstances
    start /wait ns2005-kb918222-x86-enu.exe /quiet /allinstances
    start /wait rs2005-kb918222-x86-enu.exe /quiet /allinstances
    start /wait sqltools2005-kb918222-x86-enu.exe /quiet /allinstances

    Take care of the order in wich you apply the hotfixes. Check out KB 919224 article if you receive this funny error:

    "Connect to SSIS Service on machine "computername" failed: Error loading type library/DLL."

    You can use the same trick (/quiet /allinstances) for installing the SP1 itself.

    REM install SP1
    SQLServer2005SP1-KB913090-x86-ENU.exe /quiet /allinstances

    If you just want to get a list of the installed components and versions, this will do the trick:

    REM enum components and versions
    SQLServer2005SP1-KB913090-x86-ENU.exe /reportonly /allinstances

    After the installation of the hotfixes make sure to have a look at logfiles in %windir%\hotfix.

    Friday, July 07, 2006

    What a week

    Secure Gmail

    Wikipedia in 't West-Vlams

    Mozilla Firefox Cheat Sheet

    Quote of the week
    "The trouble with facts is that there are so many of them." - Samuel McChord Crothers

    Song of the week - Das Pop - Tired
    Listen to it on

    Friday, June 23, 2006

    Checking the status of OLAP Cubes

    Running a full process of a shared dimension will bring down all the cubes that use that shared dimension. I needed a mecanism to check the status of all my cubes in an Analysis Services 2000 installation.

    So I came up with the following VBS script. It's quick and dirty but it works.

    ServerName = "LocalHost"
    'Delimiter = Chr(9) 'Tab
    Delimiter = ";"

    Set dsoServer = CreateObject("DSO.Server")

    'Connect Analysis server
    dsoServer.Connect ServerName

    'Walk through OLAP DBs
    For Each dsoDB In dsoServer.MDStores
    'Walk through Cubes.
    For Each dsoCube In dsoDB.MDStores
    State = "Other"
    If dsoCube.State = 0 Then
    State = "OFFLINE"
    ElseIf dsoCube.State = 4 Then
    State = "Online"
    End if
    Wscript.Echo dsoDB.Name & Delimiter & dsoCube.Name & Delimiter & State

    To run it use the command below:

    cscript /NoLogo CubeStatus.vbs

    And this could be the result


    Friday, June 09, 2006

    SQL Server Everywhere CTP is here

    The CTP of Microsoft SQL Server 2005 Everywhere Edition can be download here.

    Books online for thise nice new toy are here.

    Funny .... the documentation download is larger than the actual product download ;-)

    SQL Server 2005 Upgrade Technical Reference Guide

    Yesterday Microsoft published the SQL Server 2005 Upgrade Technical Reference Guide on the website. It's a + 350 pages document, so it should be enough to keep you busy during this sunny weekend.

    Also interessting is this best practices guide on Database mirroring.

    Saturday, June 03, 2006

    Visual Studio 2005 Team Edition for Database Professionals

    Earlier this week Microsoft announced Visual Studio 2005 Team Edition for Database Professionals. The product is expected to ship by the end of 2006, but there will be CTP release next week.

    More info:
    Some of the team members started blogging about this upcomming product on their blogs:

    Monday, May 22, 2006

    Ta Da Lists

    37 Signals, the guys behind Ruby on Rails, have create some nice (and free) webapps. My favourite is Ta Da Lists. Basically it's a small to do list manager. The thing I like most about Ta Da Lists is the abality to subscribe to your own to do lists via RSS.

    A video about how it works can be found the URL below:

    Tool of the week!!!

    SQL Server User Group - First meeting

    It is so far, the is proud to announce the first User Group evening which will be organised on thursday, June 22. The location is in the Microsoft classrooms at Av. Culliganlaan 1A, 1831 Diegem.

    The event is free, but registration is required. Register now, and get the opportunity to vote for the topics to be discussed during the event.

    More info on

    Thursday, May 18, 2006

    SQL 2005 SP1 hotfix

    Microsoft released a hotfix for SQL Server 2005 SP1. The hotfix can be downloaded from the Microsoft website:

    Cumulative hotfix package (build 2153) for SQL Server 2005 is available.

    Euan Garden wrote a little FAQ about the hotfix on his blog.

    Saturday, May 06, 2006

    What a Mac Week

    Funy new iMac ads

    macosxhints - 10.4: Share an iPhoto library among multiple users

    Mac Book comming next Tuesday

    Tool of the week - Adium
    Adiums a free instant messaging application for Mac OS X that can connect to AIM, MSN, Jabber, Yahoo"

    Quote of the week
    I have been working on quite some BI related projects recently. A nice qoute from Nietzsche:

    "There are no facts, only interpretations"

    Song of the week - Jesse James
    Bruce Springsteen recorded a very nice version of Jesse James on his last album .

    Saturday, April 29, 2006

    What a week

    Don't apply SQL 2005 Service Pack 1 if you use SSIS

    Monad becomes Windows PowerShell

    Overstekend Wild : Weekly column from Marc Reynebeau. available in podcast

    Quote of the Week
    "I wish I Was Mr. Gates" - Bruce Springsteen in Pay me my money down

    Song of the week - Lenny Kravitz - Breathe

    Available as a free download from

    Monday, April 17, 2006

    Article - SQL Server 2005 Logon Triggers

    My first article (SQL Server 2005 Logon Triggers) was published by SQL Server Central today.
    In the article I describe how to mimic Oracle logon triggers using event notifications in SQL Server 2005.

    The article can be found on the URL below (free registration required)

    [UPDATE] The sample code is now available on the following url:

    Friday, April 14, 2006

    What a week

    More SQL Suduko Solvers

    SQL Server Everywhere FAQ

    Processing an AS2005 cube from the command line with XMLA

    Drop support dates SQL Server 2000

    Portable Firefox has been officially released

    MacBooks comming soon
    "Apple has begun manufacturing its new MacBook and should have the laptop in consumer hands in the next 30-60 days, sources report."

    Booting Solaris on Mac

    Song of the week
    Blondie Vs The Doors - Rapture/Riders

    Lease a sheep

    Wednesday, April 12, 2006


    I was a happy Colibri user for the last couple of months. Colibri is a very nice alternative program launcher for Windows. Last week I uninstalled Colibri and switched completely to Launchy. Launchy is better, faster, and it is opensource. Since version 0.9.1 search results are also sorted by the frequency of use.
    Press ALT SPACE F ENTER to launch Firefox. Press ALT SPACE S ENTER to launch SQL Server Management Studio.

    Thanks for the tip, Mr. Peter.

    From the Launchy Readme.pdf

    Launchy is the most efficient way to open files and programs on your
    computer. Nobody likes to hunt through the start menu to find an application,
    just to find that the application is hidden under some obscure folder named
    after a company you have never heard of! Instead, Launchy is a smart search
    program which tries to guess which program or file you are looking for and will
    launch it when you hit the enter key. It is only visible when you hit the
    alt+space key combination; otherwise it hides in the background. Once you
    have used it for a few days, it becomes an indispensable utility for your
    computer. And it’s free!

    Thursday, April 06, 2006

    What a week

    Apple supports XP on Mac with BootCamp

    Firefox Extension of the week
    The Copy URL+ extension enables you to copy to the clipboard the current document's address along with additional information such as the document's title, the current selection or both.

    Ten of the Biggest Mistakes Developers Make With Databases

    Quote of the week - Dave Dargo's Blog
    "But all these things add up and now I'm lucky enough to be doing another cool thing—working with Ingres."

    Song of the week
    Blancmange - Don't Tell Me

    Breaking SQL Server 2005 news

    Breaking news:
    • Database mirroring will be supported in SP1.
    • SP1 will contain the RTM version of SQL Server Management Studio Express Edition.
    • By the end of this year there will be a SQL Server Everywhere Edition. This edition will run on anything from smarphones through servers.

    This, and more breaking SQL Server news will be announced later today by Paul Flessner. Euan Garden gives us a sneak preview on his blog.

    Switching to English

    During the last months the scope of my blog narrowed more and more to SQL Server and IT stuff in general. This gives me a good reason to switch it to English. I will use Babel Fish to translate between Dutch and English.

    From English to Dutch...

    Tijdens de laatste maanden in het algemeen versmalde het werkingsgebied van mijn blog meer en meer aan SQL Server en het materiaal van IT. Dit geeft me een goede reden om het aan het Engels te schakelen. Ik zal de Vissen van Babel om tussen het Nederlands en het Engels gebruiken te vertalen.

    ... and back to English

    During the last months in general commonly narrowed the functioning area of my blog to SQL server more and more and the material of IT. This gives me a good reason to change gear it to English. I the fish of Babel will translate between Dutch and English using.

    Babel Fish is nie slecht, maar juuste nie ....

    Tuesday, April 04, 2006

    Microsoft koopt ProClarity

    Microsoft Agrees to Acquire ProClarity, Enhancing Business Intelligence Offering: Leader in advanced analytics adds to Microsoft’s comprehensive BI capabilities.

    Virtual Server R2 gratis

    Virutal Server R2 is vanaf nu beschikbaar als gratis download. Bovendien supporteert Microsoft Linux nu ook (officiëel) als guest OS binnen Virtual Server.

    Wednesday, March 29, 2006

    Solving Sudoku with SQL

    Onder het motto I Can do everything with T-SQL.... Hier vind je een artikel hoe je een Sudoku kan oplossen met T-SQL. Solving Sudoku with SQL. Funny :-)

    Saturday, March 25, 2006

    Replication Monitor - Tracer Tokens

    De Database Mirroring Monitor is leuk speelgoed in SQL Server 2005. Voor replicatie bestaat er een gelijkaardige tool, namelijk de Replication Monitor.

    Replication Monitor maakt het heel makkelijk om latency in je replicatie te traceren. Door tracer tokens weg te schrijven in de transactie logfile kan je heel makkelijk te weten komen wat de latency is tussen publisher en distributor, tussen distributor en subscriber, en de totale latency.

    Meer info vind je in BOL (How to: Measure Latency and Validate Connections for Transactional Replication (Replication Monitor)).

    Friday, March 24, 2006

    Database Mirroring Monitor - screenshots

    Het ziet er naar uit dat database mirroring met de komst van SQL Server 2005 Service Pack 1 officiëel gesupporteerd zal worden. Het al dan niet opzetten van trace flag 1400 heeft in de March CTP geen belang meer.

    Nieuw en zeer leuk is de Database Mirroring Monitor. Hiermee kan je alles in het oog houden. Je kan ook tresholds gaan definiëren. Bij het overschrijven van die tresholds wordt er een boodschap geschreven in de event log en die kan je dan gaan opvangen met de SQL Server Agent of MOM (of een andere monitoring tool).

    Ook nieuw is sp_dbmmonitorresults. Deze system stored procedure toont je snel de status van een mirrored database.

    exec msdb..sp_dbmmonitorresults 'AdventureWorks'

    Hieronder een paar screenshots van het nieuwe speelgoed.

    Free Training - Exforsys

    Op de website van Exforsys staan een hele boel gratis tutorials, o.a. over SQL Server 2005 en Analysis Services.

    Saturday, March 11, 2006

    Friday, March 10, 2006

    Belgische SQL Server User Group

    Peter De Seranno werkt met vol enthousiasme aan de oprichting van een Belgische SQL Server User Group. Alle info en binnnkort ongetwijfeld veel meer vind je op

    Freemind is Project of the month

    SourceForge koos in februari Freemind als project of the month.

    Sunday, March 05, 2006


    SchemSapy is a Java-based tool that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships. The browsing through relationships can occur though HTML links and/or though the graphical representation of the relationships. It's also designed to help resolve the obtuse errors that a database sometimes gives related to failures due to constraints.

    Als je ook dot van Graphviz installeert en in je PATH (C:\Program Files\ATT\Graphviz\bin\) zet genereert het tooltje ook grafish de relaties tussen de tabellen.

    Zorg er ook voor dat je de SQL Server 2000 JDBC driver geïnstalleerd hebt (default locatie is C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib).

    Het volgende commando genereert de documentatie voor het Sales schema in de SQL 2005 AdventureWorks database.

    java -jar schemaSpy.jar -t mssql -db adventureworks -host localhost -port 1433 -u sa -p pwd -s Sales -o AWSales

    De output vind je hier.

    Thursday, March 02, 2006

    Reporting Services Server Management Reports

    Bij SQL Server 2000 Reporting Services zit er een DTS pakketje waarmee je de data uit de ExecutionLog tabel kan overzetten naar een meer gestructureerde en bruikbare structuur. Kijk eens in c:\Program Files\Microsoft SQL Server\80\Tools\Reporting Services\ExecutionLog.

    In 2005 is dit DTS pakketje een SSIS pakketje geworden en hebben ze er nog eens 3 interessante sample rapportjes bijgestoken.

    Al dit speelgoed is te vinden in c:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\Server Management Sample Reports\Execution Log Sample Reports.

    Meer info:

    Thursday, February 23, 2006

    TNT Technet Sessies

    Regelmatig post Microsoft een hele boel Technet presentaties en demo's op hun download site. Er zitten veel interessante zaken tussen, rond SQL Server maar ook over andere technologieën.

    Search MS download site for Technet sessions

    Tuesday, February 21, 2006

    Renaming database objects in SQL Server 2005

    Naming conventions zijn goed. Alleen jammer als je achteraf alles moet aanpassen. Hieronder vind je een paar scripts om kolommen, tabellen en constraints te renamen.

    --Rename primary keys
    --Remove Dim from column name
    'EXEC sp_rename ''HR.' + TABLE_NAME + '.'
    + COLUMN_NAME + ''', ''' + SUBSTRING(COLUMN_NAME,4, 64) + ''', ''COLUMN'''

    --Rename tables
    --Remove Dim from table name
    'EXEC sp_rename ''HR.' + TABLE_NAME +
    ''', ''' + SUBSTRING(TABLE_NAME,4, 64) + ''', ''OBJECT'''

    --Rename foreign keys
    SELECT 'exec sp_rename ''HR.' + name + ' '',''FK_'
    + object_name(parent_object_id) + '_'
    + object_name(referenced_object_id) + ''''
    FROM sys.foreign_keys

    --Rename Primary keys
    SELECT 'exec sp_rename ''HR.' + name + ' '',''PK_'
    + object_name(parent_object_id) + ''''
    FROM sys.key_constraints
    AND [TYPE] = 'PK'

    --Rename default
    SELECT 'exec sp_rename ''HR.' + + ' '',''DF_' +
    + object_name(parent_object_id) + '_' + +''''
    FROM sys.default_constraints d
    INNER JOIN sys.columns c
    ON d.parent_object_id = c.object_id
    AND d.parent_column_id = c.column_id

    Wednesday, February 08, 2006


    Colibri is een fantastisch alternatief voor het gewone Start Menu.

    Colibri combines the raw power of the command line with the intuitive nature of a modern graphical user interface to define a revolutionary new way of managing your computer.

    Een fantastisch tooltje voor de kings of the keyboard.

    Alle info op

    Tuesday, January 24, 2006

    Backup OLAP 2000 databases

    Een VBS Scriptje dat een archive neemt van elke OLAP database in een Analysis Services 2000 installatie.

    Set dsoServer = CreateObject("DSO.Server")
    Set dsoDatabase = CreateObject("DSO.MDStore")

    Set objShell = WScript.CreateObject("WScript.Shell")

    ServerName = "mySERVER"
    msmdarchPath = "c:\Program Files\Microsoft Analysis Services\Bin\msmdarch"
    OlapPath = "c:\Program Files\Microsoft Analysis Services\data\"
    BackupPath = "c:\temp\"

    dsoServer.Connect ServerName
    For each dsoDB in dsoServer.MDStores

    cmd = """" & msmdarchPath & """" & " /a " & ServerName & " """ _
    & OlapPath & """ """ & dsoDB.Name & """ """ & BackupPath & dsoDB.Name & ".CAB""" cmd, 6, True
    'wscript.echo cmd

    Thursday, January 19, 2006

    Microsoft SQL Server 2005 JDBC Driver

    Microsoft heeft een nieuwe JDBC driver voor SQL Server 2005 uitgebracht.

    In its continued commitment to interoperability, Microsoft has released a new Java Database Connectivity (JDBC) driver with SQL Server 2005. The SQL Server 2005 JDBC Driver download is available to all SQL Server users at no additional charge, and provides access to SQL Server 2000 and SQL Server 2005 from any Java application, application server, or Java-enabled applet. This is a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in J2EE (Java2 Enterprise Edition).

    The SQL Server 2005 JDBC Driver is JDBC 3.0 compliant and runs on the Java Development Kit (JDK) version 1.4 and higher. It has been tested against all major application servers including BEA WebLogic, IBM WebSphere, JBoss, and Sun.

    Thursday, January 05, 2006

    Weer zo'n tooltje dat doet wat het moet doen, niets meer en uiteraard niets minder: is an e-mail notification system that scans the entire Microsoft Knowledge Base every night, and e-mails you when updates or additions are made to the technologies you subscribe to.

    Meer moet dat niet zijn: tooltje van de week dus!!