Friday, March 20, 2015

Tips & Tricks

Last week I saw Cathrine Wilhelmsen's (b|t) slides of her session at SQLBits XIV: Tools and Tips: From accidental to efficient DWH developer. If you haven't watched the session, go scroll through the slide deck at slideshare right away! It was really useful and though I already knew several of the tips, I got some new ones out of it that I'm using on a daily basis now.

I especially liked the Query shortcuts, where you can set specific queries under e.g. CTRL + [3-9]. If for example you set SELECT TOP 1000 * FROM under CTRL + 3, that way you can select the first 1000 rows of a table by only selecting the table and pressing the shortcut.

I'd like to add a few tips & tricks of my own, mostly being keyboard shortcuts I frequently use.

In SSMS, with nothing selected you can use the following:
  • CTRL + C selects the whole row at your cursor
  • CTRL + X cuts the whole row at your cursor (also works with SHIFT + DEL)
  • CTRL + V pastes the just copied or cut rows at the cursor (also works with SHIFT + INS)
Other tips:
  • CTRL + Click anywhere in a word selects the whole word. If you move the cursor after clicking (and still holding CTRL) you can select more words or even sentences
  • (SHIFT +) F6 scrolls through the available panes of the current tab taking the following into account: current tab, results, messages, execution plan, client statistics.
  • CTRL + SHIFT + U changes the selection to UPPER CASE
  • CTRL + SHIFT + L changes the selection to lower case
  • As Cathrine mentioned: CTRL + U changes the database while you're typing, but it can also be done with ALT + D. I like this shortcut a little more because it can be done with one hand in case you're mousing at the same time.
I hope this was useful to you and you maybe learned something new!

Friday, February 27, 2015

Overlay Icons Not Showing Up In Explorer

I came across this issue, when using Dropbox or SVN for example (or every other source control tool) that once in a while the overlay icons, or ShellIconOverlayIdentifiers as they are officially called, of those tools are suddenly not working anymore. Restarting the Explorer doesn't work.

It seems that those icons have registry entries, so we can look at how many items we have in there and see if we are hitting the limit of 15. Why 15? No idea.. Some Windows developer brought this up a long time ago maybe? :)

Open Regedit (if you don't know how to, then you shouldn't be editing it), and go to
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\ShellIcon‌​OverlayIdentifiers or
  • HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\explor‌​er\ShellIconOverlayIdentifiers
I currently have the following items in my registry:

I edited the Dropbox-items so they are always on the top of the list. OneDrive and GDrive also both have overlay icons, so the limit of 15 can quickly be reached.

If there are more than 15 items, then either uninstall or reconfigure applications that are shown there or remove the registry entries manually (backing them up of course).

After you have made some changes in the registry be sure to restart the Explorer and you're good to go!

Friday, January 23, 2015

NetFx3 Error While Installing SQL Server 2012 SP1 on Windows 8 VM

A while ago I was looking for a VM with SQL 2012 installed on it (and all the other BIDS and pieces :) because I was about to start learning for my MCSE and needed some serious SSAS training. Because I couldn't find one I decided to create one myself from a VM with Windows 8 on it. I did run into some troubles however..

I was going to install all the features of SQL I could possibly need, so also MDS, DQS, IS, RS, etc. After configuring everything the install started, but I rather quickly hit the first bump:

It did continue after that, but only SSDT was successfully installed as you can see.

Apparently, the install couldn't automatically enable the .NET Framework 3.5, so let's do it manually following the next screen shots.

Windows will download the files if an internet connection is present.

But unfortunately.. The install fails once again. No idea why..

Nothing left than downloading and installing it myself from the Download Center.

And finally, it installed successfully!

Now back to the SQLServer install and my study! :)

Friday, January 16, 2015

Oracle Linked Server Error 7354: Data Type Not Supported

