Charlie's Hash

Over 150 hashers from all over the DC area and beyond got together to celebrate the life of our friend Charlie "Wrong Way" Roberts:

Jingle All the Way 10K: 51:45

A woman I work with, Allison Thompson, got a bunch of us to run in the Jingle All the Way 10K. Our team, Allison Thompson, Natalie Palugyai, Xavier Hughes, Rob Goucher, Deni Taveras, Tami Theiler and I were listed as the Disaster Blasters. It was a lot of fun, though it highlighted for me what I've been coming to grips with for a while: I'm not as fast as I used to be!

NaNoWriMo Day 27: Done! 50,062 Words!

Incredible as it may seem, I managed to finish the 50,000 novel in 27 days. There was a lot of fun in the first three weeks, a lot of moments when the prose just flowed out of me and I was amazed at the ease of the thing. There were moments of uncertainty, but the writing kept it fun, and I eventually was able to come up with a good story arc.

Then the fourth week came, and I started coming to the end of my novel-- 8,000 words too early, This part was painful. The ending point I chose seemed stiff, contrived. And I found myself writing the words THE END three days ago. Which meant I had to go back and invent scenes I had not thought about.

In the end, those were some of the most fun scenes to write. I realized when I got to the end of the book that I had neglected my protagonist's relationship with his parents, so I put in a good number of conversations between them. That was good for at least 4000 words. I also realized that my character had about a month during Christmas Break from the Naval Academy when he had nothing to do. So I put him on restriction and had him sit around and talk to the rest of the criminals. The book is not about me, but this was pretty similar to when I was on restriction for Thanksgiving my second class year-- again, I want to emphasize that the book is not about me. Honest.

I should emphasize something: no one in their right mind would write a novel in 30 days. I know that. Real Writers would not even attempt a first draft in 30 days. But I also know me well enough to know that I needed to do this in 30 days or I would not get it done.

So here's to my novel. I don't know if anyone but me will ever read it (that is, I may not let anyone else read it). But there's a lot of me in that thing.

One thing I'm sure about: I'm pretty sure I'm going to try again next year.

NaNoWriMo Day 24: 40,000 words!

Today is quite a milestone. I just passed 40,000 words and am well within my goal word count. Apparently the last 10,000 words are pretty much of a slide. I'm thinking about when I ran the Marine Corps Marathon in 1991, though. I finished off the first 20 miles and realized I had 10,000 meters to go, and thought how nice it would be to stop right then.

One difficulty is that I already know what the last two scenes in the book are going to be. So my options are starting to get limited for new scenes for my actors. But I've found finding that every aspect of this exercise so far has somehow made me squeeze out some new aspect of writing, and I expect that this restriction will make me do just that as well.

NaNoWriMo Day 23: 38,839 words

Night before last, I sat up until 3AM and finally caught up my word count-- in fact I got an entire day ahead. Tonight I realized that I was going to fall behind again, so I bit the bullet. When Sònia went off to bed, I stayed up and made myself write until I was caught up again.

I actually think I'm going to make it. I think I know how the book is going to end, although I am not discarding the idea of some new plot twist injecting itself at the eleventh hour.

I can't express what an awesome experience this has been so far. I have had to dredge up memories I had not thought about for ages. Parts of the book are starting to connect together in weird, magical ways. I'm also starting to really notice the craft of other writers-- I was reading a passage from The Lord Of The Rings the other night and was struck by how much of what Tolkein wrote had nothing to do with moving the story forward and everything to do with setting up environment and helping develop the characters.

I've gotten so cocky that I told my dad about writing this thing and he offered to edit it. My initial reaction was to accept but internally reject this idea-- I mean, what's my dad going to say about all the parts that involve genitalia? But then I thought, how many guys get to do something like this with their dads. So I guess the first person who will read my book will be my dad, if I finish it. Which I will.

NaNoWriMo Day 19: 29,060 words!

This morning I was getting ready for work and saw that today was the 19th.  Which means (yikes!) only 11 days until the end of National Novel Writing Month!  I managed to catch up a little over the weekend, which was good.  My writing session in Annapolis was shorter than I wanted it to be.  I spent a lot longer than I had expected looking over the Academy grounds.  So little has changed there in 21 years!

I've been doing some minimal sketching out of the plot and leaving un-written some of the passages that just serve to connect more interesting passages.  This is paying off.  The story is flowing along nicely.  I really understand what writers mean when they say that the book takes on a life of its own.  There have been several times when I've decided to take a character in a new and different direction only to find that details of the story up to that point support the change.  It feels like someone else is planning out the book for me.

This weekend I had dinner with my old Glee Club director from the Academy, Dr. John Barry Talley, and his wife, Marcia Talley.  We were talking with a group of other alumni from the glee club about a bar in Annapolis, and I found myself saying, "oh, thank god you know the name of that place, I'm using it in my book."  My book.  How very weird.  Marcia Talley is a renowned published writer, and she looked over at me and beamed proudly when I said that.

NaNoWriMo Day 15: 22,547 (halfway day)

In theory my word count should be at 25,000, but I'm feeling pretty good about this. Almost all of my story takes place in Annapolis, and I'll be taking a trip up there tomorrow to meet up with some of my old friends from the Glee Club and Dr. Talley, the director who just retired after 35 years. So I should have some time to get some writing done tomorrow while I'm there, and maybe research a couple of the bits where my knowledge of The Yard has gotten foggy.

