August 2009

You are currently browsing the monthly archive for August 2009.

Imagine an Integration Services package where you need to load data from an Oracle database.  Typical for ETL you try to use a timestamp field in the source table to determine if there were any changes since last load.  Let’s say that field is called LAST_MODIFICATION.  The query that you’re using to load the data is parameterized and the value passed in is the highest timestamp of the previous Load (or should I say Extract?).  So your query may look something like this:

WHERE LAST_MODIFICATION > to_date(?, ‘yyyy/mm/dd hh:mi:ss’)

In this query the question mark gets replaced with a datetime value, for instance ‘2009/08/27 21:26:32’.  (Yeah, I know, you shouldn’t select star but that’s not the point of this post.)

When running your package you suddenly get the following error:

ORA-01849: hour must be between 1 and 12

After some cursing and trial and error, you decide to have a closer look at Oracle’s to_date() function.  And there is your answer: HH returns the same as HH12, an hour between 1 and 12.  Which is not what we want here, we want hours starting at 1 and ending at 23.  So we should use HH24 instead.  A correct query would be:

WHERE LAST_MODIFICATION > to_date(?, ‘yyyy/mm/dd hh24:mi:ss’)

Another lesson learned… (and noted in case I need it again :-) )


Tags: , , ,

While building parameterized reports in SQL Server Reporting Services 2005 you may come across the following error when viewing the report in Preview.

An error occurred during local report processing.

The value provided for the report parameter ‘YourParameter’ is not valid for its type.

I encountered this error even though I had not specified a default value for the parameter.  So how can it be not valid if it doesn’t exist?

I found a perfect solution in this little article <broken link removed>.

Apparently it has something to do with the Allow blank value checkbox.  In my report the data type for my parameter was Integer.  And as explained in the mentioned article, the Allow blank value checkbox is checked and greyed out.  Luckily the little trick of switching to String, unchecking the checkbox and switching back to Integer solves the error.

But why?  How come that an error occurs due to a setting that is greyed out?  A greyed out setting, doesn’t that mean that its value is not applicable in combination with the other settings?  Apparently not.  Well, I guess that’s a bug then, right?

If someone has a good explanation on this behaviour don’t hesitate to post a comment!


Tags: , ,

If you don’t have anything planned on next September 2 and you’re interested in some free SQL Server-related learning: it’s the 24 Hours of PASS!


You can even stay in your lazy chair at home because it’s an online event, no worrying about bus/train/plane/hotel/…  Just install the software (or browser plug-in, I actually don’t know because I haven’t performed the preparation procedure yet) and off you go.

I have registered for the following 5 sessions myself:

  • Session 10 (Dev) – Working with Spatial Data in SQL Server 2008 (Greg Low)
  • Session 11 (DBA) – Effective Indexing (Gail Shaw)
  • Session 12 (BI) – Reporting Services Inside Out the Things You Should Know (Simon Sabin)
  • Session 13 (Dev) – Query Performance Tuning 101 (Grant Fritchey)
  • Session 16 (DBA) – Database Compatibility Settings: What They Really Do .. and Don’t Do (Don Vilen)

Yep, it will be a busy holiday.  That same day they’ll be delivering our new combi oven, ideally that would be right after session 13 ends.  Fingers crossed.

Anyway, I’ll be seeing you September 2?  Or well, maybe not as it’s an online event…

Happy learning!


Tags: , , ,

I’ve had the pleasure of seeing the following string in the Status Bar of my Explorer window while the focus was on the C: partition:

Disk free space: 0 bytes

This was after I got several error messages when logging on.  There was not enough space free to load my profile.  And that happened after I rebooted my machine because it had installed a batch of updates.

I was happy to see that even with all those errors, I could still get into Windows (XP SP3) – okay, it didn’t load my background picture nor my favorite Explorer settings but who cares at that point – and free up some space.  After doing that I rebooted and logged on again using my profile, this time it did load successfully.