A few days ago I was trying to get some data from several tables from an Oracle server. A linked server was already created with the name SERVERA and I used four-part naming in the query.
The data of several tables was already imported successfully when I encountered the following error message for one table:
Msg 7354, Level 16, State 1, Line 1The OLE DB provider "OraOLEDB.Oracle" for linked server "SERVERA" supplied invalid metadata for column "WMBS_TIMESTAMP". The data type is not supported.
After excluding this column I still got the same error message, so I turned to Google and arrived at MS support.

Message 7 at the "Common error messages and how to troubleshoot them" section brings me a bit closer as it seems to address my issue.

It brings me to the following Microsoft Knowledge Base article: 243027. However, as you can read in the name of the KB: it handles Numeric columns in Oracle, while my column is a TIMESTAMP. That leads me to the definition of an Oracle TIMESTAMP data type:

The default is 6. So there are 6 digits of fractional seconds in the Oracle part of the data. However, SQLServer only has defaults of 3 (DATETIME) and 7 (DATETIME2, DATETIMEOFFSET). I think the query engine tries to convert it to a DATETIME2 and fails because of the default precision.

To solve this I used OPENQUERY in which I specified the query and explicitly CAST the value of the specific column to a DATETIME2 data type.
    FROM Schema.Table 

Thursday, January 15, 2015

Rule "Same architecture installation" Failed When Installing 64-bit SSDT for Visual Studio 2012

Just a quick post on an error I encountered when installing SQLServer Data Tools - Business Intelligence (SSDT-BI) for Visual Studio 2012.
The installation wouldn't even start because of a failing rule check:
Rule "Same architecture installation" failed
The CPU architecture of installing feature(s) is different than the instance specified. To continue, add features to this instance with the same architecture.
Make sure you select “New instance” on the installation dialog if you have SQL Server x64 installed. Otherwise, you probably get the error stated above!

Thursday, January 8, 2015

2014 in Retrospective and Planning for 2015

Well, that's it for 2014 then, it's been a year already. And we're already on our way in 2015. I've encountered several ups and downs last year, with Niels being the best of course!

I'm actually not that good in setting or reaching my goals, so I thought of writing them down, so I (or anyone who reads this) can at least hold me to it afterwards. :-)

I wanted to do more blogging last year. Well.. that turned out differently :) I just couldn't find the time or inspiration. I did have some periods in which I was somewhat active, but I need to get a more regular schedule. So, starting realistic and achievable, my goal is to get at least 1 post per month out in 2015 and also get my first #tsql2sday post.
And looking further on the horizon, I do some have speaking intentions, I just don't know when or what to talk about yet. I would like to explore this in 2015 a bit further.

It's been a while ago that I started my learning path to MCSE BI. Due to private matters my time dedicated to learning hasn't been that high in 2013 and the first months of 2014. Starting in March I dedicated some more time and finished 70-461 in April. In December I also passed 462 and 463 and so I finally got my MCSA! My goal is to get my MCSE in 2015, preferably before September.

SQLSaturday Holland
I also attended my first SQLSaturday event in Utrecht last October, meeting a lot of nice new #sqlfamily: Catherine, Clint, Grant, Jeffrey, Julie, Pieter and also renewing the friendship with old ones: Jen and Joost. It was a great event and I had lots of good sessions to attend. I especially want to follow up on Continuous Integration by Grant Fritchey (b|t). He also wrote a good article on SQLMag on this same topic.

I'm also a fan of running and am finally training more in the last months (or at least trying to..). And now, I have set my mind on running a marathon with my friend Joost. Yes, the whole nine (or actually 46145) yards. The location still has to be determined. I already completed several half marathons, with the fastest last October in 1h45m.

Enjoy the year 2015 y'all!

Friday, December 26, 2014

MCSA SQL Server 2012 and Best Wishes

I can finally announce that this Wednesday, just before Christmas, I nailed the 70-463 exam. About 3 weeks ago I already passed 70-462, so I am now qualified as MCSA SQL Server 2012, whohoo! :-)

And now on to my MCSE BI.. But not before 2015 starts.. Let's take a few days of family time to relax and start the learning experience again in January!

