Open Live Writer On GitHub

A few days ago an open source fork of the Microsoft Windows Live Writer code was announced. It is now called: OpenLiveWriter. It makes it easy to write, preview, and post to your blog. The current release has version 0.5.

As I host my blog on Blogger I encountered some issues when I wanted to login to the application while installing it. According to the issue tracker at GitHub this was a known issue and is being dealt with in the next minor release 0.5.1. I hope they are able to fix this soon as I'm eager to try it out!

Have a look at the project on Github or see what they have in mind for next releases at the roadmap!

Round a datetime to the nearest minute (or down to the minute)

Last week I was doing a comparison of 2 datetime fields, but I only wanted to do the comparison on the minute level. So I needed something to round the datetime to the nearest minute or down to the minute.

I found this great answer on SO:

DECLARE @dt DATETIME
SET @dt = '09-22-2007 15:07:38.850'
SELECT DATEADD(mi, DATEDIFF(mi, 0, @dt), 0)

And if you want to round up or down, you can add 30 seconds to the parameter like this:

SELECT DATEADD(mi, DATEDIFF(mi, 0, DATEADD(s, 30, @dt)), 0)

SSMS Import and Export Wizard: 32 or 64-bit?

Ever got that annoying error?

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Good chance you're running the 32-bit version of the Import and Export Wizard and want to import an xlsx-file. You could solve it by saving the file as an xls-file (the Excel 97-2003 Workbook version). Or you can also run the 64-bit version of the wizard.

If you right click a database and select Tasks > Import Data… you automatically start the 32-bit wizard.

Start >> All Programs >> Microsoft SQL Server <version> >> SQL Server <version> Import and Export Data will start the 64-bit version of the wizard.

HTH.

Don't Always Blame The Data

Just a quick post on this error I received yesterday. 

While I was working at a client, the application manager made some changes to a Slowly Changing Dimension of type 2 with effective dates, as mentioned here. This dimension contains the organisational units from the client.

A little while later a part of the financial report I made, was broken. After executing the report (or actually the procedure of the dataset in SSMS) it gave me the following error message:

The maximum recursion 100 has been exhausted before statement completion.

Although this answer on SO mentions the MAXRECURSION option at the end of the CTE, that doesn't solve my problem. The poster mentioned he had a circular reference in his data, what could cause my report to break also.

After investigation of the dimension I couldn't find any problems so I was back at the report and looked at the procedure of the dataset. I started executing parts of the dataset and it turned out to be a code issue instead of a data issue:
The anchor part of the recursive CTE that creates a hierarchical tree of the organisation didn't take into account the effective dates of the SCD and therefore creating a circular reference between two versions of an organisational unit. It kept on trying to build the tree before failing until the 100th recursion, as mentioned in the error message.
My solution was to add the effective dates of the SCD to the CTE and after that the procedure and the report ran fine again. Everyone happy :)

Power Pivot Error While Upgrading a Workbook

I recently tried upgrading a workbook to Excel 2013 from an older version (I believe 2010).

Upgrade message

It succeeded (or at least it looked like it succeeded), but I needed to save, close and reopen the workbook according to Excel. 

Upgrade Data Model

Close and reopen workbook

Saving of the document failed with the below error message and then the Power Pivot Add-in was corrupt and the whole tab was even gone.

Error message

Disabling/enabling the Add-in didn't work, I got an error message saying the add-in cannot be loaded and Office Repair needs to run.

Office (Pro Plus) Repair ran successfully, it only needed a reboot after which the problem was solved. I still have no idea what caused Excel to blow up like this.

The second try of upgrading the workbook did work correctly and now I could save and reopen it and go on with my business! :-)

T-SQL Tuesday #66: Monitoring

This month's T-SQL Tuesday is being hosted by Cathrine Wilhelmsen (b|t), and the topic is as follows: Monitoring.

I thought I'd finally give it a go by joining the tradition of T-SQL Tuesday. The last months or so I kept saying to myself: this topic is not particularly suited for me, or I don't have the time right now.. As most people say about blogging: not starting is usually not blogging at all, so here it goes!

Since I'm a SQL/BI developer I'm not overly into monitoring servers or SQL-instances, but after thinking about it I do have some other things to monitor during my day-to-day tasks.

SSIS
I tend to execute quite a few packages and jobs during the day. I monitor them via the Standard Report option of the SSISDB Catalog in SSMS as that's the easiest way. Following naming conventions for my packages, tasks and components I can quickly address issues and see where things go bad.

SQL
I also regularly take a look at the servers that are running my solutions to keep everything going smoothly. Until now it's mainly been reactive, e.g. when users start complaining about slow reports or dashboards.
I use the below query for finding the top slow queries that have run lately.

SELECT TOP 20 SUBSTRING(qt.TEXT, (qs.statement_start_offset / 2) + 1, (
   (
    CASE qs.statement_end_offset
     WHEN - 1
      THEN DATALENGTH(qt.TEXT)
     ELSE qs.statement_end_offset
     END - qs.statement_start_offset
    ) / 2
   ) + 1) AS SQLStatement
 ,qs.execution_count
 ,qs.total_logical_reads
 ,qs.last_logical_reads
 ,qs.total_logical_writes
 ,qs.last_logical_writes
 ,qs.total_worker_time
 ,qs.last_worker_time
 ,qs.total_elapsed_time / 1000000 total_elapsed_time_in_Sec
 ,qs.last_elapsed_time / 1000000 last_elapsed_time_in_Sec
 ,qs.last_execution_time
 ,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Besides getting slow queries that have already run, it sometimes happens that a DWH-job is still running in the morning when it should've been completed looong ago :). Running sp_who2 or sp_WhoIsActive by Adam Machanic (b|t) usually gets me to find the reason why it is still running and what the problem is.

Hopefully I can continue to add more posts to the T-SQL Tuesday tradition. Have a nice day and keep sharing!

What to do when SVN cleanup fails

I ran into an issue with TortoiseSVN earlier this week, where the regular cleanup didn't finish correctly anymore, so I had to resort to other methods. Searching for a solution I came up with several possible solutions.

Found at StackOverflow:

1. Delete the .svn directory for your working copy.
2. Start a new checkout in a new, temporary directory.
3. Cancel the checkout (we don't want to wait for everything to get pulled down).
4. Run a cleanup on this cancelled checkout.
5. Now we have a new .svn directory with a clean database (although no/few files)
6. Copy this .svn into your old, corrupted working directory.
7. Run svn update and it should bring your new partial .svn directory up to speed with your old working directory.

That's all a little confusing, process wise. Essentially, what we're doing is deleting the corrupt .svn then creating a new .svn for the same checkout path. We then move this new .svn to our old working directory and update it to the repository.

However, this morning the above solution also didn't work and I resorted to the following steps (a similar solution found at SO):

1. Move the affected subdirectory out of the working copy. Move it somewhere else so it becomes invisible to SVN
2. SVN cleanup at the root
3. SVN update, which will recreate a clean subdirectory, with all the correct permissions.
4. Manually merge any changes to the subdirectory's files (copy the files affected) from the previously moved version. Do NOT copy back in any of the .svn control area.

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!

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!

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! :)

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.

tl;dr

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.

SELECT 
  OtherFields
  ,CAST(WMBS_TIMESTAMP AS DATETIME2)
FROM OPENQUERY(SERVERA,
  '
    SELECT
      OtherFields
      ,WMBS_TIMESTAMP
    FROM Schema.Table   ')

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!

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. :-)

Blog

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.

Certification
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.

Running

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!
Nicky