Sunday, May 27, 2012

Scheduling a SQL Agent Job to Run on a Calculated Day that isn’t included in the Regular Scheduler Options

I’ve had a hard time coming up with a title for this post, because while the concept is easy to understand, it’s difficult to summarize.
We refresh one of our development instances on the first Monday of each month.  That’s an easy thing to schedule, as it’s an option within the SQL Agent scheduler:
We want to send a notification to the affected users beforehand, however, and since the refresh occurs in the wee hours of the morning, we can’t send out that alert on Monday.  It has to occur on the Friday before the first Monday. 
That, friends and neighbors, isn’t a built-in option.
It’s a little trickier (though in the end, not terrible) because the Friday before the first Monday could be in this month or in next month.

Friday, May 25, 2012

Pasting Code to OneNote 2010 with Formatting and Colors

[Update 1/2915]
Head over here for a solution for OneNote 2013.  Annoying, but it works!

I use OneNote as a SQL script repository, and in most respects it’s a great solution:  it’s searchable, the notebooks make organizing easy, and its integration with SharePoint (and SkyDrive) is pretty good. There’s one problem, though, that has made it less than ideal:  color formatting doesn’t come through from SSMS. Notepad++ is my go-to editor for long scripts (if you’ve not used it, you should check it out), and it has what ought to be a perfect combination of plug-ins for the job:
  • First is the excellent Poor Mans’ T-Sql Formatter.  This is a first-rate SQL formatter that integrates with SSMS and Notepad++.
  • Second is NppExport (included with NPP), which allows you to copy the selected text to the clipboard as HTML.  Unfortunately, OneNote doesn’t really handle HTML (even!) very gracefully:  NppExport html is rendered by OneNote without any line breaks.  It should be noted that this appears to be a limitation in the rendering that OneNot does, not in the HTML generated by NppExport.
Rant aside, please allow me to say that *this should work!*  I’ve been on a crusade to figure out how to paste my code from SSMS to OneNote while retaining the color formatting, and it’s taken awhile to find a good solution. After a lot of searching, I ran across NoteHighlight on CodePlex.  The instructions (and the installer) are written in Chinese, so the hyperlink here is to the Google Translation link to that page.  I have this to say about it:  It. Works. Well. In OneNote 2010.  If you're using ON 2007 or (like me, now) ON 2013, I'm afraid there's still not a good option.

Here’s what my code looked like when pasting formatted SQL from NppExport:
I'm guessing, by the way, that this is a result of NPPExport using the White-space:pre tag in the exported HTML (css, really).  It would appear that ON doesn't do full HTML.  See this connect item for an upvote on fixing that.

Update:  Microsoft has removed Connect options for OneNote, so there's no longer a good way to submit or track bugs.

Here is the same SQL when pasted from NoteHighlight: image
Better, huh?  Note that the colors aren’t the same:  NoteHighlight actually does some parsing of the code and does its own coloring.  You can choose from a variety of syntax color schemes.

Update 8/26/12:  Bad news for folks using ON 2007:  this only appears to work with OneNote 2010.  Thanks to +Edelman for pointing that out.  The NoteHighlight page on Codeplex appears to be pretty static, too, so I wouldn't hold out a lot of hope for feature requests and the like.