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.