New song: Wired differently

"Wired differently" by Timothy Chen Allen



I learned something about you
You're just wired differently
Some of what you see so clearly
I'd need a telescope to see

And I could build a spaceship
And try to fly to the moon
But by the time I get you
You and your wiring move too soon

And if I yield to your wiring
I only start to feel confused
All of my breakers blowing
All of my circuits melt their fuse

Compared to you
My CPU's held in with electrical tape
I try and I try to replace it
But the circuit pins start to break

But that's just how I like it
I'm wired differently, too
And when my system's in complete shutdown
My only warm restart is you
Waiting 'til my meltdown's through
'Til my command prompt comes back
Waiting in the green screen glow
Sorry, I know my drives are slow

And that's not the end of the story
There is no manual for you
I know if I called the help desk
The late night technician never heard of you
Sometimes I feel a fool
Standing next to you
But my wirings in your hands
But you're just wired differently
That's the story of you and me
Don't take a CRT to read it
A kid with a slide rule could see it

Geek stuff: running Infocom games to work on Windows XP

Okay, Ike didn't cause the world to collapse. It was a very expensive, huge storm that caused a lot of damage, but it didn't kill many people. My brother in Beaumont was fine. I feel vaguely foolish for having gotten so wound up about him being there. Meh.

Anyway, I found an old CD with Infocom games on it ("Lost Treasures of Infocom"). These were the old text-based games that are now called "Interactive Fiction": Zork, The Lurking Horror, Infidel... This was all very magical to me when I was a kid. I wanted to become a computer programmer because of this kind of game. So I got my CompSci degree at the Academy and after the Marines I taught Seychellois people to use computers and... somehow I ended up working for FEMA. Go figure.

The trouble is that when I went to start up Zork, I got odd characters on the screen. This killed my eighties buzz. I vaguely remembered that these weird characters were the program's attempt to talk to ANSI.SYS. This was the way programs used to do super advanced graphics like dark letters on a light background (WOW!). I understand that Al Gore invented ANSI.SYS.

Okay, here's how you fix this:

1. Start up a command prompt (Start->Run->CMD)
2. Edit your CONFIG.NT file (notepad %systemroot%\system32\config.nt)
3. Add a reference to ANSI.SYS at the end of CONFIG.NT (add this line: device=%SystemRoot%\system32\ansi.sys)

Now ZORK and company should start up correctly and not give you any weird characters. XYZZY.

Perl: Make KML from NOAA Forecast

I wanted a simple way to make GoogleMaps KML to show forecasted storm tracks. The forcasted storm tracks can be downloaded from the National Hurricane Center and generally look like this. This is today's forecast for TS Gustav:

INITIAL      27/1500Z 18.8N  74.0W    50 KT
12HR VT     28/0000Z 19.0N  74.8W    55 KT
24HR VT     28/1200Z 19.2N  76.0W    60 KT
36HR VT     29/0000Z 19.3N  77.5W    70 KT
48HR VT     29/1200Z 19.9N  79.4W    80 KT
72HR VT     30/1200Z 21.5N  83.0W   100 KT
96HR VT     31/1200Z 24.5N  86.0W   100 KT
120HR VT     01/1200Z 28.5N  88.5W   100 KT

So I wrote a Perl program to translate that into a KML file. The program looks like this:

use strict;
#Get the storm name from the user
print "Storm Name? ";
my $storm = <STDIN>;
chomp($storm);

#Print out the KML header
print << "STARTLINE";
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.2">
<Document>
 <name>$storm</name>
 <open>1</open>
 <description>NOAA $storm track</description>
 <Folder>
   <name>Placemarks</name>
   <description>$storm</description>
   <Placemark>
     <name>$storm</name>
     <LineString>
       <tessellate>1</tessellate>
       <coordinates>
STARTLINE

