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.