I wish everybody a merry and joyful Christmas (yes, we have 2 of them days over here :) and see or speak to you in the new year!

Tuesday, December 16, 2014

DSM 5.1-5021 Update Release Notes

This morning I found an update for my Synology DS214se. I got the release notes from You can get yours here. Select your model and click on Release Notes.

Updates to the different app "Stations" (Video, Audio, Download, etc) are also available in the package center.

DSM Version 5.1-5021

What's New

  1. This update includes all bug fixes as well as security fixes in the previously released critical updates since DSM 5.1-5004.

Fixed Issues

  1. Enhanced data security by disabling "Mount automatically on start-up" after restoring an encrypted shared folder.
  2. Fixed a vulnerability that allows senders to access unintended destinations during a backup job.
  3. Enhanced stability on Amazon S3 on large file backup.
  4. Upgraded OpenVPN to 2.3.6 to address a security vulnerability.
  5. Fixed a vulnerability that allows local users to initiate a denial of service by queuing the maximum number of file descriptors (CVE-2014-7824).
  6. Upgraded PHP to 5.5.19 to address multiple vulnerabilities (CVE-2014-3669, CVE-2014-3670, CVE-2014-3668, and CVE-2014-3710).
  7. Improved DTV dongle compatibility.
  8. Improved file-copying stability on DS2015xs.
  9. Fixed an issue where DS213+ and DS413 could power up unexpectedly when backup tasks are scheduled.
  10. Minor bug fixes.

Thursday, December 4, 2014

(Not) Using CTRL+ENTER to send e-mail with Outlook

I'm always in favour of keyboard shortcuts and hotkeys to make life easier.
However, sometimes they're not the nicest thing to have, especially when you're in the middle of typing an email and you accidentally press CTRL+ENTER instead of SHIFT+ENTER...

Aaaaand it's gone

When you enable the CTRL+ENTER option in Outlook, you get the following popup when using it for the first time.

From here, you have 3 options:

  1. Outlook always sends the message (select the box and press Yes)
  2. Outlook never sends the message (select the box and press No)
  3. Outlook should always ask you what to do (do not select the box and choose Yes or No)

However, when you (accidentally) took option 1 or 2 and want to revert that so you can choose 3 again, you're kinda stuck. Disabling and re-enabling the option under "File > Options > section Mail > option group: Send Messages > option: CTRL + ENTER sends a message" doesn't show you the pop-up anymore.
Ofcourse, there are other options: you can edit the registry to delete or modify the value chosen for CtrlEnterSends in the Outlook preferences:
Key: HKEY_CURRENT_USER\Software\Microsoft\Office\<version>\Outlook\Preferences
Value name: CtrlEnterSends
Value type: REG_DWORD
Value data: 0 = Outlook will not send when pressing CTRL+ENTER
                   1 = Outlook will send when pressing CTRL+ENTER
                   2 = Outlook will prompt you when pressing CTRL+ENTER
When this key is missing, Outlook will behave as if its value was set to 2.
Hope this helps!
I found this solution on:

Wednesday, August 27, 2014

Can't Find Chrome 64-bit Version Download?

It's not that you're not looking good enough. Just move to the United States and all will be solved...

~30 minutes earlier
So I wanted to get the 64-bit stable version of Chrome and as several websites point out, you'll have to get it yourself explicitly because it won't update to the 64-bit version. Well that's fine, I just go the the website I was pointed to:
Unable to see the specific link to the 64-bit version I just hit the Download button and hope I get the chance to choose between 32- and 64-bit later. Well, that's not going to happen.
 After searching for a few minutes (yes, in Chrome...) I keep coming back to the same page and not seeing anything about a 64-bit version of chrome.
Then I scroll down the site and see a little language-selection drop-down (or actually a drop-up).
When I set this to English - United States a download link magically appears beneath the Download button... After checking, all the English languages seem to work, the other are not working.

Thanks for that Google.