I finally took a few minutes (I'm talking like five minutes here) to outline the rest of the story. I'm not 100% committed to all elements yet, but I kind of have an idea of what points the storry will be building towards now. I have the whole semi-plan written out in BIG HONKING CAPITAL LETTERS at the end of the novel so far.

One bit of advice I got today was to leave aside all of the connecting passages and concentrate on the juicy bits that drive the story forward. The connecting stuff can stand as some instructions ("AFTER THIS X LEAVES THE PARTY AND SOMEHOW GETS TO Y'S HOUSE WHERE THEY WILL HAVE A FIGHT") that would be fixed in editing. I'm going to try that tomorrow and see what happens.

NaNoWriMo Day 14: 20,338 Words

My lovely wife, Sonia, has patiently allowed me to write during our normal DVD viewing time for the past two nights, allowing me to almost catch up with my word count (I should be at 14 x 1,667 = 23,338 words).  It's still not great, but definitely do-able.  Also, a quick check of the NaNoWriMo founder's web page shows him at only 17,905 words, so I feel like I'm doing okay.  Day 15 is the halfway point.  As I understand it, things should actually get easier from this point on.

The novel is definitely set at the Naval Academy right now, which I didn't really expect (hell, it started out in the UnderDark world of Dungeons & Dragons).  As if I hadn't stressed this enough, you should definitely try doing this.  I have had to take a hard look at some of my old memories and realize the complexity of some of them.  Almost no experience in this life is 100% good or 100% bad.  Trying to extract detail out of my memories so I could make my precious word count has forced me to relive some of what I had considered the worst times in my life and made me remember that there were positive elements to all of them.

Aside from that, my support network has been positive so far, and the people I have told about it have said they'd like to try it (if only to humor me).

NaNoWriMo Day 13: 18,187 words

The last couple of days haven't been as productive as I had hoped-- now that the first week is over, life has crept back in and I'm finding that I have to return to my obligations outside of novel writing. So tonight I played a little catch-up and made up for most of the 5000 words I had fallen behind.

These scenes in my novel are coming from scenes in my own life, intentionally or not. I'm finding that writing about them is making me think about parts of my life that I had pretty well decided not to think about. And I feel really strongly about some of the stuff that I've experienced. I didn't realize that there would be this unexpected side effect. There are many parts of my life that I have distilled down to a caricature of reality because that kind of shorthand is just emotionally easier to deal with. Writing about them forces me to re-consider all aspects as I try to wrest another 300 or 400 words out of it.

NaNoWriMo Day 9: watch out for research

I'm still ticking along with NaNoWriMo. At 13,941 words I have 1062 to write before midnight to make my quota of 15,002 words tonight. I got a little behind over the last couple of days, primarily because I got bit by the research bug. My protagonist is still at the Naval Academy. Although I remember a lot about USNA, I have had to resort to the web to remember some of the layout of the actual campus. That's fine, I probably could have cleared all of that up with a couple of trips to Wikipedia, but day before yesterday my research just simply turned into web surfing and I didn't make quota.

I realize this may sound like an unhealthy obsession with making quota, but there really is something to this. I have tried writing a novel several times before. In general I have carried around a notepad and a pen and just written whenever the spirit moved me, and got some very good scenes out of that process. I've also tried following a structure in The Weekend Novelist that was supposed to allow me to write a novel in one year, writing only on the weekends. I found my enthusiasm for this was not something I could sustain more than a month and a half. I find that by writing a set amount each day I am producing some pretty good stuff every day, along with some things that are regrettable that will have to be edited out in the end. But in the past, that regrettable material would have stopped me dead in my tracks. Since I'm writing with the understanding that this is a first draft that will have to be edited, I feel a lot freer to get my material out. Different stuff works for different people.

National Novel Writing Month Day 5: 10,002 Words!

I have to say that I'm surprise at myself. I've managed to keep on track with National Novel Writing Month for five days now, and have written my 10,000th word (well, 10,002 exactly). At this rate, I will have written my 50,000 word first draft on the morning of the 30th of this month.

I would have liked to have built up a little cushion by now, as I understand that the second week is harder. But I'm happy to be at least up to quota. I've had to leave behind the idea that I was not going to write about my years at the Naval Academy. It turns out that this is the story I've been waiting to tell. At least part of it. I dunno. Anyway, my protagonist is now firmly into plebe year. That's all I can say. This still might turn into a dragon rider fantasy novel in the end. Okay, I've said too much now.

If I haven't said it before, you definitely should consider doing this. It is crazy, but in a good way.

National Novel Writing Month Day 4: how I got to hate the number 1,667

I can't believe it, but I'm still on track with the novel. If I keep cranking along as I have been, I can actually produce 50,000 words this month. The good thing about this is that no one said they had to be good words. I'm not proud of every line I've out down. However, I have to say that I'm pretty proud of a lot of them, which I think is about as good as it gets for a first draft.

One thing I'm not really too happy about: I had sworn to myself that I would not write a lot about the Naval Academy, but here my character is, showing up for Induction Day. Guess there isn't much I can do about it except wait it out and hope the next part is something more or less original.

National Novel Writing Month Day 3: Sleepy but made quota

Today was the first time since I started NaNoWriMo that I didn't write in the morning. I didn't plan it out too well this time. I kept meaning to sit down and write, but Daniel woke up, then we had to go play for a while, then this that and the other. Finally, Sònia and I went out to dinner with friends, and I had still not sat down to write. I knew this meant trouble.

You see, I have consistency issues about my hobbies. There have been times in my life when I have had a very consistent running habit, for example, only to miss a couple of days, then realize that I had not returned to running for several months. So missing a day's writing could add up to not finishing out the Novel Writing Month for me.

So when we got back home from dinner, I told Sònia that I was going to have to sit down and write, like it or not. So I did. I needed to up my total word-count to 5001 words. Fortunately I had written a little extra on Friday, so I didn't have to make the whole 1,667 words tonight.

I'm happy to say that I made quota: 5,005 words. I fell back on that old tried and true formula: personal experience. I've got a pretty good mix going on fictional elements and stuff that actually happened to me. One good thing about being 43 is that a lot of stuff has happened to me. I hope I've learned my lesson. I feel pretty tired now and need to go to bed.

National Writing Month Day 2: still alive

It's day 2 of National Writing Month and I haven't quit yet. I got up and wrote my quota again this morning. I re-read what I wrote yesterday and have decided to take the attitude that even Sinatra cleared his throat before he sang. After the first paragraph it wasn't half bad. Maybe some elements of what I wrote won't make it all the way to the final draft, but that's why this is called the first draft.

Except for the first paragraph. That was crap. Utter bat guano.

But this morning things started rolling pretty well. I dredged up stuff I hadn't thought of in ages. I realized that I've done some pretty weird stuff that could probably be wedged into a work of fiction somehow. I chirped along merrily and wrote until Daniel woke up.

National Novel Writing Month starts

National Novel Writing Month started this morning at Midnight and goes for the entire month of November. The objective is to write a novel of 50,000 words in 30 days. For reference, The Great Gatsby was about 50,000 words long. In case you're wondering, that works out to right at 1,667 words a day, or about three and a half pages in a normal Word document.

So this morning I dutifully sat down and wrote 1,667 words. I should preface this by saying that I have done some writing in the past, some of it quite good (that's just my opinion, but for these purposes it's the only one that matters). What I produced this morning was garbage. Really very bad writing. Right on up there with Vogon Poetry.

I thought that maybe I was exaggerating, but I just opened the document and started reading it, and couldn't stomach it. I wouldn't read what I just wrote if you paid me.

Part of me is a little depressed about this. I mean, after all the good stuff I've written in the past, the one time I make a committment to actually finish something, it really stinks.

But I'm going to continue with this. First off, the goal is to produce a first draft. As Hemmingway said, "The first draft of anything is shit". But more than that, it occurred to me that I'm a pretty good writer when you look at me at a tactical level-- I write funny sentences. I write pretty good paragraphs. I probably could (had have in the past) pull together a decent short story. But I have never even tried to maintain the strategic level required to write a full novel. So why should I expect it to be great the very first time I do it? I think what I mean to get out of this exercise is to prove to myself that I can do it, to learn the logistics of fitting writing into my life, and to get better at seeing a longer story from a strategic level.

Besides, I think I get a t-shirt or something for doing it.

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.

Running with the DCH4 in The Sticks, Maryland

One of the few benefits of having Sònia and Dan go to Spain every Summer is that I get a little more freedom to get down to DC to see old friends and run with the DC Hash House Harriettes and Harriers (DCH4). I began running with this group when I was a young Marine stationed in Arlington, VA in 1989 (that's 18 years ago). The last time I ran with them was last year when Sònia went to Spain.

This was a farewell run for Mother Chalker, a great guy I've known since the very beginning. He co-hared with Sounds Like Pushy and her kids. The trail was excellent: a lot of brambles, a lot of poison ivy, a little getting wet (which was welcome in the 92ºF weather). Great food, too, top notch stuff.

I got to run with Bobby Long Hare, another guy I've known from the very beginning. After five knee surgeries he's still running really fast. It was fun catching up with him on the trail. I also chatted with Deb and Lynn about living in Anacostia (which would have been unthinkable ten years ago) and life in DC in general. Funny how things have changes so much since I was there. Funny how so much has stayed exactly the same as well. I talked with Amelia Airhead and Slow Man for a while about old times and poison ivy (always a great topic at the hash). I saw Dragon Lady, which was wonderful; she and Charlie really helped me out when I was fresh back from Peace Corps. Leisure Suit Larry told me about a book he started writing with his dad that he is finishing by himself now-- I'm always fascinated to hear about someone actually writing a book and getting it published.

I ended up pulling out the guitar and singing for a while with Sushi, who has a great voice. Kind of like Joni Mitchell. We did a bunch of old stuff. I'm finding that the songs I learned before I turned 25 are rock solid in my memory but the more recent stuff slips away more easily. What a drag it is getting old. Sushi teaches children using music. We talked about how Daniel and I mess around with the guitar together. The hash is great for stuff like that.

Anyway, a really good day. I was scratched, sweaty, exhausted, full, and happy when I got in my car to drive back to Philadelphia. Great day.

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.

43rd Birthday: One of my best days

Today is my 43rd birthday. It's been a great day-- in fact, I was thinking towards the end of the day that today has been one of the best days of my life-- just a really solid day.

About eight years ago I wrote about an idea I had that religions should celebrate the one really good day that their Supreme Being had (when girls smiled at him and his car started on the first try) a lot more than the day He died in fearful agony (http://timothychenallen.blogspot.com/1999/06/really-good-day.html). Well, today was just that kind of good day.

I woke up next to Sònia, who has loved me for a really long time now. I gave her a kiss and then got up and ran my new favorite 10K route (http://timothychenallen.blogspot.com/2007/06/10k-route-starting-in-center-city.html) in a few minutes faster than I've done it before. Granted, it was a good deal slower than I did when I set my sub-40 P.R. when I was 25. But hey, I'm 43 now, so sue me. When I got home, Daniel was up and he and Sònia sang me "Happy Birthday". Then Daniel gave me a dinosaur figure that he painted himself. He liked how he had done that so much that he asked for it back, which was fine. Sònia gave me Neverwinter Nights: Diamond Compilation Pack (DVD-ROM), a video game based on Dungeons and Dragons, which I love, and Jack Black's Tenacious D in The Pick of Destiny. Rock on.

We had my actual party at Franklin Park. Everyone who came spoke Spanish. Our Argentinean friends Martin and Paula, and our Venezuelan friends Ingrid and Roberto, and two Spanish folks, Rafa and Marga. I chuckled to myself at one point, thinking, "well, I guess I speak Spanish now". This is sort of the culmination of the dream of speaking Spanish that led me to move to Barcelona, Spain back in 1998, which led me to meet Sònia, which led to us getting married and having Daniel.... At the party we just sat and talked and laughed about everything. At one point we talked about ages (Sònia and I were the two oldest at 43 each) and I could remember the important things that had happened at each age-- at 35 I met Sònia... at 37 Daniel was born... at 39 we moved to New Orleans.

We got home and I played with Daniel for a while, then I fell asleep on the carpet of our living room. Ah.... We had dinner in the back yard with our neighbors, David and Olivia, who are great, and ate too much ice cream.

Now I'm going to get in bed and continue reading George R. R. Martin's A Game of Thrones (A Song of Ice and Fire, Book 1), which has me totally sucked in. How I love my life....

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.

5K Route through South Philly

I had to drop off some DVDs at BlockBuster tonight, so rather than give up our good parking space I decided to run it. As it turns out, the route is almost exactly 5K. I've saved the route to http://www.gmap-pedometer.com/?r=1063016. It's basically down South to 10th, 10th through Little Italy to Snyder, Snyder over to 12th, then 12th back to South.

I was surprised at the difference between 10th and 12th street. 10th street between South and Snyder is almost exclusively an old Italian neighborhood with some Hispanic folks and a smattering of students. 10th street was almost entirely African-American once I got north of Washington. Strange how neighborhoods sprout up like that.

It was a warm, humid night, and everyone, regardless of background, was sitting outside taking in the breeze. I'm pretty sure they thought I was crazy to be running. They're most likely right.

10K Route starting in Center City, Philadelphia

To be honest I haven't been very constant about updating my running log, and with good reason. I've become extremely consistent about running 20 minutes every morning, basically the same route every day. My blog would consist of entries that basically said, "I went running".

But this morning I found such a nice route that I thought I'd write about it. I started out for my basic 20 minute route, but it was such a good day that I decided to stretch out and run for an hour. I should mention that I went to a barbeque yesterday and ate about five times what I meant to (damn chocolate), so I think I was trying to run that off as well.

The route is almost exactly 10 kilometers starting in Center City, Philadelphia. It's dirt simple: starting from Broad and Lombard, run East on Lombard (crossing Juniper, 13th, 12th, etc). Turn right onto Front Street, near the water. Continue down Front until you get to Oregon Avenue, past the IKEA. Turn right on Oregon Ave and run until you get back to Broad. Turn right again and run back to Broad and Lombard. I've put the route up at http://www.gmap-pedometer.com/?r=1056128.

This probably isn't the greatest route ever to run at night time, as Oregon Avenue and Broad both have some kind of iffy areas. But during the day you get to see the diverse life of South Philadelphia. You can hum the theme to "Rocky". Good stuff.

Lumberton High School 25th Reunion

I went to my 25th High School reunion this weekend. It was an eye-opening, life-affirming, clarifying experience.

I went into the reunion with a good deal of trepidation. As I've written elsewhere in this blog, I had the impression that I was an "unpopular geek" during High School (see http://timothychenallen.blogspot.com/2007/06/my-25th-high-school-reunion-this-friday.html). After 25 years, it turns out I was the only person in my High School who thought that. I showed up for the initial event on Friday night after flying in from Philadelphia, and had the embarrassing realization that I had problems recognizing even some of my closest friends. 25 years had changed all of us quite a lot. One of my best friends in school, Walter Mobley, was completely unrecognizable to me until he spoke, whereupon I recognized him immediately.

My next realization was that the changes of 25 years had left most of us a lot better looking than we were in High School. I decided that Brian Fournier had gotten to look exactly like Brian Fournier should have looked all along, for example.

I had interesting conversations with people I had been afraid to talk to in High School. After 25 years, these people had stopped being people for me and had started becoming concepts. I found myself saying to myself, "wow, I'm talking with THE Kristi Ransom", then realizing that Kristi is a person who has continued to live and grow for exactly the same amount of time as I have since graduation.

And I arrived at the conclusion that everyone, and I mean everyone, was so self-conscious in High School that it was impossible for us to have an accurate impression of what was really happening around us. As self-absorbed as I am now, I was MUCH more so in High School. We had all found personae that we hoped would work for us, trying out new stuff occasionally when we found that things didn't work. I believe that's what we've done since then as well. But I think that now that we're all in our forties we all seemed to have dropped a lot of the pretense and had come to accept the reality of our situations better.

I found that some of my most beloved teachers in High School were not so very beloved by many of my classmates. This came as a shock.

The first night I was one of the last to leave, sitting in a circle with Jeannie Landry, Laura Miller, and Mike Kelly, hashing out old times. They were three people I had known since I was nine years old. Good lord, that's 33 years now. Jeannie's husband Alexander was with us, and I was pleased with what a nice guy he was. I had been a little dismayed with how many of us had divorced-- it seemed like all but four of us out of the 50 or so attendees had divorced. Surprisingly, almost all had remarried, and they all seemed to be happy with their current spouses.

I went for a run the next morning with every intention of running to the end of my road and back. I ended up running through the entire town. Lumberton has grown so much in the last 25 years. We had one traffic light when I was here, and the High School I went to was newly constructed in our sophomore year. Now the schools have grown to have more buildings to house many more students. There are lots of restaurants (we had Sonic and the Wagon Wheel), an office building, and many more businesses in general. Just about everyone in the class had remarked on this, and how Lumberton had ceased to be our Lumberton about ten years ago.

I have to say that the kids in Lumberton sure looked and acted a lot like we did when I was a kid.

I hung out with Troy Soileau the second day for much of the day. We drove around and saw the entire town and then drove out to Lake Charles for a while. We caught up on our families of origin and the families we were building ourselves. Troy was one of the people who remained married to his original spouse, Pamela. They seemed to get along really well. As I listened to him talk, I thought about how all of us had fought our own personal battles, and how some of our most harrowing experiences had made us what we were. Every one of the 128 members of our graduating class had lived through the exact same number of days since graduation day, and many of those days were filled with crises, disappointments, and occasionally, triumphs.

The second night more people came and I was reunited with three of the four guys who I considered the Geek Crowd (Charles Johnson, Walter Mobley, Paul Smith, and Markel Kelly-- Markel couldn't make it), who I hung out with my last few years. They all seemed to have grown into their lives and were happy. They also seemed a lot less geeky now, more like three guys who had found their grooves.

I was elected "least changed boy". It was nice-- the question came up about who had changed least and just about everyone said my name.

I had a long conversation with Ginger Jenkins. She had become a journalist. I asked if she had done any other writing, and she discreetly said, yes, I wrote a novel. It made me feel really humble-- if I had written a novel, I'd probably have walked around saying, "nice to see you, I wrote a novel" to everyone. She spoke about what an amazing process it had been, and how she was working on another now. I was proud of her. I wanted to read her novel.

I stayed until the end, sitting around as the staff cleared the tables, talking with the Marianne Blackstock, Joy Carter, Paul Smith, Laura Miller, John Crowe, and Mark Mitchell. I think we were all a little in awe of what we had just done. We talked about doing it again in five years. I hope we do.

My 25th High School Reunion this Friday

I'm headed back to Lumberton, Texas for my 25th High School reunion this Friday. I'm not quite sure I'm expecting. I was kind of an unpopular geek in High School, though I have some very fond memories-- especially of some of my teachers like Michael Perryman and Glenda Peacock. They were good folks.

I guess everyone grows up, and though I'm still a geek, I'm a lot more comfortable with that fact (of course being a geek these days is not a big deal).

Anyway, I am looking forward to seeing some of my old buds. Funny, my first ever girlfriend, Carol Weil, won't be there. Probably just as well.

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?

Photos! Fotos!

PHOTOS! FOTOS! http://picasaweb.google.com/TimothyChenAllen http://picasaweb.google.com/sontida These are our new Google Picasa photo sites. We will put up new photos as they happen there.

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).

A change in how I blog

No more politics

After talking with some good friends tonight, I've decided for my own good to close off part of my blog. I've written about politics a fair amount on this site. But I've decided that it's not for me any more.

The problem is that politics has the tendency to separate us at times when we most need to pull together. Sometimes, when we're arguing, saying, "as a nation we should go this way", we really need to be looking at each other and saying, "I really like you, and I need you to know that".

Why the change? Someone who I care about very deeply is sick now, not doing very well at all. He and I don't agree politically. But I love him. I don't want to waste another second trying to convince him of anything. He's a grown man and he's entitled to his opinions, and the fact is that compared to how he's feeling, I don't really care about his politics.

That goes for everybody. I'm done trying to convince you of anything. It's not that I don't care anymore. But I'll talk politics as soon as I've gotten around to telling my loved ones just how much I care about them. That might take a while.

10:28 Down Market and Back

This barely counts as a run, but it's been a while since I ran regularly and I just wanted to get started again. I don't want to injure my knees (again) so I'm taking it easy.

"Lost Values: A Study of Predatory Lending in Philadelphia" Released

I rarely write directly about the non-technical part of what we do here at my work. The one real mention I've made of our mission here was in hate. where I decided that the one best way to handle the problem of homeless guys on the street screaming at me for smiling was to do my job as well as I could-- building opportunity allows more people to live lives with dignity.

I work for The Reinvestment Fund. We're a non-profit financial services organization. We fund a lot of stuff-- under-privileged housing, sustainable energy, charter schools... stuff I'm proud to be a part of. I hold together the databases that help make our work possible, and help folks get their hands on usable data so we can do our job better.

TRF put out a publication today called Lost Values: A Study of Predatory Lending in Philadelphia. It is a study of predatory lending practices. It explains its concepts at both a macro and personal level. It also points to solutions that may lead to the regulation of the sub-prime lending market. In the right hands, this information may lead to more opportunity for people who need it. I'm proud that an organization I'm a part of does things like this.

Incidentally, the author of the study is Ira Goldstein, the husband of Linda from How We Evacuated from Hurricane Katrina. Small world.

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.

New Orleans Trip

We just spent the weekend in New Orleans visiting family and friends. A few things stood out:

1) New Orleans has not recovered much since the last time I was there in October 2005. When I was there to pick up our stuff, I remember a feeling in the survivors that they were going to make things happen, that they were going to turn this city around. Now people are faced with the lack of progress and seem tired. Some people who were pretty sure about staying are leaving as well.

2) Disasters happen to us collectively, but recovery happens individually. That is, the storm and subsequent damage happened to everyone in New Orleans, but how people rebuild their lives is very individual. This was illustrated by the number of houses we saw in Lakeview that were pretty well rebuilt standing right next to houses that seemed not to have been touched since the storm. Each of the millions of people affected by Katrina has a story, and none is exactly the same as any other.

3) As soon as we got off the plane, we started having Katrina conversations. We did not stop having conversations about Katrina until we were on the plane home to Philadelphia. This is alive and real for the city of New Orleans. Sadly, but realistically, Katrina was over for the people of Philadelphia about 3 weeks after the storm.

It was so very good to see my family and our friends. Nothing could change that. We even got to have a party with the people we evacuated with (see How We Evacuated from Hurricane Katrina). It was also devestating to drive through Lakeview and see how less than 1% of the houses are inhabited.

SQL Server: How to reset an IDENTITY column

I recently filled up a big table that had an IDENTITY column. IDENTITY columns are "auto-numbering" columns that can be set up to increment every time you insert a new row in a SQL Server table. This can be used to make a convenient ID column.

But if you delete records from a table, or even TRUNCATE the table, the IDENTITY column will not go back to zero, or whatever initial value you set up. To do this, you use a DBCC CHECKIDENT command:

DBCC CHECKIDENT('MyTable')

This example sets the IDENTITY column of MyTable back to its original seed value, if that is possible.

VB: Use a UDL file to create an unusual connection string

I write VBScripts and VBA stuff all the time to connect to SQL Server and other databases. You need a connection string to do this, and sometimes you don't know how to make a connection string for the database you are connecting to. I have in the past always Googled for connection strings when I didn't know what to do, but I recently learned a great trick to create my own. You use a UDL file to find it:

1. Create a new text file. Change its name to "sql_server.udl" or something appropriate with UDL as the extension.
2. Double-click this new file. The Data Link Properties application will launch.
3. Configure the connection as you want, including selecting the provider, server, user name, password, etc.
4. Test the connection if you like with the "Test Connection" button
5. Close the Data Link Properties editor, then open the new file with notepad, gvim, emacs, or some other text editor
6. The last line of the file will be your connection string! Here is an example for SQL Server:

; oledb
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=REALSECRET;Persist Security Info=True;User ID=tim;
Initial Catalog=master;Data Source=MyServer;Application Name=http://www.timallen.org

Some things to remember when configuring the connection:

• The Provider will default to ODBC, so be sure to set the provider first, as all other properties will go blank when you select a new provider.
• Don't forget that there is an "ALL" tab where you can set things like the "Application Name", which is nice if you will be trying to trace your app with SQL Profiler later.
• If you click on "Allow Saving Password", the password will be saved in plain text in your UDL file, so be careful with the file when you are done with it.

SQL Server: BACKUP... RETAINDAYS doesn't do what you might think

I was just scouting around for a way to make the backup set on a backup device expire and get overwritten periodically. I found the RETAINDAYS clause of the BACKUP command. I thought this might do it, but as it turns out, it merely *protects* a backup set for the number of days specified. That means that if you try to do a BACKUP..INIT of a backup device and it was unexpired backups, your backup will fail! Just another case of RTFM!

SQL Server: How to write to the event log

Here's how to write a message to the Event Log from SQL Server:

master..xp_logevent 50001, 'Test Event', 'ERROR'

The first parameter is the Error Number and should be 50000 or above for user-defined errors. The second parameter is the actual message. And the third is the severity. It can be a text value of 'Informational' (default), 'Warning', or 'Error'.

Command-Line parameter to open the Event Viewer on Another Computer

I use my Event Viewer to look at other computers' Event Logs. This is very usefule, for example, to see what is going on on a SQL Server without Remote Desktop.

Here is a way to start Event Viewer already connected to another computer:

 eventvwr.msc /computer=OTHER_NAME 

Where OTHER_NAME is the name of the other computer. I find this useful to set up as an external tool in Enterprise Manager. I set up one for each of my SQL Servers.

SQL Server: How to turn off event logs for successful backups

I recently noticed that I get an event log message on my SQL Server every time a successful backup is done:

Event ID: 17055
Information
18265 :
Log backed up: Database: MyDatabase, creation date(time): 2006/01/11(10:50:49), 
first LSN: 1212:97:1, last LSN: 1212:97:1, number of dump devices: 1, 
device information: (FILE=103, TYPE=DISK: {'MyDatabase02'}).

This is annoying, because I run transaction log backups about every ten minutes. This generates so many messages that my event logs recycle and I get no important messages.

The solution for this is to set the TRACEFLAG 3226. TRACEFLAGs are a mechanism in SQL Server 2000 that allow you to turn on and off behavior. Setting TRACEFLAG 3226 turns off logging for backup events. You can still set up event logging in your backup jobs, but have it only write to the event log if the backup fails.

There are two ways to set this traceflag. The following works when you can't restart the SQL Server service:

 DBCC TRACEON (3226, -1) 

This turns on traceflag 3226 globally until the next time the SQL Server restarts.

If you have the option of restarting the SQL Server service, you can set this as a startup parameter. In Enterprise Manager, right-click and choose "Properties", and on the "General" tab, click the "Startup Parameters" button. Type "/T3226" in the "Parameter" box, then click "Add". Click OK. Windows will ask if you want to restart your service, and you can either choose yes, or choose "no" and restart the service yourself later. This will keep the TRACEFLAG 3226 set permanently, and you won't get Event Log messages about successful backups anymore.

30:38 - 7th & Arch to 6th & Wharton loop (3.4 miles)

I've slacked off for about a week. Very bad. But it was a really good run, a little slower than usual. I feel motivated to go out again.

I finally got around to getting a RoadID (www.RoadID.com) that displays my insurance information and Sonia's phone number.

Mileage figure from http://www.gmap-pedometer.com.

28:18 - 7th & Arch to 17th & Lombard (3 miles)

A nice, fast run. I had taken two days off, which does not quite fit with my plan, but I had been feeling pretty beat up. Maybe I need to take three days off a week for now. It occurred to me that even though we live in one of the largest cities in the US, our lives play out in a quadrangle bounded by Washington Ave, Water Street, Vine, and 21st Street. Daniel goes to school out by the Art Museum. Other than that, we pretty much stay in here. That's kind of nice. I listened to the sounds of Philadelphia as I ran back; the saxophonist, the homeless guys asking for money, people speaking Chinese and German... I was in love with the sounds of my city. When I passed through the tunnel by Reading Market and Fillmore, a trucker blew his horn at an indecisive motorist and left me deaf for a few minutes. The bad with the good, I guess.

SQL Server: How to move a database file

I recently had to move the log file of a SQL Server database. Initially I thought it might be a simple alter database command, as in How to Change a Database File, but that does not work out:

-- Note: this will not work
alter database MyDatabase
modify file (name='MyDatabase_Log',
filename='c:\Program Files\Microsoft SQL Server\MSSQL\Data_log\MyDatabase_Log.LDF')
go
Msg 5037, Level 16, State 1, Server MYSERVER, Line 1
MODIFY FILE failed. Do not specify physical name.

I found the answer in the SQL Server 2000 Administrator's Pocket Consultant: you have to detach the database, move the file using Windows, and then reattach the database, specifying where the files have gone:

-- Put the database in single user mode
use master
exec sp_dboption MyDatabase, 'single user', true
go

-- Find out where the database files are located
exec sp_helpdb MyDatabase
...
C:\Program Files\Microsoft SQL Server\MSSQL\data\MyDatabase_Data.MDF
C:\Program Files\Microsoft SQL Server\MSSQL\data\MyDatabase_Log.LDF

-- Detach the database
exec sp_detach_db 'MyDatabase', 'true'
go

-- After this, you will need to move the files to their new locations using Windows

-- Reattach the database, specifying where the files are now located
exec sp_attach_db 'MyDatabase',
'C:\Program Files\Microsoft SQL Server\MSSQL\data\MyDatabase_Data.MDF',
'C:\Program Files\Microsoft SQL Server\MSSQL\data_log\MyDatabase_Log.LDF'
go

Mardi Gras 2007

Mardi Gras kind of snuck up on me this year. Obviously, it was a much bigger deal when we lived in New Orleans. It's a day off there in most jobs, and Sonia and I would walk down Bourbon Street and catch beads. It was always a real scene. Every year, a group of fundamentalists set up right in the middle of Bourbon Street with banners ("get born again-- ask me how..." etc); the last time we were there, we walked past just as a group of revellers rolled up a shopping cart with a blanket over it. They formed a circle around the fundamentalists, then pulled the blanket off of the shopping cart to reveal a paper mache cow painted gold. Then the revellers got on their knees and started to worship the calf. The funny thing is that the fundamentalists just seemed kind of amused. New Orleans is different. This morning as we got ready for work, Dan was watching PBS kids. There was a show about New Orleans. It had a little footage of previous years' Mardi Gras parades, but mostly it showed how people were reconstructing their lives almost two years after Katrina. And that kind of made me a little sad. When I first moved to New Orleans, if I told someone where I was from, they would say, "wooh wooh, Mardi Gras, party, etc etc". Now they pretty much always give the 25 degree head tilt and say, "Ohh...". I wonder how long it will take for that to stop happening.

30:24 - South Philly

A nice fast run down South to Front Street, down to Washington Ave until Moyamensig Ave-- http://www.gmap-pedometer.com/ measures that to be 3.4 Miles.

33:24 Around South Philly with Dan

I ran with Dan in the Baby jogger down to Water St. and back up. Nothing special, just a pretty nice run in the snow with my kid.

49:22 To Dan's school with Dan

The snow made it a little hard to run with the baby jogger, but I took Daniel almost all the way to his school. It took about 25 minutes to get there.

SQL Server: Which databases are set to auto close?

When you import a MSDE version of a database to regular SQL Server, it imports the "Auto Close" property. This is useless in regular SQL Server-- Auto Close is a property used to close down the database when the last connection is closed. This is needless overhead in the Server version of SQL server.

This VBScript script polls all the databases in a SQL Server instance to see their Auto Close property. This script could be modified to show other properties as well, of course.

' Drop this in a text file called show_dbs_with_autoclose.vbs,
' modify the server name to your server name,
' save it, then double click it, and you will get a message box for each
' database set to Auto Close
Dim svr 'As New SQLDMO.SQLServer
Dim db 'As SQLDMO.Database
Set svr = CreateObject("SQLDmo.SqlServer")
svr.LoginSecure = True
svr.Connect "MySQLServer"
For Each db In svr.Databases
	If db.DBOption.AutoClose Then
		WScript.echo db.Name & " is set to autoclose"
	End If
Next

The commented code is there to show how you would use this in VBA or Visual Basic-- this is nice, because then you get all the IntelliSense. Make sure to open references and add in SQLDMO Objects if you do this (of course, in VBScript, this is unnecessary (and impossible)).

SQL Server: How to change a database file name

Note: this is how to change the logical file name of a database file. To change the physical location of a database file, see How to Move a Database File.

I've had a couple of databases hanging around that have very bad logical file names. They were supposed to be test databases, but grew to be production databases (there is a law about this: there is no such thing as a prototype...)

I found a simple piece of code for how to change the names of these logical files:

alter database MyDatabase modify file (name='test', newname='MyDatabase')
alter database MyDatabase modify file (name='test_log', newname='MyDatabase_log')

This allows me to follow my naming convention and neot have to explain to the next guy why my database has logical file names that are animal names.

31:22 Old City/South Philly

A pretty standard run-- I just ran through Independence Mall down 4th to Washington Ave and back up 7th to Arch. I'm just pleased I'm managing to stick to my running schedule, more or less. I'll probably try to mix it up this next week-- different lengths of run, not all just 30 minute chunks.

30:05 Wash West past Rittenhouse Square

A slippery run in the snow. Feeling good so far, managing to stick to my schedule pretty well. Daniel was sick, so I didn't take him with me. A shame.

SQL Server: How to display your session id

Sometimes it's useful to know your session id when you're working from Query Analyzer or oSQL. Then you can tell if you're blocking or being blocked, etc. Here is the code for finding that out-- in this example, I'm running this from oSQL:

C:\>osql -S MyServer -E
1> select @@spid
2> go

 ------
     53

UPDATE: Also, there is a simple way to check this in SQL Query Analyzer: In the status bar at the bottom are listed the user name and session number in parentheses.

32:02 - Through Chinatown in the snow

It's starting to snow pretty heavily today-- I went out and ran, which was fun. I'm trying to get my running program going again, so I'm please that I've hit two days in a row. I did right at 3.5 miles, according to my footpod. The good news is that I didn't try to go out further-- I normally end up overshooting my goals and getting injured when I start running again.

SQL Server: How to shrink transaction logs

This is just a sequence that I forget sometimes-- every now and then I get a transaction log that is bigger than I want it to be. This shrinks it pretty well. One thing to be cautious about: make a full backup of the database after you do this.

use MyDatabase
go
checkpoint
go
backup log MyDatabase with truncate_only
go
dbcc shrinkdatabase(MyDatabase)
go
dbcc shrinkfile(MyDatabase_Log)

Now, make a complete backup of the database.

23:20 - Vine Street / Vurt by Jeff Noon

It's been a while since I wrote up a run, though I've been running pretty regularly. I think I'll be gearing up my running time again, as my yoga practice has come to a standstill.

I listened to an audio recording of Vurt, by Jeff Noon. It has to be the best audio book I've ever heard. It tells the story of a group of Manchester kids who are hooked on Vurt feathers, a drug that lets the user experience virtual reality as a dream (the mechanism is never made really clear, which adds to the intrigue). I picked up this recording in Denver well over ten years ago, and I never get tired of it. It ranks right up there with Hitchhiker's Guide to the Galaxy.

VBScript/VB: How to write error messages to the Event Viewer

I work in a small IT shop where I do all of the internal programming, but also pitch in on help desk duties. Users often call because they got an error message, but cancelled it immediately without reading it.

I've often thought that it would be a good idea to write an entry to a log with any error message text that a program displays, so that a tech can read the error log when a user has cancelled the error message away.

I write a lot of VBScripts and VBA code. In Windows, there is a mechanism for putting messages directly in the event viewer log:

Set WshShell = CreateObject("WScript.Shell")
WshShell.LogEvent 1, "Error 972: Could not invert A-Frame"
WshShell.LogEvent 4, "Information Message: Everything went fine."

This has now become a standard part of my standard error-handler block in VB:

eh:
dim WshError
Set WshError= CreateObject("WScript.Shell")
WshError.LogEvent 1, "Error " & err.number & ": " & err.description

I'm going to start adding this for all of my programs whenever I display messages to my users. What I think would be cool would be if they API for displaying message boxes would include a "WRITE TO EVENT VIEWER" parameter as well.

VBScript: how to run a program from vbscript

I always need this one: how do you run a program from within VBScript? For example, you may need to launch notepad or wget from a VBScript. Here is the code to launch notepad:

sub shell(cmd)
    ' Run a command as if you were running from the command line
    dim objShell
    Set objShell = WScript.CreateObject( "WScript.Shell" )
    objShell.Run(cmd)
    Set objShell = Nothing
end sub

shell "notepad"

Put that in a text file called "run_notepad.vbs", doubleclick it, and notepad will run. This may not seem too useful, but imagine that you use VBScript to run a series of WGETs, for example, based on input from SQL Server.

---

UPDATE

I have had a lot of requests for enhancements to this program. The following is the cadillac version. It calls an EXE and asks the user for parameters to pass to the EXE.

 
sub shell(cmd)    
    ' Run a command as if you were running from the command line
    dim objShell
    Set objShell = WScript.CreateObject( "WScript.Shell" )
    objShell.Run(cmd)
    Set objShell = Nothing
end sub

dim file_name
' Ask the user for a parameter
file_name = inputbox("What file to see?", "File name?", "c:\boot.ini")
if file_name <> "" then
    ' The user gave the parameter.  Open the program
    ' with the value the user gave.
    shell "C:\WINNT\system32\notepad.exe " & file_name
else
    msgbox "User cancelled.", 16, "User cancelled"
end if

888-226-3713 - Blue Green Resorts (Be careful)

I get a call from the same number on my cell phone every day: 888-226-3713. When I answer the call, the caller hangs up immediately.

I finally decided to call the number back from my work phone. A recorded announcement thanked me for calling back, and said that the call was from Bluegreen Resorts. The recording pitched some 3 day trip to Vegas.

I thought this sounded fishy, so I Googled "bluegreen resorts", and about the 3rd link was for The Ripoff Report.

I have no direct experience with Bluegreen Resorts, but I would say that in the very least, you should proceed with caution if you deal with them.

(Note: that number was 8882263713, Bluegreen resorts.)

Bikram Yoga Session after months of inactivity

These last few months have not been good for my Bikram practice. I've probably made it in less than 10 times in the last three months. Between getting sick for a while, going to Barcelona, and the warmth of the bed vs. the cold of the street at 5AM, it has started to seem like less and less of a good idea to go to Bikram. Which is a shame. I really like the studio where I go (http://www.bikramphiladelphia.com). I've gotten to be friends with the owner there, Joel. And I feel better when I do it. So I'm going to try and get in more for the next week or two and see what comes of it.

Perl program to calculate major triad chord progressions

I've been playing guitar for 31 years (good lord I'm old). But I only recently got interested in learning the theory behind chord progressions. I wrote this simple piece of code to calculate major triad chord progressions in all of the keys. I suppose it could be extended easily to create minor chord progressions as well.

