Script Batch XMLA Commands Using T-SQL

Very recently I had to delete a 600 partitions from a single measure group in a cube. This is a high number of partitions, but to make matters worse, it was 600 random partitions out of 1,000 or so partitions already there. So, I could’ve spent the best part of a day picking through which partitions to delete, or I could use SQL to script out the xmla for me and then execute the xmla manually. Ideally I would have done this in PowerShell. And I probably will if I have to do something like this again. But seeing as it is a one shot, I decided to write it in T-SQL. At any rate, I’ve posted the T-SQL below. Continue reading

Drop and Create all Foreign Keys The Elegant Way

Yesterday I posted a script that will drop all of the foreign keys in your database in a very inelegant, but super effective way. Today’s script is slightly more sophisticated in that you can print out the CREATE statements for the foreign keys before running the drop. The script will print out the commands rather than running them, so you can script them out and run them whenever you want.

Continue reading

Changing Passwords On Nested RDP Sessions

Been working from home lately, and this morning I needed to alter my password to a RDP session inside another RDP session. So I needed to find a way to press CTRL+ALT+DELETE without the first session receiving it. I had hoped that the control panel might help, but sadly not:

2014-10-10 11_45_01


One suggestion that worked for me is to use the on screen keyboard (OSK, osk.exe).

2014-10-10 12_42_24

But don’t press CTRL+ALT+DEL on the OSK exclusively. It does not work. You have to press CTRL+ALT on your physical keyboard and then press DEL on the OSK in the nested RDP session.

Clustered Columnstore Index Hotfix Released

A little under a week ago on the 25th of September a hotfix (2999809) was released that pertained to poor query performance when a query contains table joins in SQL Server 2014.

The poor performance may show up if all the following apply:

  • You query contains an inner join
  • both tables have a Clustered Columnstore Index
  • There is a hash join executing in batch mode
  • the plan decides that a bitmap filter will be selective enough to be useful
  • the bitmap filter used is a complex bitmap filter (filter is made up of multiple columns and data types, whilst simple is a single column filter)

Continue reading