I have been using this macro for years. I have rewritten it several times. I find it to be quiet useful. It is comprised of four subroutines. The first two are the work I need done. The third evaluates the conditional that work is predicated on and calls the 2nd or 1st and 2nd. The 4th calls the 3rd and is bound to a keyboard shortcut in order to decouple the execution from the keyboard binding.

The first subroutine is called date header. As the name suggests it inserts a date header. I use header one for the date. For the purpose of keeping a journal, this is useful as it displays at the top level of the navigation pane (which has a checkbox on the view tab).

Sub DateHeader()
'
' DateHeader Macro
' Insert H1 Current Date
'
    Selection.Style = ActiveDocument.Styles("Heading 1")
    Selection.InsertDateTime DateTimeFormat:="dddd, MMMM dd, yyyy", _
        InsertAsField:=False, DateLanguage:=wdEnglishUS, CalendarType:= _
        wdCalendarWestern, InsertAsFullWidth:=False
    Selection.TypeParagraph
End Sub

The second subroutine is called time header. This subroutine inserts the current time in header 2. This appears subordinate to the date which is in header 1 on the navigation pane. This allows collapsing the navigation pane to the date level.

Sub timeHeader()
'
' timeHeader Macro
' Insert current time in H2
'
    Selection.Style = ActiveDocument.Styles("Heading 2")
    Selection.InsertDateTime DateTimeFormat:="h:mm am/pm", InsertAsField:= _
        False, DateLanguage:=wdEnglishUS, CalendarType:=wdCalendarWestern, _
        InsertAsFullWidth:=False
    Selection.TypeParagraph
End Sub

The third is called find today. This macro searches for an existing current date header and inserts one if there isn’t one. It also inserts the time. The same work can be accomplished with 4 fewer lines of code. Comment if you see it.

Sub FindToday()
'
' FindToday Macro
' This macro searches for the existence of the date header and inserts it at the end of the document if it is not found.
' If the date header is found it inserts the time at the end.
Dim vToday As String
vToday = Format(Now(), "dddd, MMMM dd, yyyy")
    Selection.Find.ClearFormatting
    Selection.Find.Style = ActiveDocument.Styles("Heading 1")
    With Selection.Find
        .Text = vToday
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    
    If Selection.Text = vToday _
    Then
Selection.EndOf Unit:=wdStory, Extend:=wdMove
Selection.TypeParagraph
Application.Run "timeHeader"
    Else
Selection.EndOf Unit:=wdStory, Extend:=wdMove
Selection.TypeParagraph
Application.Run "DateHeader"
Application.Run "timeHeader"
  End If
    
End Sub

The third subroutine is call run ctrl alt D. This decouples the key binding from the execution. It affords the option to run additional or different subroutines for the ctrl alt D keyboard combination (this can done via a dialog launched by the customize button under the commands pane of the customize ribbon dialog of the options window launched from the home/file tab). I can also delete this macro if I need to start using that keyboard shortcut for its default purpose which is to insert a footnote reference to the current cursor location.

Sub runCtrlAltD()
'
' runCtrlAltD Macro
' Replace insert footnote with macro execution.
'
    Application.Run MacroName:="FindToday"
End Sub

In my last post I made it a point to laud the Gestures app for Window 8.1 (Lumia Denim). In this post I am going to briefly describe an issue I have with Quiet Hours. Quiet Hours is a feature to Cortana that allows you to configure just how silent you want silent mode to be and when. You can turn Quiet Hours on Right Now, at specific times, or when one or several calendars have an appointment marked as busy. You can also configure rules for breakthrough (e.g. if it is a member of the “inner circle”, or they call 2 times in 3 minutes). You can also configure a response message to your callers indicating you are unavailable.

I would like to see the option to allow breakthrough for the 1st call or for contacts associated with an appointment. My reason for this is that I mark appointments as busy so that I do not get interrupted on an important call. The problem comes when my important calls are for contacts not in my inner circle and they are calling me. If they don’t call me before the time the appointment is set for, they get blocked.

Earlier today I was watching a video from The Great Courses series. The lecturer was Arthur T. Benjamin of Harvey Mudd College. He was discussing strategies to solve a 3 x 3 lights out puzzle. It inspired me to write a T-SQL version of the game.