use strict;

sub chord_progression
{
 # The parameter is the index of the root note in the @notes array
 my $i = shift;
 my @notes = ('C', 'C#/Db', 'D', 'D#/Eb', 'E', 'F', 'F#/Gb',
                 'G', 'G#/Ab', 'A', 'A#/Bb', 'B');
 my @intervals = (0, 2, 4, 5, 7, 9, 11, 12);

 # Create a scale based on the intervals in a major scale
 my @scale = map {$notes[($i + $intervals[$_]) % 12]} (0..7);

 # Create triads.  Increment each note within the scale.
 # All notes in the chord progression fall within the
 # major scale.
 my ($root, $third, $fifth) = (0, 2, 4);
 my @name = ('Maj', 'min', 'min', 'Maj', 'Maj', 'min', 'Dim', 'Maj');
 for (0..7) {
  printf "%6s %3s: %6s, %6s, %6sn",
      $scale[$root], $name[$_], $scale[$root], 
               $scale[$third], $scale[$fifth];
  $root++;
  $third++;
  $fifth++;
  $root %= 7;
  $third %= 7;
  $fifth %= 7;
 }
 print "n";
}

# Call the subroutine for each note in the scale
for (0..11)
{
 chord_progression($_);
}

This produces output like this for the 12 root notes:

     C Maj:      C,      E,      G
     D min:      D,      F,      A
     E min:      E,      G,      B
     F Maj:      F,      A,      C
     G Maj:      G,      B,      D
     A min:      A,      C,      E
     B Dim:      B,      D,      F
     C Maj:      C,      E,      G