As you may tell, I have been having space issues for a while now.  Here are some tips that you can use if you want to free up space on the C: partition of a Windows XP installation.

Run Disk Cleanup

In case you’re not familiar with this tool, have a look at this article at The Elder Geek, or this Microsoft KB article.

I run this occasionally, especially right before starting a defragmentation.  To defragment my drives I use JkDefrag which does it quite nicely btw.

Compress Folders

If your partition is formatted using NTFS, you have the option of compressing your folders.  This is an easy solution but usually it doesn’t really give that much extra space.  Unless you are storing a lot of large text files on your C: partition.  Anyway, currently I have both my \Program Files\ and my \Documents and Settings\ folders compressed.

Beware though: SQL Server does not like its folders compressed.

Move Swap File

If you haven’t done so already, an easy way to free up space is to move the pagefile.sys to another partition.  See the How to move the paging file in Windows XP KB article for more info.

My paging file is on the D: partition.

Reduce Space For System Restore

Here’s an interesting article about how the System Restore functionality uses harddisk space.  Now you know why you see the balloons popping out of the System Tray when you’re in a space race :-)

The fastest way that I can think of to change the space usage settings are: Windows Key + Break > System Restore tab > select drive > Settings button.

As I recently ran out of space on the C: partition, practically all my restore points are gone already.

Good information about the System Restore tool is available here on Tech Republic.  With that info you’ll be able to decide how to adjust your settings.

Remove Backup Folders

Hotfix Uninstall Folders

When Windows updates are installed, for each hotfix there will be a backup folder.  You’ve probably already seen them.  Under C:\Windows there are some folders called $NtUninstallKB123456$, or similar.  You can even recognize the Knowledge Base number in the folder name.

These folders are only needed in case you want to uninstall a certain hotfix.  Until now I never needed to do this.  But still, to be safe, I keep the ones that have been installed recently, the last 3 months for instance.  The rest, I delete.

More info on these folders:

Service Pack Uninstall Folders

Similar to the hotfix folders, backup folders are being created when installing a Windows XP Service Pack.  The folder that you may remove once you’ve decided that you will never want to uninstall your Service Packs is called $NtServicePackUninstall$.

Here’s a good Microsoft KB article about How to Remove Windows XP Service Pack 1 Folders.  Although it’s aimed at SP1, it still applies if you’ve installed SP3.  I have deleted all folders that contained “$NtServicePackUninstall” in their name.

Another good read on this topic can be found on Tech Republic, where the writer covers a backup procedure in case you change your mind afterwards.

SQL Server Hotfix Backup Folders

Again similar to the Windows hotfix folders, hotfixes for SQL Server create uninstall folders as well.  The folders that you may find back on your machine (depending on what components you’ve got installed) are:

  • SQL9_KBxxxxxx_ENU
  • SQLTools9_KBxxxxxx_ENU
  • RS9_KBxxxxxx_ENU
  • OLAP9_KBxxxxxx_ENU
  • DTS9_KBxxxxxx_ENU
  • NS9_KBxxxxxx_ENU

These folders are for SQL Server 2005 and depending on what hotfixes you have installed you may have several similar folders with different KB numbers (the xxxxxx stands for a number).

I couldn’t find an article to support this part of my blog item, but here’s a short discussion about it with a comment from a Microsoft representative.

I removed them all.  (Please do note that this is not on a production environment.)

Internet Explorer Update Backup Folders

Just like the uninstall folders mentioned earlier, patches for Internet Explorer are creating backups as well.  On my machine I could find the following two folders:

  • ie7updates
  • ie8updates

If you don’t plan on uninstalling Internet Explorer 7/8, it should be safe to delete the content of these folders.  But I couldn’t find an official statement to confirm this.  If you know of an article that covers this please do let me know!

Until now I have not yet deleted these myself.

Remove SQL Server Setup Log Files

When performing installation changes to your SQL Server installation, including upgrades, several log files are created.  These files are located under C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG for SQL Server 2005 and C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log for SQL Server 2008.

Check out the Books Online for info on how to view and how to read these setup log files.

