Monday, March 18, 2013

Cost of a Bedroom overnight

After hooking up a humidifier in the boys room overnight, I started to wonder what that actually cost me. Easy enough to calculate. The boys have a clock radio, a night light and the humidifier running overnight. They have not been running the ceiling fan so I did not include that in my calculations. I used 10 hours per night which is about average.

Here are the numbers for the Humidifier.


I figured 350 nights per year taking into account the nights we are not at home, probably away from home a few more nights and sure some other variables here but you get the gist. So if we run the humidifier every night that will cost me $107 per year. That is of course if we run the humidifier every night. Costs .30 cents per night.

Since we only run the humidifier when the boys are sick, I wanted so see what their room cost me overnight on a more typical night with just the night light and the radio. Figure below.


So 0.08 cents per night on a normal night. The little things add up. Surprised that the radio drew less power than the night light. The radio is playing music and displaying the time all night.

Friday, March 15, 2013

SQL Server xp_dirtree

I needed a way to parse the filenames in a directory and plug them into a RESTORE command. The xp_dirtree works really well for this.

IF OBJECT_ID('tempdb..#restoreFiles') IS NOT NULL
DROP TABLE #restoreFiles

CREATE TABLE #restoreFiles
[filename] VARCHAR(2000),
[depth] INT,
[file] INT

INSERT INTO #restoreFiles
EXECUTE xp_dirtree 'D:backups',1,1

From there I was able to set a variable based off a WHERE clause. My FULL backups are pre-pended with the word FULL so I can plug that into my RESTORE command.

DECLARE @FullBackupFile VARCHAR(2000) = (SELECT TOP 1 [filename] FROM #restoreFiles WHERE [filename] like 'FULL%' ORDER BY [filename] DESC)

FROM DISK = "D:Backups' + @FullBackupFile + '"
WITH MOVE "DataFile" TO "D:MSSQLDataData.mdf",
MOVE "LogFile" TO "D:MSSQLDatalog.ldf",
STATS = 10

Wednesday, March 13, 2013

Syntax Highlighter using Pygments and Python

I was using this web site to highlight and format my SQL code to HTML to insert into our wiki at work on Sharepoint, when all of a sudden the website stopped working. After some searching I discovered the site was using Pygments, a Syntax Highlighting Library for Python. So I decided to try and roll my own solution. This article will detail the steps I went through to make it happen.


  • Download Python 2.7, 3.3 did not work for Easy Setup which I used to install pygments.

  • Download Easy Setup also called setuptools.


  • Installed Python 2.7 with the defaults which creates a folder called C:Python27

  • Installed Easy Setup using the EXE taking the defaults

  • Installed Pygments by hitting a console window and running this from the C:Python27Scripts folder. easy_install Pygments


  • You have to configure IIS to work with Python.

  • IIS 7: I used this article for IIS 7 config:

  • IIS 6: I used this article for IIS 6 Config:

  • The IIS 6 and IIS 7 config is a little different specifically on the Executable parameters.


  • First I setup a basic HTML page with a simple form.The page had a single form with a TEXTAREA named code.

  • Second I pieced a Python file together using code snippets from the Pygments web site, StackOvervlow, and Python help files.

#!/usr/bin/env python
# -*- coding: UTF-8 -*-

import cgi
# enable debugging
import cgitb

#print "Content-Type: text/plain;charset=utf-8"

from pygments import highlight
from pygments.lexers import SqlLexer
from pygments.formatters import HtmlFormatter
from pygments.styles import get_all_styles

form = cgi.FieldStorage()
code = form["code"].value

print ""
print ""
print ""
print highlight(code, SqlLexer(), HtmlFormatter(noclasses=True,style='default'))
print ""
print ""
print ""
#print HtmlFormatter().get_style_defs('.highlight')

print highlight(code,SqlLexer(),HtmlFormatter(noclasses=True,style='default'))

#print list(get_all_styles())

  • SqlLexer is hardcoded here meaning that we can only convert SQL code with this page.

  • Working on allowing for other languages

  • Pygments Formatter has several options, this code is only using the noclasses and style options.

  • There are several builtin Styles and you can create your own styles.

Here is the semi-finished product.

Next Steps

  • Allow for different types of code

  • Create a custom style (don’t like the default background color)

Friday, March 8, 2013

Tuesday, March 5, 2013