----------------------------------
 C#/Db Maj:  C#/Db,      F,  G#/Ab
 D#/Eb min:  D#/Eb,  F#/Gb, A#, Bb
     F min:      F,  G#/Ab,      C
 F#/Gb Maj:  F#/Gb, A#, Bb,  C#/Db
 G#/Ab Maj:  G#/Ab,      C,  D#/Eb
A#, Bb min: A#, Bb,  C#/Db,      F
     C Dim:      C,  D#/Eb,  F#/Gb
 C#/Db Maj:  C#/Db,      F,  G#/Ab
...

18:51 - Center City

I went for a quick run today, bundled up as it has gotten very cold. I just looped around center city and watched the people. It started snowing just a little after I stopped-- a pity, I've always loved running in the snow.

12 years

I celebrated 12 years today. Not much to say about that, another little miracle in a long series of little miracles.

Back from Rome and Barcelona.

We're back from Spain now. We were in Barcelona from 30-Dec-06 - 14-Jan-07. We stayed with our friends Beatriz and Ricardo, who are kind of like Daniel's surrogate grandparents.

When we landed in the Madrid Airport, we saw the damage from the ETA bombing. Folks, it was a lot worse than the US news made it out to be. The news here in the US said that it was a minor bombing with about 15 people treated for ear injuries. In reality, an entire four story parking lot was completely demolished, and two people were killed. I was taking photos of the broken glass all across the front of the airport when Sonia told me to look at the parking lot across the street. It was unbelievable. There was even a slight crater where the parking lot was before. For days afterwards the rescue workers dug to find the bodies of two innocent Equadorian immigrants who could not get out of the parking lot when it was evacuated.

ETA are a bunch of gillipollas. How does killing a couple of innocent immigrants forward their cause? The one thing I am sure of is that if there ever were an independent Euskera, I certainly wouldn't want that bunch of bloodthirsty clowns to be in charge of it. I doubt that the Basque people want that either.

Sonia and I were able to go to Rome for a weekend and leave Daniel with Ricardo and Beatriz. Rome was great. We took about 200 photos (at http://picasaweb.google.com/sontida). It was impossible to take a bad photo in Rome.

We're glad to be home, but are already planning our next trip.