If you’re not in the middle of performing installation changes and all your previous changes were successful then it’s safe to remove the content of this log folder.

More info on this folder related to security can be found in this MSSQLTips article.

Resize The Partition

If with all of the above you’re still struggling for space, only one option (besides a full re-install) remains: resize the partition!

I wasn’t aware of a free tool that could do this until one of my friends told me about EASEUS Partition Master (thanks btw :-) ).  If you need it professionally or you have a 64-bit machine, check out the Professional Edition.

With this tool I was able to first make my D: partition smaller and then increase the size of the C: partition.


And Windows XP ran happily ever after…

Disclaimer: I hope it’s obvious for everyone that several of the manipulations mentioned above are not without any risk and should only be performed when you know what you’re doing.  And not in a production environment.  And only when you’re desperate (well, maybe not really but it helps) :-)


Tags: ,

For those who haven’t heard about SQL Server 2008 R2 yet, have a look at its home page:

Download the August CTP to give it a go!

After having had a look at the available documentation in the Books Online, which at this moment is pre-release obviously (R2 is planned for the first half of 2010), following is a list of new stuff that caught my interest.  Well, besides the major new components such as Project Gemini or the Self Service Business Intelligence capabilities.  These are not yet included in this release anyway, you’ll have to wait for the Fall 2009 CTP for that.

Reporting Services


That’s right, maps!  Isn’t that cool?  Now we’ll finally have a way to visualize our two new spatial data types which we have available since SQL Server 2008 (geometry and geography).

These maps will be available in Report Builder 3.0 as well as in SSRS projects in BIDS.  If you want, you can try out Report Builder 3.0 without downloading SQL Server, it’s a separate download in the page mentioned earlier.

This is what the new report template looks like when first opening the new Report Builder:

New report in Report Builder 3.0 shows the Map icon

Indeed, it now shows a third object icon called Map.  Dan English wrote a detailed blog post about the Report Builder 3.0 installation procedure, in case you want to see the wizard before actually running it yourself.

Furthermore, in this tutorial Robert Bruckner shows you how to use the map visualization functionality using Report Builder 3.0.

Lookup functions

Finally, here’s the answer to the "Want to link datasets? Please vote!” article.  Well, not exactly but at least it’s a first step.

The following 3 new functions will be available for us to use in expressions in our reports:

These lookup functions will allow us to fetch data from dataset2 and display it within a data region that’s bound to dataset1.  Of course it’s not as convenient as when it would be possible to join two datasets together but it does overcome the limitation of not being able to display data from more than one dataset in the same data region.  Definitely looking forward to trying it out!  Here’s a blog post by Teo Lachev where he shows how it works.

Report Manager Enhancements

Look and feel plus user experience have gotten an upgrade here, such as "Eliminating the need to render a report before accessing and configuring report properties when in default view.".  Well, that saves us the click to switch to Detail View, as we’re used to doing.  Teo Lachev has posted some screenshots of the new Report Manager, which as you’ll notice has become more “SharePointy”.

Analysis Services

Windows XP Not Supported

According to the release notes it’s not fully operational on XP.  I do hope that this gets fixed by RTM?  More info in the Release Notes.

Database Engine

The relational database engine didn’t get any major functionality changes but I still noticed the following.


The number of CPU cores that a server can use for database operations has been increased from 64 to 512.  Well, I sure would like to try that feature out!  If you’re reading this and have got a spare computer with, let’s say 128 CPUs please do contact me and I’ll give you the shipping details :-)

Unicode Compression

Unicode data that is stored in nvarchar(n) and nchar(n) columns is compressed by using an implementation of the Standard Compression Scheme for Unicode (SCSU) algorithm.  Could be interesting.  I’m always in favor of saving space if it doesn’t cost in performance.  But there is a limitation: Data values that are stored off row or in nvarchar(max) columns are not compressed.
More details:

Now if you’ll excuse me, I have some downloading to do! :-)


Tags: , , ,

« Older entries

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