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.


Since the installation is in Chinese (documentation here), it’s a little hard for us non-sinophones to follow it.  Happily, the installation appears to allow for (mostly) default click-through.

Make sure you have the correct version: most of us run the 32-bit version of office, which is the second choice on the download page. If you install the wrong version, the plugin simply won’t appear in OneNote.

When you run setup, you’ll see a window like the one below:


This would appear to be a EULA. And in fact, if we paste this text into a translator, it appears to be the Microsoft .Net 3.5 EULA (more or less).  Sure enough if we click accept (A) (not the default), we see dotnetfx35setup.exe run.  So it’s checking to see if we’ve got .Net 3.5 installed.

Next we get the main installation window: image The default button (N) appears to be Next.

The next window allows, at the least, for you to pick the installation location: image
I’m guessing that the (M) and (E) choices refer to “Me” and “Everyone”.  As in “Make this available to just me or to everyone who uses this computer.”  I can verify that selecting the defaults worked just fine for me.

image Ready to install?

And complete.

Now, when I launch OneNote, I have new tab on the ribbon: image

You can see that you get a lot of language options.  Because I only care about SQL, I’ve included that button in the Quick Access Toolbar (circled) so I don’t have to switch over to the NoteHighLight tab to get to it. Now, you can click on the SQL (or other language of your choice) button and you’ll see a window like this: 
image The Style drop-down allows you to choose the color scheme you’d like to use.  I prefer the default colors for SQL. The Line Number option actually includes the line numbers in the text.  I don’t really like that option, as it keeps me from being able to copy and paste my scripts to SSMS.  But I can see where it’d be useful, sometimes.

When you click OK, your color-highlighted text is pasted into OneNote. Do note that NoteHighLight is a syntax highlighter, not a text formatter:  it’ll highlight your code nicely, but it won’t change the text layout at all. This is pretty much the only option I’ve found that isn’t a Visual Studio (not SSMS) plugin.  I’m pleased to have found it, and I’m grateful to the author.  I hope it’s useful to you, too.


  1. This comment has been removed by the author.

  2. I can verify that this does NOT work on OneNote 2007 :(

    1. Edelman, that's a bummer; I'm sorry to hear that's the case, tho I'm grateful you took the time to post your results here (and I'll be updating the post to reflect this).

      I had read, in all my searching, that ON 2007 does a better/different job of rendering HTML. Unfortunately, I don't have a copy of ON 2007 to test on at the moment; have you tried using Notepad++ to export as HTML (to the clipboard) and then pasting that into OneNote? I'd be curious to know if 2007 renders that differently than does 2010.

      In Notepad++ (after having selected the appropriate language so the syntax highlighting is correct), select plugins -> NPPExport -> Copy HTML to clipboard.

      In OneNote 2010 (and OneNoteMX in Windows 8), the syntax highlighting pastes just fine (colors are good), but line spacing gets lost. For sizeable scripts this, then, is a non-starter. I'd be curious if ON 2007 behaves differently.

    2. BTW, there's a connect item filed here: that could use an upvote as a bug.

      The problem, I think, is that OneNote doesn't render HTML as specified, namely: it ignores tags, which pretty much breaks everything you might be doing with scripts.

    3. Ahem. *ignores < white-space > tags.
      BlogSpot, apparently, doesn't ignore those. :)

  3. A painful work around for NppExport is first paste to MS Word then Copy/Paste to OneNote. It's sloppy but gets the job done if your coding language isn't supported by NoteHighlight.

  4. Thanks for the tip and step by step instructions.
    I think you should really highlight the 32-bit remark. I didn't even realize I wasn't running the 64bit MS Office, so when I installed, nothing happened. Considering it's in Chinese, it probably tried to tell me but I didn't understand.

    1. That's a good suggestion; I've highlighted it, perhaps a little better, now. Thanks a ton for the feedback!

  5. FWIW - I keep a blank Word doc open alongside OneNote. When I'm Copying and Pasting code, I copy it from SSMS, Paste it to Word, then Copy it from Word and Paste it in OneNote - Formatting maintained, and didn't have to install anything extra.

    Hope this helps...

    1. Thanks, Jim,
      Can you then paste that formatted text directly from ON into SSMS? My experience always has been that spaces or empty lines become nonprinting characters that SSMS can't parse, when the text has gone through Word to OneNote.
      If you've got a workaround for that, I think a lot of folks would be quite happy, since the above plugin only works on ON 2010...

      Appreciate your chiming in here! -Lane

    2. Sorry for not getting back sooner...

      It all works pretty seemlessly for me - copying from SSMS, thru Word, to OneNote, as well as copying from OneNote and pasting directly back into SSMS. Spaces and blank lines are maintained. In fact, I don't see any change in formatting.

      I should note that I'm currently working on SSMS 2012 and Office 2013, but I believe I was doing this when my setup was SSMS 2008 and OneNote 2010 as well.

      - Jim

    3. Hi, Jim,
      Thanks for following up!

      I've done some more searching, and it appears that when pasting from Word to OneNote, There's an ASCII 160 character (non-breaking space) inserted at every blank line. So the HTML is something like:
      &nbsp;<br />
      &#160;<br />

      That nbsp gets translated into an ascii code 160 character when you paste it into OneNote, and then when you copy that into SSMS, SSMS doesn't like it, giving this error:
      Incorrect syntax near ' '.

      So I'm guessing your SQL scripts don't have blank lines in them, perhaps? If they do, I'm wondering if there's not a setting in Word or OneNote that suppresses the non-breaking space.

      I'm still looking to find an answer to this, especially now that I'm using OneNote 2013, and NoteHighlight doesn't work with it.

    4. A few weeks ago I thought it would be great if SSMS had query windows similar to OneNote sheets. I mean you don't need to save them explicitly and they would reappear when you open SSMS the next time. Of course I would not need all the other functionalities of OneNote just that autosave and maybe when tabs/subtabs can be expanded/collapsed and all the tabs would have a name (it can be the first word on the tab) instead of SQLQueryx.sql...
      Do you also think it would be worthwile to suggest it on connect? Does anyone else dislike the current tab-management way of SSMS?

    5. I think that's a great idea. I'd *love* a layout in SSMS like OneNote has: it'd allow for some fantastic template organization.

  6. I did a presentation for a bunch of DBA's, BI and ETL folks and this was a question that came up. My response was doing what you did originally where you copy from NPP or other tool into MS Word and then to OneNote. I will have to give this a try to see if it works. I'm not surprised that I'm not the only person annoyed by this.

  7. In NPP using NPPExport > Export to RTF it saves a file with the correct formatting that I was then able to copy into ON, retaining the formatting and coloring of NPP. I am using ON2013 and NPP6.8.8 - a few more steps than I'd like, but it seems to do the trick...

  8. 1. Copy from source
    2. Paste to Word
    3. Copy from Word
    4. Paste to OneNote

    1. Yeah, it's true that the above works, visually, but it doesn't generally work for pasting SQL from ON into SSMS: the non-breaking space character that Word uses in HTML breaks your SQL scripts in SSMS.

      So if you want to have a fully-functional script repository, you have to add a fifth step: search in SSMS for a NBSP character, and replace with a regular space.


Thanks for leaving a comment!