# Build kml stanzas for the line and forecast markers
my $line = "";
my $points = "";
while(<DATA>) {
chomp;
if (m/^ *(INITIAL|\d+HR VT) +(\d+\/\d{4}Z) (\d+\.\d+)N +(\d+.\d+)W +(.*)$/) {
 $line .= "-$4, $3\n";
 $points .= << "HERE"
   <Placemark>
     <name>$1, $2, $5</name>
     <Point>
       <coordinates>-$4, $3</coordinates>
     </Point>
   </Placemark>
HERE
}
}
# Print everything out, including the end of the kml
print $line;
print << "FINISHLINE";
       </coordinates>
     </LineString>
   </Placemark>
FINISHLINE
print $points;
print << "FINISHPOINTS";
 </Folder>
</Document>
</kml>
FINISHPOINTS
__DATA__
INITIAL      27/1500Z 18.8N  74.0W    50 KT
12HR VT     28/0000Z 19.0N  74.8W    55 KT
24HR VT     28/1200Z 19.2N  76.0W    60 KT
36HR VT     29/0000Z 19.3N  77.5W    70 KT
48HR VT     29/1200Z 19.9N  79.4W    80 KT
72HR VT     30/1200Z 21.5N  83.0W   100 KT
96HR VT     31/1200Z 24.5N  86.0W   100 KT
120HR VT     01/1200Z 28.5N  88.5W   100 KT

As you can see, I copy and paste the NOAA storm forecast into the program. The resulting GoogleMap looks like this:

View Hurricane Gustav Forecast in a larger map
Oh my, that looks uncomfortable for New Orleans. Keep your fingers crossed.

Outlook: VBA Script to display all unread messages

I wanted a button on Outlook that would let me catch up with all my emails by opening each unread email one by one. There is a button on the reader pane for this, but I specifically wanted to be able to do this quickly from the main window. Also I was casting about for something geeky to do and this caught my eye.

Here is the code to make it happen:

Sub find_unread()
    On Error GoTo eh:
    ' I want to be able to catch up by reading all my unread messages
    Dim ns As Outlook.NameSpace
    Dim folder As MAPIFolder
    Dim item As Object
    Dim msg As MailItem
    
    ' Open the inbox folder
    Set ns = Session.Application.GetNamespace("MAPI")
    Set folder = ns.GetDefaultFolder(olFolderInbox)

    ' Loop through items in the inbox folder
    For Each item In folder.Items
        DoEvents
        If (item.Class = olMail) And (item.UnRead) Then
            ' This message has not been read.  Display it modally
            Set msg = item
            msg.Display True
            ' uncomment the next line to have it only find one unread 
            ' message at a time
            'Exit For
        End If
    Next
    
    ' If you uncommented the line to read individual messages,
    ' comment the next line so you don't get a message box
    ' every single message!

    MsgBox "All messages in Inbox are read", vbInformation, "All Read"
    Exit Sub
eh:
    MsgBox Err.Description, vbCritical, Err.Number
End Sub

To make this work,

1. hit alt-F11 to show the macro window,
2. double-click ThisOutlookSession
3. and paste this code into the resulting window.
4. Save it, then back in the Outlook primary window, right-click the button bar and choose Customize....
5. On the "Commands" tab, scroll down to "Macros" in the Categories window and click "Macros"
6. Select your macro in the "Commands" window and drag it over to the button bar.

You can clean up the button if you like.

To use the command, press the button and each unread message will open, one at a time. When you close a message, the next message will open.

Historically Most Active Hurricane Day: September 10

I recently looked over some hurricane data from 1851 until this year. I got curious: on what day of the year is there the most hurricane activity? Answer: September 10. There have been 96 active hurricanes on that day since 1851.

To create this graph, I downloaded a data set from the NOAA Historical Hurricane Tracks page: http://maps.csc.noaa.gov/hurricanes/download.jsp. I downloaded the dataset for both basins. It comes in the form of an ESRI ARCMap dhape file, but there is a dBase dbf file that can be imported into Micosoft Access (if you try this, you may run into some trouble unless you rename the DBF file to have a normal eight character file name).

I used Microsoft Excel to query the data from the Access database I had created and create the graph. I had to be careful in my query not to double count hurricanes that had more than one weather advisory on the same date.

Visio Org Charts: Change Border Color Based on a Custom Property