USE Experiment

/** Thanks to Professor Arthur T. Benjamin of Harvey Mudd Univserity for the inspiration. */

IF OBJECT_ID(‘lightsout’) IS NOT NULL DROP TABLE lightsout;–Technically Merlin.

CREATE TABLE lightsout

(

column1 BIT,

column2 BIT,

column3 BIT,

rownumber INT IDENTITY(1,1)

)

GO

We’ll use a trigger to enforce the one button push rule as well as to update the adjacent cell with their bitwise negation.

CREATE TRIGGER onebutton ON lightsout

INSTEAD OF UPDATE

AS

BEGIN

–Trigger to assure a single button push

IF @@ROWCOUNT > 1 OR

(SELECT TOP 1

ABS(

CAST(d.column1 AS INT)-CAST(i.column1 AS INT)

+ CAST(d.column2 AS INT)-CAST(i.column1 AS INT)

+ CAST(d.column3 AS INT)-CAST(i.column1 AS INT)

)

FROM deleted d

INNER JOIN

inserted i

ON d.rownumber = i.rownumber

) > 1

BEGIN

PRINT ‘Rule Break.’

ROLLBACK

END

ELSE

–Triggers to update on update

IF (SELECT TOP 1 rownumber FROM deleted) = 1

BEGIN

IF (SELECT TOP 1 deleted.column1 ^ inserted.column1

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber) <> 0

BEGIN

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 1;

END