I recently was asked to modify an existing Visio Org Chart based on a custom property. Every person in the org chart had a custom property for a BlackBerry number. Based on whether the person had a blackberry number or not, my client wanted the border to be set to a thick red line.

I came up with the following code to do just that:

Sub set_blackberry_border_red()
  Dim vsoPage As Visio.Page
  Dim VsoShp As Visio.Shape
  Dim i As Integer

  For Each vsoPage In ActiveDocument.Pages
    ' Loop through each page
    For Each VsoShp In vsoPage.Shapes
      ' Loop through every shape on the page
      If Not VsoShp.OneD Then
        ' Shape is not a line
        nrows = VsoShp.RowCount(Visio.visSectionProp)
        ' Set shape to normal border
        black_border VsoShp
        For i = 0 To nrows - 1
          ' Look at all properties
          If VsoShp.CellsSRC(Visio.visSectionProp, i, visCustPropsLabel).ResultStr(Visio.visNone) = "Blackberry" Then
            ' If property is blackberry phone number...
            If VsoShp.CellsSRC(Visio.visSectionProp, i, visCustPropsValue).ResultStr(Visio.visNone) <> "" Then
              ' ... set its border color to red
              red_border VsoShp
            End If
          End If
        Next i
      End If
    Next VsoShp
  Next vsoPage
  MsgBox "Finished.", vbInformation
End Sub

Sub red_border(shp As Visio.Shape)
  ' Set border size thicker
  shp.CellsSRC(visSectionObject, visRowLine, visLineWeight).FormulaU = "1.2 pt"
  ' Set Border color to red
  shp.CellsSRC(visSectionObject, visRowLine, visLineColor).FormulaU = "2"
End Sub

Sub black_border(shp As Visio.Shape)
  ' Set border size to thin
  shp.CellsSRC(visSectionObject, visRowLine, visLineWeight).FormulaU = "0.24 pt"
  ' Set border color to black
  shp.CellsSRC(visSectionObject, visRowLine, visLineColor).FormulaU = "0"
End Sub

Microsoft Live Map of where I've lived

This animation was created using the Microsoft Live Map tool. You can reach the tool at http://www.3dMapTour.com. I was impressed with how easy this was to create, and a little impressed at how far I've come to end up where I am.

How to reinstall the Windows TCP/IP stack

It's been a long time since I blogged anything technical. In my current job I am quite far from technology, which is a drag, but kind of part of the dues of moving along in my career. However, I still get some opportunities to do geeky stuff.

For example, my neighbor just called me with a problem with his Windows XP machine. He had gotten hit with a virus. Norton took care of it, but his browser no longer worked.

I fired up a command prompt and pinged google. I got this:

Pinging www.l.google.com [ºÿ] with 32 bytes of data:
Reply from 64.233.167.99: bytes=32 time=63ms TTL=245
Reply from 64.233.167.99: bytes=32 time=63ms TTL=245
Reply from 64.233.167.99: bytes=32 time=63ms TTL=245
Reply from 64.233.167.99: bytes=32 time=63ms TTL=245
...

Which was kind of what I expected, except for the weird ºÿ characters in the first line. I started to suspect that my neighbor's TCP/IP stack was messed up. In the past, I seemed to remember fixing this by deinstalling TCP/IP and reinstalling it.

But when I went to deinstall TCP/IP, I saw that the "uninstall" button was grayed out. Hmm.

A little research on the Internet (always have a second computer!) revealed a single command that could be used to "reinstall" TCP/IP. It doesn't really reinstall it, it just resets the configuration of it:

netsh winsock reset

After this, Windows asked me to restart. When I went to the signoff login, the "Shutdown" button had a message that stated that shutting down would install a necessary update. I chose to shutdown.

When the PC rebooted, everything worked! There are more details on this technique here:

http://support.microsoft.com/kb/811259

VBScript: How to ensure the script is running from the command-line

There is a pretty awful feeling when you're debugging a VBScript that produces hundreds of lines of output, and you absent-mindedly double-click the VBS icon and it starts producing message box after message box. When you do this, you are pretty well stuck. You either go to task manager and kill the wscript.exe instance (and pray it doesn't mess anything up) or click "OK" a few hundred times.

If you put this little piece of code at the beginning of your VBScript, you can avoid this:

'check that we're running from cscript
if lcase(right(Wscript.FullName, 11)) <> "cscript.exe" then
   wscript.echo "Please run this using cscript"
   wscript.quit 0
end if
' Okay, proceed with the regular program
for n = 1 to 1000
   wscript.echo n
next

Big Update: Moved to Washington, DC, working for FEMA

It's been a while since I wrote-- guess I got busy. After a fairly long process, I got hired by FEMA. As a Katrina survivor the irony is not lost on me. We moved from Philadelphia to Washington almost two months ago (it seems like forever now). I've been working for a month and a bit. It's an adjustment. In my last job, I was in charge of no one at all. In this one, I have a section of folks working for me. I'm nearly twice as old as most of them. *Sigh*. I remember my first job after Peace Corps working at the Office of Management and Budget as an eager young programmer, and wonder if I had it as together as my folks.

The really big adjustment is that I'm not programming here. Not an electronic sausage. I'm not sure what to think about that. I've been trying *not* to think about it much. But the fact is that I have defined myself as a programmer for... (quick math problem... oh wow) 15 years. I actually started writing programs 28 years ago, when I was 15. On second thought, let's not count from when I was 15. That's just masochistic.

So I'm not sure what will happen with this blog, as most of what I wrote about here had something to do with programming. Guess I could go back to writing about what this blog was originally supposed to be about: writing.

I'm toying with the idea of participating in National Novel Writing Month in november. The idea is to write a 50,000 word novel in a month. I biked from Natchez, Mississippi to DC solo with no training once. I suppose I'm foolish enough to try this as well.

One high point: I did find a group with whom to play Dungeons & Dragons. We're even playing 3.5. We'll see what happens when 4th edition comes out (supposedly Really Soon). I'm playing a cleric for the first time, which is fun. I found this D&D group through http://www.penandpapergames.com.