IF (SELECT TOP 1 deleted.column2 ^ inserted.column2

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 1;

END

IF (SELECT TOP 1 deleted.column3 ^ inserted.column3

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 1;

END

END

IF (SELECT TOP 1 rownumber FROM deleted) = 2

BEGIN

IF (SELECT TOP 1 deleted.column1 ^ inserted.column1

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 3;

END

IF (SELECT TOP 1 deleted.column2 ^ inserted.column2

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 3;

END

IF (SELECT TOP 1 deleted.column3 ^ inserted.column3

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 1;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 3;

END

END

IF (SELECT TOP 1 rownumber FROM deleted) = 3

BEGIN

IF (SELECT TOP 1 deleted.column1 ^ inserted.column1

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 3;

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 3;

END

IF (SELECT TOP 1 deleted.column2 ^ inserted.column2

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column1 = ~(l.column1) FROM lightsout l WHERE rownumber = 3;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 3;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 3;

END

IF (SELECT TOP 1 deleted.column3 ^ inserted.column3

FROM deleted

INNER JOIN

inserted

ON deleted.rownumber = inserted.rownumber)<>0

BEGIN

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 3;

UPDATE l SET column3 = ~(l.column3) FROM lightsout l WHERE rownumber = 2;

UPDATE l SET column2 = ~(l.column2) FROM lightsout l WHERE rownumber = 3;

END

END

END

GO

Let’s insert some random bits.

–Random bits

INSERT INTO lightsout

SELECT CAST(ROUND(RAND(),0) AS BIT) column1

,CAST(ROUND(RAND(),0) AS BIT) column2

,CAST(ROUND(RAND(),0) AS BIT) column3

UNION ALL

SELECT CAST(ROUND(RAND(),0) AS BIT) column1

,CAST(ROUND(RAND(),0) AS BIT) column2

,CAST(ROUND(RAND(),0) AS BIT) column3

UNION ALL

SELECT CAST(ROUND(RAND(),0) AS BIT) column1

,CAST(ROUND(RAND(),0) AS BIT) column2

,CAST(ROUND(RAND(),0) AS BIT) column3

Today’s email newsletter from Brent Ozar linked Flowing Data’s best of 2014 data visualization. Hidden among the bar charts and rose diagrams and the New York Times standard stunning collection of visualizations was Tyler Vigen’s Spurious Correlations. I am in awe, from the time that I found excuses not to correlate the population of wild asses and election results for a contest Tableau was sponsoring until I saw a graphic using Godzilla to compare military spending by country (guess who is Godzilla and who is a midsize high rise) I have been enamored by data visualization, and even more so when it is done to humorous effect. I particularly disturbed by possibility that someone might be particularly disturbed by the fact that for every billion dollars in sales at General Mills 2.3-2.5 women in California die by slipping and falling.

http://tylervigen.com/view_correlation?id=28625

My last post touched on a specific SQL Server task and how a reduction of permission footprint can be realized by using SSIS instead of T-SQL. This is often the case with cross-server operations. The post also touched on my blogging habits. Who are we kidding I shoehorned SQL in at the end. I love SQL, of course I love problem solving more. SQL happens to be the tool that presents me with a cornucopia of problems I can solve. Without problems to solve I am often at a loss for the fuel of blog posts, of course with problems to solve I frequently have an NDA.

My last post also touched on the simplification that we need to focus on to make ourselves more accessible.

Another year is drawing to an end. That means it is time to review the successes and failures on my list of resolutions. It is also time to make new ones. On the plus side I have already met my commitment to at least one blog post per month average. I won’t have to put up a flurry of articles in the next week and a half to meet that resolution. I think I should modify my resolution next year such that I need to post more regularly.

Reviewing my posts I have noticed that I am following that advice that I post about what interests me.Of course, I have been told that I can be interested in things that don’t necessarily interest others. It’s not that people aren’t interested in database administration or SQL server; It’s that most people aren’t interested in more abstruse topics. At a recent board meeting of the Philadelphia SQL Server user’s group we were discussing speakers and topics. We wanted to be clear that we are seeking to bring in speakers that will bring our members to meetings.

To that effect I am going to stipulate for the coming year’s blog posts that I post about more accessible topics. I am also going to make it a point to articulate why my sometimes more advanced seeming efforts are not. One example comes to mind.

When you are trying to set up an automatic restore job and you want that job to start when the backups have finished. In order for a job to be started via a linked server the linked server account must own that job (it might also need to be a member of the target server role modified to be granted execute on sp_startjob). There must also be a connection such that it can read the backup tables to find the placement of the backup files. XP_dirtree of cmdshell can also be used to list existing files but they won’t fail the way attempting backup files that don’t exist (perhaps they got deleted, it would behoove you to notice this). If we were to use SSIS it would only need to be db_creator on the restore machine and db_reader on msdb on the backup machine, as well as have read file system permissions for the location of the backup. This is a smaller service area than would be needed to start remote jobs and read remotely, especially as it would be a single Windows login as opposed to two SQL Logins, and at least one Windows login (more if the SQL Agent account is not a domain account used by both machines).

If you didn’t know it tech is rampant and wonton in its generation of buzzwords for all sorts of things, or more specifically for sexing up all sorts of things that are more than the sum of their parts. The singularity, Web 2.0, e-this, i-that, social, mobile, the cloud, big data, and the Internet of Everything (Things) to name a few. Sometimes these things overlap. Sometimes they overlap in a so many ways that a Venn diagram starts to need an expert algebraic topologies to explain. Lately, the buzz has been around the cloud, big data, and the internet of things. As a technology professional and in particular a database professional it is important to me to sort through the really cool things.

One of the things we now have an is internet of the light bulb. This is real unlike the hyper text coffee pot protocol which is a 16 year April fool’s joke the IETF created, or the Linux toaster which is a malware vector. Phillips  Hue is a light bulb (system) that represents the culmination of incremental improvements to lighting bringing together a number of preexisting lighting features and adding them to an API. Hue combines, timers, dimmers, color, and circuit switching to provide a system that allows us to further take for granted one of the greatest technical achievements of the last millennium. That of course is the internet of things, at least the command side of it. I wonder if people have it in them to take their beverages according to a queue, or perhaps a locational aware protocol that starts my coffee when I approach the break room during a certain period of time.

Other things there are internets of will have sensors. These will write data somewhere. Lately, it looks like some Hadoop file system will be the standard. I wish I were more abreast of the internals the way a storage engineer would be so I could form a better opinion of why HFL would be a better choice than WAFL for writing streams. I suspect it has to do primarily with Map Reduce (at least public domain Map Reduce) only being implemented on Hadoop and MongoDB (and …). Of course if we need to respond to events in real time we need to put our event triggers upstream from storage.