I'm training for the Jingle Bells All The Way 10K. It's fun running my old running routes from 18 year ago (Jeez, I'm determined to feel old today). I've found a 10K route from the corner of 29th and Calvert to Westmoreland Circle, on the border between DC and Maryland. It's essentially a run up Mass Ave, which goes right past the National Cathedral. It's all fine, I just have to watch it, because my old knees are always warning me that they'll go on strike.

VB: Convert any Access table to CSV without truncating numbers

We produce a lot of CSV files in my current job. A lot of the time they come out of Access databases. But when you export CSV from Access, it does funny things you don't want, like truncating numbers or representing them in exponential representation. I wrote this VBScript to solve that problem. It takes the name of an Access database, the name of a table, and an output file as its input and creates a CSV file of the contents of one whole table. The script could be tweaked to work with other file types by giving the correct connection string:

Note: A lot of folks have written who were familiar with Access but who could not make the VBScript run. Here is one way to make this script run:

1. Open Notepad
2. Paste this code into the notepad. Be sure that you are careful to fix lines that may have split onto two lines.
3. Save the text file in notepad to "DB_to_CSV.vbs" in "My Documents"
4. Open "My Documents" and double click the DB_to_CSV.vbs file.

This should open the script and prompt you for the database and the table you want to export.

Incidentally, VBScript is one of the most powerful features of Microsoft Windows. They hid an entire programming language right in the operating system. Granted, it is not Java or C++, but you can do a lot of very cool stuff with VBScript. You can even easily do a lot of things that would be very difficult to code in a more advanced programming language (this script is an example).

option explicit

const ForWriting = 2

'Prompt for these variables
dim file_name
file_name = "C:\data\test_data.mdb"
dim table_name
table_name = "sales"

' Prompt the user for a database name
file_name = inputbox("Access filename?", "Access to CSV", file_name)
if (file_name = "") then
    ' The user hit "Cancel"
    wscript.quit
end if

' Prompt the user for a table name
table_name = inputbox("Table name?", "Access to CSV", table_name)
if (table_name = "") then
    ' The user hit "Cancel"
    wscript.quit
end if

' Prompt the user for a table name, default to the Access database name
' with .CSV concatanated to the end.
dim output_file
output_file = file_name & ".csv"
output_file = inputbox("Output CSV file name?", "Access to CSV", output_file)
if (output_file = "") then
    ' The user hit "Cancel"
    wscript.quit
end if

doit file_name, table_name, output_file

Sub doit(file_name, table_name, output_file)
    Dim sql
    Dim cn
    Dim rs
    Dim oxl
    dim t
    t = timer

    file_name = trim(file_name)
    table_name = trim(table_name)

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    ' Here we set connection properties, open a connection, and create a recordset with the SQL
    ' Note that setting the properties takes the place of creating a connection string.
    With cn
        ' This can work with other databases.  Look at http://connectionstrings.com/
        ' You could extend this to accept other database types.

        if (right(file_name, 6) = ".accdb") then
            ' For Access 2007, use this:
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & file_name & ";Persist Security Info=False;"
        elseif (right(file_name, 4) = ".mdb") then
            ' This is for Access 2003 files
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file_name & ";User Id=admin;Password=;"
        else
            wscript.echo "I don't recognize this file type: " & file_name
            wscript.quit
        end if
        .Open
    End With


    ' Here we specify the SQL we want to select data from
    sql = "SELECT * FROM [" & table_name & "]"
    rs.Open sql, cn

    ' Prepare the output CSV file
    output_file = trim(output_file)
    dim fso, file
    Set fso = CreateObject("Scripting.FileSystemObject")
    set file = fso.opentextfile(output_file, ForWriting, TRUE)

    wscript.echo "I'll output the CSV file to " & output_file

    ' Let's output the header row
    dim col
    dim line_to_write
    line_to_write = ""
    for col = 0 to rs.fields.count - 1
     line_to_write = line_to_write & ", " & rs(col).name
    next

    ' knock off the leading comma
    line_to_write = mid(line_to_write, 3)
    file.write line_to_write & vbcrlf

    ' Write out lines of data
    dim number_rows 
    number_rows = 0

    Do While Not rs.EOF
        line_to_write = ""
        For col = 0 To rs.Fields.Count - 1
     line_to_write = line_to_write & ", """ & rs(col).value & """"
        Next
        ' knock off the leading comma
        line_to_write = mid(line_to_write, 3)
        file.write line_to_write & vbcrlf
        number_rows = number_rows + 1
        rs.movenext
    Loop
    rs.Close

    wscript.echo "I'm done.  I wrote " & number_rows & " rows to " & output_file & " in " & cstr(timer - t) & " seconds"

    ' Close all of the ADODB objects
    If rs.State = 1 Then
        rs.Close
    End If
    If cn.State = 1 Then
        cn.Close
    End If
    Exit Sub
End Sub

One nice thing about this code is that it does not require you to know the names of the columns in the table you are exporting to CSV.

SQL Server: How to read from a stored procedure like a table

I wrote a really spiffy stored procedure for my client. It returns a bunch of rows, just like a table. Today the client asked if I could run the stored procedure with a WHERE clause (a perfectly reasonable request). I didn't know how to do that. But now I do:

It was actually quite simple. As far as I can see, the only trick here was I needed to have the user name and password of a SQL Server user:

-- Parameters that take single quotes
-- need two single quotes
Select * from Openrowset(
'SQLOLEDB.1'
,'MYSERVER';'joe_user';'password1'
,'exec mydatabase.dbo.my_stored_procedure ''7/1/2001'')
where address like '%elm%'
order by 7


This takes advantage of the OPENROWSET function. OPENROWSET lets you do magical things, like one-time ad hoc queries from Access and Excel and CSV. The way I'm using it is probably a bastardization, but so is everything that works well in SQL Server. As we said in the Marines, if it looks stupid but it works, it's not stupid.

Three things that can go wrong:
1) Don't forget to prefix the stored procedure name with the database name and owner (in my example, this is "mydatabase.dbo.".
2) The user you select has to have execute rights on the stored procedure.
3) this worked great in SQL 2000. It also worked great in SQL 2005, however, when I first went to do it, it failed with the following (horrible) error:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server...

I had to go directly to the server, and run the "SQL Server Surface Area Configuration" utility (Start->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration). From there I added myself as an administrator, then clicked on "Surface Area Configuration for Features". Under "Database Engine" I clicked "Ad Hoc Remote Queries", then a checkbox appeared for "Enable OPENROWSET and OPENDATASET support". I checked this. After that, the error stopped happening and I was able to run my SQL statement.

Just finished "Harry Potter and the Deathly Hallows"

I took the day off from work today and finished Harry Potter and the Deathly Hallows (Book 7). About the book... I won't say anything at all. Except I'm glad I read it, a little sad it's over. For me that marks a good book. But no details. You'll have to read it yourself.

One Warning: If you are planning on reading the book, do not read the table of contents. Do not flip through the book. Start at page one and read on through. I say this because I accidentally saw three words towards the end of the book before I should have, and it ruined a surprise for me. Caveat lector.

I got the book at nine minutes after it was released at 12:01 AM on Saturday, 21 July 2007. I had pre-ordered my book from Borders, and they had a little celebration. Since Sònia and Daniel were in Barcelona, this was my first and only opportunity to be present for one of these Harry Potter celebrations. I'm really pleased I got a chance to participate in this phenomenon. The group of folks who gathered at the Borders were my people-- kind of geeky. The group represented every demographic, and I was surprised to see a lot of people who I normally would not have picked as readers participating in lively debate over very exacting points of the previous six books.

There were tiny children who could not possibly have been born when the first book came out who could quote you chapter and verse of any of the books. There was a woman, about my age, who had come with her mother, and apparently this series of books have become a source of constant discussion for them. There was a young man, probably 15, who came dressed as Albus Dumbledore, right down to the pointy hat, who argued passionately that Snape was a good man. He reminded me of me when I was 15, except he seemed a lot more comfortable with his role as "alpha geek".

I wonder what will happen now? My niece read Harry Potter and the Prisoner of Azkaban (Book 3) nine times when she was just eight years old. It would seem a shame for something that has so animated kids to read not to have some successor, even if J. K. Rowling does not write it.

How to import dBase tables into SQL 2005 using SSIS

Ah, SQL Server Integration Services (SSIS). Just when I'd learned to love Data Transformation Services (DTS) in SQL 2000, they completely changed how Extraction, Transformation, and Loading (ETL) is done in SQL Server 2005.

That's okay. Something else to throw on the resume.

At my current position I have to read in dBase files (DBF) a lot. We use ESRI ArcMap and everyone here uses ESRI shape files, which put data in DBFs. So I had to learn how to read in DBFs in SSIS.

I was going to go into all the troubles I had doing this, but the fact is there is just so much that can go wrong here. Here is one way to make things go right:

1. Change the name of the DBF file you want to read in to be 8.3 format. That is, 8 characters before the dot and 3 after. e.g. If the data file is named "2007 Data Load for Finance.DBF", change it to FIN2007.DBF or some such. SSIS will reckognize the DBF if the file name is not in 8.3 format, but will not be able to read data from it. This caused me much heartache before I figured it out.
2. In SSIS, set up an OLE DB Source. Hit the "New..." button for the OLE DB connection manager". Choose "Native OLD DB\Microsoft Jet 4.0 OLE DB Provider" as the provider.
3. For the Database File Name, put in the path to the DBF, but not the DBF file name itself. You will not be able to browse for this, because SSIS is looking for an MDB file at this point, which is not what you want. e.g. if your DBF is in c:\databases\FIN2007.DBF, put in "C:\databases\".
4. Click the "All" button, scroll up to Extended Properties, and put in "dbase 5.0". If you don't do this, SSIS will try to read your dBase file as an Access file, which will fail. While you're here, you can hit "Test Connection" and it should work.
5. Hit OK until you are back at the OLE DB Source Editor screen. Choose the name of the database file from "Name of the table or the view".
6. At this point you should be able to hit "Preview..." and see your data. You can now use this DBF connection as a data source.

How to sell books on Amazon without losing money

The problem of the overzealous reader
Sònia and I have been looking a lot at the huge pile of books we've been dragging all the way from Barcelona through New Orleans to Philadelphia. It's too dang big. About half the shipping container we brought from Barcelona was just books.

On the one hand, I love having books around me. But I've discovered something. I really love the library. It's like having someone else's dog around. They're fun, but you don't have to pay for their food and when you're tired of them you send them on home. Incidentally, if you live in Philadelphia, the Philly Free Library even has a web page that will let you order books. They'll notify you by email when the books are ready to pick up. It makes you feel smug and important when you go pick up your $500 worth of books at the library for free.

Which brings us back to the mountain of books I bought and now have sitting at my house. I finally decided to sell about 100 of them. That's a laughably small fraction, but it's a start.

Amazon to the rescue
Amazon has a system by which you can sell your old books. The process to become a seller is relatively painless. You have to have a credit card or checking account in which you can receive the payments, and they go through a verification process to ensure your account exists.

Listing books
To list books you want to sell, the simplest thing is to enter the ISBN into a form Amazon has on their seller's page. You don't have to scan images of the books or write descriptions, all that is taken care of by Amazon. The only trick is that the book has to be in Amazon's catalog, but you'd be amazed at what they have in the catalog. All of the Spanish language books I put in were listed, and almost all of the Chinese books were found as well.

Assigning a quality rating
The one thing that is a little bit of a pain is that you have to assign a price and a quality rating to the book. The quality rating is subjective. You look at it and try to see if there is notable damage to the book, etc. You have to be honest on this. If you aren't you can get a bad reputation score and no one will buy from you.

Assigning a price: A little math problem
When you go to assign the price, Amazon tells you what the lowest price is at that moment. I'm not a big volume seller. I just want to get rid of these books, so I just undercut the lowest price by a little. However, sometimes there is no profit to be made by doing that. In my case, if I calculate no profit, I just don't sell the book (maybe someone gets it next holiday season).

Amazon takes a commission: set the price accordingly
Of course, you get the money for the original price you set for the book. However, Amazon takes a commission for the sale. The commission for books is 15%, plus a 1.35 closing fee, plus a 99 cent transaction fee:

Amazon's take = (Your price) * 0.15 + 1.35 + 0.99 Example: $10 Book -> ($10 * 0.15) + 1.35 + 0.99 = $3.84 commission

Shipping costs
I sent out the orders via Media Mail, which is a U.S. Postal Service type of shipping that only allows books, CDs, DVDs, and the like. It is slower (5-7 business days, usually), but cheaper. Here are the rates for Media Mail by weight:

Weight (lbs)	Shipping cost
1	$1.80
2	$2.14
3	$2.48
4	$2.82
5	$3.16

A standard padded mailer envelope from the post office is $1.65. You can probably do better than that at Office Depot.

Pricing: Putting it all together
So you need to keep in mind Amazon's commission, shipping costs, and materiel costs when setting the price. A couple of examples:

(Your price) - (Amazon's Commission) + (Shipping Allowance) - (Media Mail Shipping Cost) - (Envelope) = (Your profit)

$10 book that weighs 1.5 lbs: $10 - $3.84 + $3.99 - $2.14 - $1.65 = $6.36

$4 book that weighs 2.75 lbs: $4 - $2.94 + $3.99 - $2.48 - $1.65 = $0.92

$2.50 book that weighs 1.1 lbs: $2.50 - $2.72 + 3.99 - $2.14 - $1.65 = -0.02

Take note of that last figure. You actually make no profit on that sale. I put myself a minimum of $1.00 profit on every book, it's just not worth it to go to all the trouble to sell for less for me.

You see a lot of books being sold for 1 cent. This is because big volume sellers pony up for a special Pro Merchant Subscriber account that gets Amazon to waive the 99 cent fee. Also they may have figured out a way to ship more cheaply or get shipping materials for a lot less.

Receiving orders
About an hour after I had all of my books in the system, I started receiving orders. These come in the form of "Sold, Ship Now" emails from Amazon. You can also just check the "Recent Orders" report on the Amazon Seller's page. I responded to each of these orders immediately with a message about how soon I was going to ship the order. From the same report page you can print out shipping labels and packing lists. I just printed out the packing list and stuck it in the first book of each order so I knew what to ship out.

Resetting the low price
One annoyance I noticed was that people continuously change the price on their books to get the lowest price. So if you want to sell relatively quickly, you have to spend some time every now and then at the "Your Marketplace Open Listings" page to check that your low, low prices are not higher that anyone else's.

How much I made
Finally, once I shipped the books, I was able to write to my customers to tell them everything had been shipped. My first run netted me about $25 on the sale of eight books. Am I going to get rich doing this? Probably not. It's a lot of work for not much money. But we have less books now, and some other book lover has our old books.

SQL Server: SSIS error: Cannot create connector...

I've been using SQL 2005 SSIS (SQL Server Integration Services) more and more lately. It is a big departure from SQL 2000 DTS (Data Transformation Services), but actually grows on you after a while.

I get the following error pretty often:

Cannot create connector.
The destination component does not have any available inputs for use in creating a path.

If you get this, happy you. This simply means that you have tried to use an OLE DB Source as an OLE DB Destination. The Fix: Simply delete the OLE DB Source and replace it with an OLE DB Destination block, being careful to set all of the connection parameters correctly. Wish they were all this easy.

SQL Server: "All Values" parameters in the WHERE clause

Recently, I put a parameter in a WHERE clause in a stored procedure so my client could select values from a list in SQL Reporting Services:

projectID	projectTypeID	projectManagerID	projectName
1	100	200	TPS Reports
2	100	201	Interest rounding exploit
3	101	200	FLARE evaluation
4	101	201	B.E. Channel Referral

select *
from projects
where projectTypeID = @projectTypeID

The client liked it a lot, but then he wanted to add an "All Project Types" option to the list. When the "All Project Types" option was selected, the parameter would be set to -1, and the WHERE clause would essentially go away.

I initially solved this with a temporary table and an IF clause, but found that SQL Reporting Services is squirrely about temporary tables. So I found this sweet little solution:

select * from projects
where @projectTypeID = -1
or projectTypeID = @projectTypeID

Which is really quite smart, isn't it? When the parameter is -1, the first part of the WHERE clause is TRUE, and TRUE OR anything is TRUE. When the WHERE clause evalutuates to TRUE always, then all rows are returned. If @projectTypeID is not -1, for example 100, then the first part of the WHERE clause is FALSE, but FALSE ORed with TRUE is still TRUE. So in our example of @projectTypeID = 100, two rows will be returned.

There is one last wiggle. My client wanted to be able to specify "All Project Types" and "All Project Managers". The same logic works, but you have to write the WHERE clause correctly:

select * from projects
where (@projectTypeID = -1 or projectTypeID = @projectTypeID)
and (@projectManagerID = -1 or projectManagerID = @projectManagerID)


See if you can trick out the logic your self on this.

Special thanks to Ilo at Ilo's Quest for this trick.

I've moved my blog!

I've actually moved my blog over here to Blogger. I was a tough choice, but considering that when I was paying for it, the blog was costing me about three times what I was getting on AdSense, I decided to drop out of it. I'll be fully over here by this weekend, I think. Hmmm, what to do with the domain timallen.org now?

Moving my blog

I've recently had troubles with my blog software (I had been using GeekLog), so I finally decided to bite the bullet and move my blog over to blogger. I figured out some Python code to move my stuff, though I've discovered that I can only post 50 posts every 24 hours via code. So it'll take a few more days to get everything over here. When I'm done I might post the code (though it's embarrassingly simple and basically just reuses code from the blogger API site).

John Backus, inventor of FORTRAN, dead at 82

John Backus, inventor of the FORTRAN programming language, just died at the age of 82. The very, very first program I ever wrote was in FORTRAN, on Hollerith cards. The obit is here: here.