Blackberry Google Tools

I have a Blackberry through my work. Google has put out a bunch of great tools for the Blackberry, including really effective versions of Google Maps and GMail. You can download all of the Google Blackberry tools at the same time and keep them up to date from this URL: http://www.google.mobi When you surf to this URL on your Blackberry, it will do over-the-air installations of all of the tools that you choose. My favorite is the Google Maps application, which does a lot of things that the web version won't do. I was able to surf right to Plaza Catalunya in Barcelona and actually see people-- that's the level of detail it has.

SQL Server: How to concatenate field text across rows

Today I needed to concatenate text in a fields across rows. Specifically, given a table with columns for user accounts and quotas, report summing the user accounts, a list of the quota labels, and the summed quota amounts.

What you would want is something like this (But this won't work!):

-- WARNING: THIS WON'T WORK!
select user_accountID, concat(quotaNo), sum(quotaAmount)
  from user_accounts
 group by user_accountID

Alas, in SQL Server 2000, there isn't a built-in function to do this. About the only way I know of to do this is to custom build a user-defined function for the particular table you are working with. Here is my example:

-- Create a test table
create table user_accounts (user_accountID varchar(32), quotaNo varchar(32), quotaAmount float)

insert into user_accounts values('user_account1', 'quota1', 100)
insert into user_accounts values('user_account1', 'quota2', 200)
insert into user_accounts values('user_account1', 'quota3', 400)

insert into user_accounts values('user_account2', 'quota4', 800)
insert into user_accounts values('user_account2', 'quota5', 1600)

go

-- Create the user defined function
create function concat_quota_number(@user_account varchar(20))
returns varchar(200) as
begin
  declare @str varchar(200)
  select @str = coalesce(@str + ', ', '') + quotaNo from user_accounts where user_accountID = @user_account group by quotaNo
  return @str
end

go

-- How to use it
select user_accountID, dbo.concat_quota_number(user_accountID) quota_labels, sum(quotaAmount) quota_sum
from user_accounts
group by user_accountID, dbo.concat_quota_number(user_accountID)

user_accountID	quota_labels	quota_sum
user_account1	quota1, quota2, quota3	700.0
user_account2	quota4, quota5	2400.0

Adobe 8.0 installation error: how to beat it

This morning I decided I was going to upgrade to Adobe Acrobat Reader 8.0. I tried to do the installation directly from the Adobe web page (http://www.acrobat.com), but I continuously got this error message:

You're resuming a download, but the file on the server has changed. Would you like to restart the download process?

After about five tries at this I got annoyed. The simple solution is this (as long as you are on a Microsoft Windows machine: Download the file directly from Adobe's FTP site:

1. In explorer, open ftp://ftp.adobe.com/pub/adobe/reader/win/8.x/8.0/enu/


2. Download "AdbeRdr80_en_US.exe" by Right-clicking it and choosing "Save As..."


3. Run the resulting file

I don't know what caused this error, but this simple solution gets rid of it. If you are on a different operating system, you can still go to the FTP site, but you'll have to fish around a little to find your file.

SQL Server: How to do old and new style left joins

I like using the newer ANSI-92 standard JOIN statements, and I believe that once you get used to them, they are easier to understand. They certainly are easier to read, because they separate the function of joining tables from the filtering taking place in the where clause.

That said, I get SELECT statements out of applications sometimes by running a trace, just to see what is going on in an application. A lot of times, these SQL statements don't use the new standards. You can't use old style left joins with the new ANSI-92 standard.

So this code shows how to use both styles. I create a couple of temporary tables for this example, one called MASTER and the other SLAVE. Our objective is to see all rows of MASTER, no matter whether there are matching rows in SLAVE:

-- Create and populate our temporary tables
create table #master (id int)
create table #slave (id int, descrip varchar(32))

insert into #master values (1)
insert into #master values (2)
insert into #master values (3)

-- Note that we don't put a description for 2
insert into #slave values (1, 'one')
insert into #slave values (3, 'three')

-- old left join-- the * goes on the "MASTER" side of the equal sign
select m.id, s.descrip
  from #master m, #slave s
 where m.id *= s.id

id	descrip
1	one
2	NULL
3	three

-- ANSI-92 left join
select m.id, s.descrip
  from #master m
  left join #slave s on s.id = m.id

id	descrip
1	one
2	NULL
3	three

-- Drop our temporary tables
drop table #master
drop table #slave

These two SELECT statements produce identical output. I won't say that these two JOIN styles are identical-- there are examples of how they differ out there somewhere. But if you're stuck using the old style, it helps to know the syntax.

SQL Server: Restoring from someone else's backup device

This morning I received a SQL Server backup file from someone else. I was to restore the backup file to a new database. Before restoring it, I decided to verify it to make sure everything was okay:

1> restore filelistonly
2>    from disk='C:\datanewdb.bak'
3> go
Msg 3201, Level 16, State 2, Server SQLDB, Line 1
Cannot open backup device 'C:\datanewdb.bak'. Device error or device off-line. See the SQL Server error
log for more details.
Msg 3013, Level 16, State 1, Server SQLDB, Line 1
RESTORE FILELIST is terminating abnormally.

That was frustrating. But I noticed that the error said the device was off-line. That's a different error from what you get when the file is just corrupt.

I figured out that I had been sent the file from a backup device, not a straight backup file. I've thought for a long time now that the naming convention should be different for backup devices-- maybe .BKD instead of .BAK.

In any case, what I needed to do was to create a new backup device from the backup device file, then restore to a new database. Here are the steps I took which are suggested as a program of recovery (nudge nudge wink wink):

1. I created the backup device using the third-party's file:

1> exec sp_addumpdevice @devtype='disk',
2>                      @logicalname='newdb',
3>                      @physicalname='c:\datanewdb.bak'
4> go
(1 row affected)
'Disk' device added.

2. I verified the backup device:

1> restore verifyonly
2>    from newdb
3> go
The backup set is valid.

3. I got the names of the files in the database backup set (I'll need these later when I restore):

1> restore filelistonly
2>    from newdb

olddb	D:\Microsoft Sql Server\MSSQL\Data\olddb.mdf	D	PRIMARY	2088828928	15728640000
olddb_log	D:\Microsoft Sql Server\MSSQL\Data\olddb_log.ldf	L	NULL	1422065664	5242880000

4. I restored the database:

1> restore database newdb
2>   from newdb
3>   with move 'olddb' to 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\newdb.mdf',
4>        move 'olddb_log' to 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\newdb_log.ldf'

Processed 167088 pages for database 'newdb', file 'olddb' on file 1.
Processed 1 pages for database 'newdb', file 'olddb_log' on file 1.
RESTORE DATABASE successfully processed 167089 pages in 339.970 seconds (4.026 MB/sec).

Note that when I restore the database, I have to specify some MOVE clauses to move the physical data and log files from where the third party stored them on their old system to where I want them on my system.

SQL Server: How to rename an index

When you create a table with a primary key in SQL Server, SQL Server creates an index for you with an automatically generated name, like PK__acctreg__29819341. I find the naming convention they use to be annoying, so I wanted to be able to change the name. Here's how:

-- to find the names of your current indexes
use my_database
select tab.name table_name, idx.name index_name
  from sysobjects tab
  left join sysindexes idx on idx.id = tab.id
 where tab.xtype = 'U'
 order by 1

-- to rename an existing index
exec sp_rename 'company.PK__company__1ED599B2', 'company$companyID', 'INDEX'

SQL Server: What service pack am I on?

Anyone remember the SQL Slammer virus? It turns out that it attacked SQL Server databases (and SQL Server DBA's) that were not protected by the latest Service Pack. I sit around and scare myself by asking myself questions like, what service pack am I on?

Here is a simple query to tell you what service pack you are on and some other interesting information about your SQL Server instance:

select serverproperty ('servername') [Server Name],
       serverproperty ('productversion') [Product Version],
       serverproperty ('productlevel') [Product Level],
       serverproperty ('edition') Edition,
       serverproperty ('licensetype') [License Type],
       serverproperty ('numlicenses') [# Licenses]

Of course you have to connect to the server you are interested in before running this query. The Service Pack level will be in the "Product Level" column.

SQL Server: How to show tables with a column name like...

In SQL Server, I often seek which table has a column with a name that I am not sure about. For example, I need the table and column name for a column like "current loan balance".

I use this query to find which table has a column name like...:

use loan_db

select tab.name table_name, col.name column_name
  from sysobjects tab
  left join syscolumns col on tab.id = col.id and tab.xtype = 'U'
 where col.name like '%current%balance%'
 order by 1,2

The only thing you have to work out then is the LIKE clause.

SQL Server: How to do a fuzzy search

I was asked yesterday to do a fuzzy search in SQL Server. The request was to find some numbers in a field that fell within a certain tolerance.

I worked out how to do this pretty simply using a BETWEEN clause with a fuzziness factor to go with it. In a simplified example, I'm looking for all employees within 3 years of 30. I parameterized both the age and the fuzz factor to make changes to this simple:

-- Here I just create a temporary table for the example.
create table #employee (employee varchar(32), age int)
insert into #employee values ('John', 32)
insert into #employee values ('Mary', 24)
insert into #employee values ('Sam', 40)
insert into #employee values ('Sarah', 28)
insert into #employee values ('Charles', 29)
insert into #employee values ('Henry', 31)

-- Here I declare variables to hold the age to look for
-- and the "fuzziness" of the search
declare @fuzz int
set @fuzz = 3
declare @seek_age int
set @seek_age = 30

-- This is the actual search
select employee, age
  from #employee e
 where e.age between @seek_age - @fuzz and @seek_age + @fuzz

Employee	Age
John	32
Sarah	28
Charles	29
Henry	31

Increasing the @fuzz value increases the possibility of finding records.

In the actual case, I used the BETWEEN clause as the joining clause in a left join to fuzzily join records. This looks pretty scary but works. The following example does this to create a list of employees matched to other employees within 3 years of the same age:

-- Find other employees within three years of an employee's age
declare @fuzz int
set @fuzz = 3

select test.employee test_employee, test.age test_age,
       fuzzy.employee fuzzy_employee, fuzzy.age fuzzy_age
  from #employee test
  left join #employee fuzzy
    on test.age between fuzzy.age - @fuzz and fuzzy.age + @fuzz
   -- here we don't want an employee to match him or herself
   and test.employee <> fuzzy.employee

test_employee	test_age	fuzzy_employee	fuzzy_age
John	32	Charles	29
John	32	Henry	31
Mary	24	NULL	NULL
Sam	40	NULL	NULL
Sarah	28	Charles	29
Sarah	28	Henry	31
Charles	29	John	32
Charles	29	Sarah	28
Charles	29	Henry	31
Henry	31	John	32
Henry	31	Sarah	28
Henry	31	Charles	29

A NULL result mean that that employee has no co-workers with 3 years of their age. Again, the @fuzz factor can be increased to find more matches.

SQL Server: How to list the columns in a table

This is a simple script to list the columns in a given table in SQL Server. It uses the SYSCOLUMNS, SYSTYPES, and SYSOBJECTS system tables:

use mydatabase

select left(col.name, 32) col_name, left(typ.name, 32) col_type, typ.length
  from syscolumns col
  left join sysobjects tab on tab.id = col.id
  left join systypes typ on typ.xtype = col.xtype
 where tab.name = 'TABLE NAME' and tab.xtype = 'U'

go

Bikram Philadelphia Studio

I had a great session this morning. The instructor this morning was Alex, who is currently my favorite instructor-- she seems to get me to do things today that I thought were weeks or months away. I worked a lot on my focus today. I had some difficulty because my left knee has really started to bother me. I think this is because I really upped my running schedule a few weeks back. But the advice I've gotten from Bikram's book is to go ahead and try to continue, but cautiously.

Bikram with a Polar Heart Rate Monitor

I run with a Polar Heart Rate Monitor sometimes. I have been curious for some time about what happens to my hear rate during Bikram, so I wore one during this morning's session. I told my instructor, Alex, what I was doing, and she suggested that I check my hear rate prior to and after the Camel Pose (Ustrasana). She predicted that my heart rate would go up a lot. She was right. Prior to the posture, my heart rate was 122 bpm. After it was 164. Anyway, here were my numbers for the session: Total time: 1:26 Average: 134 KCal: 818 Fat %: 40% I generally get numbers like this when I run about an hour and a half.

Perl script to split MP3 files

This is a simple script I used recently to split a large (50 minute) MP3 file into a series of smaller files. I used the MP3::Splitter module. MP3::Splitter is very versatile, so what I am doing does not use all of its functionality. But this does a good quick and dirty job of chopping up a file into bits. This comes in handy so you don't have a huge file to fast forward through on your iPod or other MP3 player.

The first two parameters of mp3split are the name of the file and a hash with options-- the only one I use is to set "verbose" to true. After that are a series of array references that describe each bit I want written out to a file.

The first parameter is the start time. I start the very first piece at 0 minutes. After that, each bit starts relative to the end of the last bit-- this is marked by the > symbol.

The next parameter shows how long the bit should be in seconds-- I chose to make each one two minutes long.

Since my MP3 file was about 50 minutes long, I just made 25 two minute bits. The last bit has a special marker as the finish: =INF. This means to use all of the rest of the file.

use MP3::Splitter;
mp3split('01-italian.mp3', {verbose => 1}, 
 ["0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "120"],
 [">0", "=INF"]
);

Session at Bikram Philadelphia

After swearing I'd get in to the studio (http://www.bikramphiladelphia.com) for a session all last week, I finally made it in today. Except for the half session in my office, it's been two weeks since my last session. Forgive me, Bikram, for I have sinned.... The session was good-- I actually get a little stronger when I take a break. Except this break was too long, so I'm actually a lot weaker. My one insight recently has been that you really have to relax between postures. My instructor, Joel, always says that Hatha Yoga is different partially because we relax completely between postures. But I really had not done it before. It just made me think about how Earl Campbell used to stay completely still until the entire pileup was finished when he got tackled. At first people would think he was hurt, but after he did it a zillion times everyone realized he was just having a rest. Part of the reason for relaxing completely between postures is to allow the effects of the postures to happen, as well. A lot of postures restrict part of the body on purpose-- then when you relax afterwards, that part is unrestricted. Kind of like wringing out a rag. And I was pretty wrung out after this morning's session.

Half session in the office

I've been having some trouble getting into the yoga studio, but I want my practice to go forward, so I tried doing something I've done a couple of times before: a half session on my own. Basically I did one set of each pose as opposed to two. It took me about 40 minutes. I felt great when I was done.

It was lacking some, though. There was no heat (which actually was not all that great of a problem!). And no one was egging me along, so I had to concentrate harder on not giving up a pose when it got difficult.

But I think this may be a good way to keep things going when I can't get to the studio.

By the way, I found this amazing video of Kristina Kireeva that made me realize that I could go a lot further in the Cobra Pose (Bhujangasana).

How to copy a SQL Server database to a different database

It is useful to be able to restore a database to a different database.
For example, you may want to create a development database with the
same structures and data as the production database.

Here are the steps:

1. Find out the file names of the database you are copying.

restore filelistonly
   from disk='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\my_prod.bak'

This will list the names of the database files.

2. Restore the database to the new database, moving the datafiles

restore database my_test
   from disk='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\my_prod.bak'
   with move 'my_data' to
             'c:\Program Files\Microsoft SQL Server\MSSQL\Data\my_test.mdf',
        move 'my_log' to
             'c:\Program Files\Microsoft SQL Server\MSSQL\Data\my_test.ldf',
        stats=5

This will do the restore and move the data and log files to new locations.
If you don't include the "move" parameters, it will try to restore over the
existing files and you will get an error like this:

Msg 1834, Level 16, State 1, Server TRF-SQL01, Line 1
The file 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\my_data.MDF'
cannot be overwritten.
It is being used by database 'my_prod'.

Msg 3156, Level 16, State 1, Server TRF-SQL01, Line 1
File 'my_Data' cannot be restored to
'c:\Program Files\Microsoft SQL Server\MSSQL\Data\my_data.MDF'.
Use WITH MOVE to identify a valid location for the file.

Perl one-liner: sort a string of numbers

This is a stupid pet trick with Perl.

I was recently writing an email to a cow-orker and had a long list of numbers to put in the email. I had them like this:

56165, 55163, 56137, 56104, 56115, 55108, 56971, 55768, 56682, 56585, 55940

My cow-orker likes things to line up and be in order, so I needed to sort them. I reckoned I could use split, sort, and join in Perl to make this job easy. It worked. Here is the one-liner I came up with:

perl -e "@_=split(/, /,'56165, 55163, 56137, 56104, 56115, 55108, 569
71, 55768, 56682, 56585, 55940');print join(', ',(sort @_))"

55108, 55163, 55768, 55940, 56104, 56115, 56137, 56165, 56585, 56682, 56971

Shiny.

Hate 2: City Face

A few weeks back, I talked about how I got yelled at and threatened by a crazy guy on the street here in Philadelphia (Hate). This same thing happened to me again a week ago. Another crazy homeless guy yelled at me. I tried to play it off, but it kind of got to me. The first time it could have been a chance thing. But twice in a month? Maybe I was provoking it somehow.

I asked a native Philadelphian about it, and he told me to stop smiling in the street. I needed to adopt a "city face", not smiling at strangers, not looking at strangers.

It sounds awful, but I've been trying it out. The fact is that I understand why city folk walk around with zero expression on their faces. No one messes with me now. Homeless guys don't bother asking for change.

It has its downside-- I walked right past a friend without seeing him, for example. On the other hand, I understand that there are a lot of people out there who just aren't right.

Microsoft SQL Server: how to truncate the ERRORLOG

The Microsoft SQL Server error log is stored at c:/Program Files/Microsoft SQL Server/MSSQL/LOG/ERRORLOG (or similar). This can get very big, because it only recycles when the instance is restarted. When it's too big, it becomes difficult to parse it for errors.

When the instance restarts, the ERRORLOG is renamed to ERRORLOG.1 and a new ERRORLOG is started. As this hapens again and again, the number of the file increases.

You can force the ERRORLOG to recycle without restarting the instance with this simple command:

osql -S myserver -E -Q "exec sp_cycle_errorlog"

Or, of course, you can run the command "exec sp_cycle_errorlog" from Query Analyzer.

Microsoft SQL Server: setting your editor in oSQL

This is another oSQL trick. I like to use gvim as my text editor. It reinforces the fact that I am a dinosaur. And it has great regular-expression-based pattern matching.

There was a command to set the editor in SQL*Plus, so I wantetd to be able to do the same in oSQL. The default editor in oSQL is the program "edit. You invoke it from within oSQL by typing "ed".

You set the editor in oSQL by setting the EDITOR command line variable. Here is how I set mine:

set EDITOR=gvim -c "set ft=sql"

This also calls the VIM command to set the syntax to SQL, which gives you pretty color-coding and such.

This tip could be adapted to UltraEdit and similar.

Microsoft SQL Server: how to run a script from within oSQL

I like to use oSQL to do a lot of my work in SQL Server. I'm used to using SQL*Plus in Oracle, so I'm comfortable with the command line. Also, it reassures me to know that I can do my job even if I only have a command line to work with.

There are a few things I liked about SQL*Plus that I had not found in oSQL until recently. One was the ability to run a script from within an oSQL session. You do this with the :r command:

c:>osql -S myserver -d mydb -E
1>:r my_script.sql
2> go
... The script runs ...
1> exit

You could have run this all from the command-line one time through like this:

c:> osql -S my_server -d my_db -E -n -i my_script.sql

However, the :r command lets you read in scripts within an already open session.

1:02:33 - BF Parkway past boathouse row (6.3 miles) - LearnItalianPod.com

It was a pretty day, so although I was pretty beat from Bikram this morning, I decided to run. I ended up clocking just tover an hour. Currently I have Italian lessons loaded on my MP3 from http://www.learnitalianpod.com. I really like LearnItalianPod. They are an Italian woman and man that do a simple dialogue in each PodCast. Their lessons are about 10 minutes each, which are enought that I can listen to a couple when I walk to and from work. They seem pretty simple-- of course, I can't vouch for that, because learning Italian when you already have pretty good Spanish is really simple. It's not exactly the same (and my Italian co-worker tells me it's really annoying to listen to a Spaniard who thinks they *are* the same). But some essential stuff is in both-- gender matches in most cases, and probably the most important: 17 shared verb forms. So once you've got your brain around the Past Subjunctive in Spanish, you don't have far to go to learn it in Italian. If you've been reading my blog for a while, you may remember that I was studying Mandarin for a while. I haven't abandoned that, but I was getting a little burnt out on it. Italian's great because I'm already somewhat conversent in it after just a few weeks (I talked for quite a while with some Italian folks in San Diego after the ESRI conference). It would have taken years to get that far in Mandarin.

Using the Perl Xbase module to read xBase databases

I recently had occasion to read some xBase database files (e.g. dBase or FoxPro files). What I was doing needed some of the capabilities of Perl. I found a module that reads xBase databases directly: Xbase.

Here is a simple set of code to read all of the records in an xBase database:

use strict;
use Xbase;

# Create a new Xbase object
my $database = new Xbase;

# Open the database
my $db_name = 'C:exampledatabase.dbf';
$database->open_dbf($db_name);

# Get the last record number
my $end=$database->lastrec;

# Go to the first record
$database->go_top;

# Loop through the records
for my $i (1..$end)
{
 # Print the record number
 print $database->recno . ". ";

 # Get the fields in this record
 my @fields = $database->get_record;

 # Loop through the fields
 for my $f (0..$#fields)
 {
  # Print the values of each field
  print $fields[$f] . "\t";
 }
 print "\n";

 # Go to the next record
 $database->go_next;
}


# Print information about the database,
# including the names of the fields
print $database->dbf_type . "\n";
print "Last update: " . $database->last_update . "\n";
print $database->dbf_stat;

# Close the database
$database->close_dbf;

This code is adapted from the perldoc documentation of the Xbase module.

Hate

This morning I walked out of my house to catch the trolley to West Philadelphia to see a friend preach at a Mennonite church. I turned down Chestnut street. The sun was shining, and I was happy. So I smiled.

I heard a voice yell out, "fucking faggot!". I turned to see who was getting yelled at. On the other side of the sidewalk, a young black man was looking right at me. He repeated, "fucking faggot! what you smiling at?". I then realized he was yelling at me. He ran right at me and stopped a foot away, yelling, "FUCKING FAGGOT WHAT ARE YOU SMILING AT?" Just then, a father and his young son were right next to us, and they broke into a dead run to get away. Everyone on the street was looking at us.

I don't know why I did what I did next. I smiled at him, turned away, and signaled to the crowd that it was all right. Then I walked away. I could hear him behind me, murmuring, "faggot... fuck faggots... smiling mother fucker".

I guess I could have died back there. I'm glad I didn't try to fight him. I wondered how he saw me smiling and concluded, "faggot". And I wonder about what suffering he is in.

As I walked away, a toothless old homeless guy came up to me and signaled that he wanted some money to get something to eat. I smiled at him and said, "I'm sorry. I'm not in the mood to help now."

I'm trying to wrest a lesson for myself out of all of this.

UPDATE: See Hate 2: City Face.

19:07 Around Old City (2.2 Miles) - Els Pets/Miguel Bose

It's hot today. I actually like running in hot weather. When I was in Barcelona it would be cooking in the summer and I would freeze a water bottle and go running with it. The doctors say that this is what caused my last embolism... no, seriously, it was okay. Barcelona was just beautiful to run in-- I would run up over the hil from Guinardo into Parq Guell with Daniel in the baby jogger. I miss those days.

I went out and did a loop through old city today. It's pretty here, too, but in a different way. People looked at me like I was nuts running in the heat, and I guess I was.

I listened to Els Pets and Migúel Bosé on the MP3. Guess I miss Spain a little.

1:30 Glen Echo with DC Harriettes and Harriers (DCH4)

I moved to Washington DC in 1989. I was stationed there after I rotated out of Okinawa, Japan with the Marines. DC was a big chunk of my life; on and off I lived there for about eight years.

One of the first things I did in DC when I first arrived was to start running with the Hash House Harriers. It was the running equivalent of a rugby club-- there were a lot of songs and beer drinking, and many of the people running were either attached to the embassies or had gone overseas with the military or the Peace Corps. One of the officers I was assigned with was involved with the hash and encouraged me to come.

Some of my oldest friendships date from that initial hash-- for example, my friend Lou, who I'm having brunch with today, was at that first hash, 16 years ago. I eventually drifted away from the hash-- it was difficult to go to the hash when I first stopped drinking.

Sonia and Daniel are still in Barcelona, so I realized yesterday morning that I could make the drive down to DC from Philadelphia and see my old friends. I hit pay dirt (sort of): a lot of the same people who ran 16 years ago were there yesterday. The run was in Glen Echo, where I had lived back then, so it was a run down memory lane for me.

1:08 Ben Franklin Pkwy to Boathouse Row (10K) - Antonia Font/Alegria

I decided to go for a long run today-- I realized two miles out that I only had to run one mile more to make a 10k (I was running out and back), so I did.

I listened to an album that was recommended to me by a catalan girl in FNAC in Barcelona-- Antonia Font/Alegria. It was fine, didn't really trip my trigger the way Els Pets does. Actually, by heart wasn't in it anyway. I mostly was running to get my mind off of a problem.

It worked, somewhat. I've been trying to be mindful lately, and I received the suggestion that I concentrate on my breathing. I ran along, thinking, "Breathing in, I move forward. Breathing out I move forward". That didn't work for me. I couldn't stay focused on the moment at all. Finally I hit on the most basic: "breathing in I breathe in. breathing out I breathe out." It sounds laughable, but I can actually concentrate on the moment without disassociating by telling myself that.

I noticed after doing this for a while that I sometimes have trouble synchronizing my breathing with the tension in my abdominal muscles. I actually fight my breath a little bit. I relaxed my abs and relaxed them breathing in, tensed them breathing out. That helped a lot.

I was slow as hell, that I can say. The last time I paid attention to my 10K time, I was 27. I ran a sub-40 10K at the Cherry Blossom in DC. Of course, that was 15 years ago, and it was a race-- I didn't have to stop for traffic lights. Still, how the (marginally) mighty are fallen.

My Dalai Lama story

Today is the Dalai Lama's birthday. He's 71. In honor of this occasion I'll tell my Dalai Lama story.

In 1996 I was on a project for Oracle in Denver. I was to meet some friends at a bar in lower downtown. They were late arriving. Newly sober, I was uncomfortable hanging out in the bar, so I decided to walk around the block. I saw a group of people in saffron and maroon robes on the sidewalk near a hotel, holding signs and chanting. I didn't know what they were there for, but they all seemed pretty happy.

A black limosine pulled up, and a group got out. They rushed into the hotel, and a large crowd formed around the front of the hotel. They were having a press conference, and it was visible through the front glass of the hotel. I ran up and joined the crowd, still unaware of who was giving the press conference. I was working my way through the crowd to see who was inside, when suddenly the crowd parted in front of me, and I fell forward towards the glass. I put my hands up to break my fall and thumped the glass.

Just at that moment, I saw the Dalai Lama answering reporters' questions. He stopped to see what the ruckus had been, saw me with my hands still up above my head against the glass, and must have thought I was waving at him. I'll never forget this moment: He looked me straight in the eye, smiled, and waved back at me. I'm not sure what he was thinking, but he seemed to be amused.

Beau and Elena's Wedding/La Boda de Beau y Elena

I had the huge and unexpected honor of marrying my friends, Beau and Elena, while we were visiting Spain. Beau and Elena were the people who we evacuated from Katrina with. Here is what I said:

...Y ya llegamos al final de la boda y el comienzo del matrimonio. Sólo os puedo compartir mis propias experiencias con vosotros.

...And now we've come to the end of the wedding and the beginning of the marriage. I can only share my own experiences with you.

Hace unos años conocí a una chica catalana. Era muy guapa. Nos reimos y lo pasamos bien juntos, y al final de un tiempo decidimos casarnos. No tuvimos ni idea de que haciamos.

About seven years ago I met a Catalan girl. She was very pretty. We laughed a lot and had a good time together, and after a while we decided to get married. We had no idea what we were doing.

Sònia y yo hemos experimentado, como decis, la prosperidad y la adversidad. Han habido momentos en los que nos hemos preguntado, "¿y porqué no me hubiera podido enamorarme de alguien de mi propio pais?"

Sònia and I have experienced, as you say, better and worse. There have been moments when we have asked ourselves, "why couldn't I have fallen in love with someone from my own country?"

Nos dijeron en nuestra boda que, como mínimo, uno de nosotros siempre seria extranjero.

They told us in our wedding that at least one of us would always be a foreigner.

El otro día pregunté a Daniel, nuestro hijo de cuatro años, "¿y porqué la gente se casa?". Me dijo sin dudar, "porque quieren". Lo he dado muchas vueltas y me he dado cuenta que Dan tenía razon. Nos casamos porque queremos, como de razones prácticas hay pocas.

The other day I asked Daniel, our four year old son, "so why do people get married?". He responded without a pause, "because they want to." I've thought about it a lot, and I've realized that Dan was right: we get married because we want to, since there aren't many practical reasons to do so.

Así que os presento el Señor y la Señora Lambert: dos personas, poco prácticas, que se casan porque quieren, y porque se quieren muchisimo.

So I present to you Mr. and Mrs. Lambert: two impractical people who got married because they wanted to, and because they love each other very much.

You may kiss the bride...

Mérida: Wedding

I'm in Merida, Spain, right now, at the house of Juan Carlos and Filo. We came here because I (get this) had to marry their daughter to a friend. The service went fine-- the bride and groom were Beau and Elena, the folks we evacuated from Katrina with. I'll post my comments from the wedding when I get to Barcelona.

Mérida is terrific-- the Spanish Ministry of Tourism is making a big mistake by not advertising the type of town like Mérida more. There are Roman ruins here from before the year 0.

Bikram Yoga: Two months

I've been practicing Bikram yoga since March 29, 2006. That's just over two months now. I've been going three times a week since then. I haven't written much lately, but things have been happening. For example, about three weeks ago my left knee popped when I got overly ambitious during the toe stand (Padangustasana). It's just now starting to feel pretty good again, and this morning I tried, cautiously, to get into the pose again. I didn't get all the way in, but nothing popped this time.

I've been doing maitenance for the studio's web page (http://www.bikramphiladelphia.com). That's been fun, and something I'm actually more or less good at.

I understand that yoga is slow-- it may take years to see changes. That's okay. I still see short term changes that I like-- I'm a lot more flexible, my balance is better, and I lost six pounds. That's all good.

35:57 Vine St/BF Parkway - The Roches, "Keep on Doing"

I haven't run much since I started doing yoga-- in fact, I think this is only the second time. This was a good run. It was pretty hot and humid out; however, I'm now one of those Bikram Yoga whackos so heat doesn't bother me much. No injuries, aches, pains, etc, either. Just a nice fast run.

I listened to The Roches, "Keep on Doing". I originally bought this album on vinyl when I was at the Naval Academy at Oceans II records (anyone remember that?). I pretty much got it because I had seen The Roches on Saturday Night Live singing the Hallelujah Chorus, and that was on this album. Now I pretty much skip that song.

The Roches are great. They do really tight harmonies, and on this album they were backed by Robert Fripp of King Crimson, which was just weird enough to work (I think one of them was dating Fripp). I really liked "Losing True" and "The Road to Fairfax County", but skipped over some of the sappier stuff. I guess I liked "Largest Elizabeth in the World" when I was in college, but it's stopped working for me. On the other hand, I never really got "The Scorpion Lament" when I was in school, but now I kind of understand it better.

I also listened to Will Farrell's version of the Starland Vocal Band's "Afternoon Delight". The funny thing is that the harmonies in this remind me of the harmonies of The Roches. I know it was a ready-made parody of itself, but I have to agree: "Afternoon Delight" is the finest composition in human history.

SQL Server: How to fix an orphaned user

I recently was sent a backup file to restore by a consultant. The file had logins I had already defined as users of the database. When I restored the backup, I was unable to sign in with a known user:

C:\>osql -S SERVER01 -d TestDB -U JSmith Password: Cannot open database requested in login 'TestDB'. Login fails.  

My login, JSmith, had become orphaned. This means that, although I had a login named JSmith, the internal representation of JSmith in my database did not match the internal representation in the backup.

There is a simple piece of code to fix this:

EXEC sp_change_users_login 'Auto_Fix', 'JSmith' 

If this works, you will get a message like this:

 The number of orphaned users fixed by updating users was 1.  

I did this and my login started to work again.

Loving another's character defects

I recently have been focussing a lot on my character defects-- taking something of an inventory of what's good about me and what could, in my eyes, be improved. This is not an easy process; some parts of my personality are not easy for me to look at, some I like even though they cause me trouble, and some I actually forget about until they get me into trouble.

When I consider the changeable and uncontrollable nature of my own personality, why do I have so much trouble accepting the character defects of other people? Often the people I love the most have predictable and well-defined characteristics that make me insane. If I cannot control the foibles of my character that I seem compelled to follow, why do I expect others to control theirs to suit my liking?

So I've been trying to practice seeing others' characteristics as part of them, and loving them for those characteristics.

With some people it is extremely easy: when my son starts to act like Veruca Salt, I can smile a little, see it as characteristic of his age, and try to give him some guidance through the clever use of parenting.

With others it is not so easy-- what about my friend who is always angry? Can I love him for that and not condition my love for him on my desire for him to change?

What about my President? The message I get from my spiritual teachings is that I should try to love him-- and I should also try to love Bin Laden. How do I love their character defects? And yet it is essential for my personal growth and well-being that I try. The fact is that the President has no idea what I think of him. But *I* know, and the feelings I get about him sometimes remove me from the sunlight of the spirit. And that hurts me-- my own inability to love the President's character defects hurts me.

I feel I'll be working on this for a while.

SQL Server: How to rename a database

I use Enterprise Manager for most operations in SQL Server. I feel really guilty about it, because I was strictly command-line with Oracle. Anyway, I recently needed to rename a SQL Server database. I fired up Enterprise Manager and did not find it as an option. Not F2, not rename, nothing.

Here are the commands to rename a SQL Server database:

EXEC sp_dboption 'old_name', 'Single User', 'TRUE'
EXEC sp_renamedb 'old_name', 'new_name'
EXEC sp_dboption 'new_name', 'Single User', 'FALSE'

Using HOSTS to block websites

I have to admit something: My name is Tim, and I'm a YouTube addict. Someone sent me a link to the Sony Bravia commercial (the one with with Jose González singing "Heartbeats") a few months ago and I haven't been able to get away from the site since. I'm powerless over it. My life has become unmanageable.

Which is all cool, the American way, really. Except when I started clicking into YouTube at work for a couple minutes, and had that turn into more than a couple minutes. This had to stop. I was going to have to call on a higher power.

I found it in the form of the HOSTS file. The HOSTS file is a simple way of bypassing the Domain Name Service (DNS) that converts Uniform Resource Locators (URLs) into IP addresses. Essentially, it acts like a telephone directory, remembering that "www.google.com" means "64.233.161.99" so you don't have to.

Because the DNS does such a great job, there isn't much call for the HOSTS file, unless you need to reach a site that only has an IP address (I used this hack recently as a workaround for some sloppy coding we got from a consultant), or if you want to redirect a troublesome URL to a safe one.

I bit the bullet and decided I was never going to look at YouTube at work again. So I opened c:/windows/system32/drivers/etc/HOSTS in notepad and put in the following entry, being careful not to erase anything that was already there:

# Sites I don't think I should look at at work
66.241.245.251 myspace.com
66.241.245.251 www.myspace.com
66.241.245.251 ebay.com
66.241.245.251 www.ebay.com
66.241.245.251 videosift.com
66.241.245.251 www.videosift.com
66.241.245.251 youtube.com
66.241.245.251 www.youtube.com

66.241.245.251 is the IP address of our company website (The Reinvestment Fund), but you could put whatever you want that is not problematic for you. I put in other potentially bothersome sites as well. Not that I look at eBay at work (really, boss, I don't). By the way, that big space there is a TAB. And the first line is a comment, because it starts with a pound sign.

The other thing I did to make my system a little more user friendly was to put a shortcut to "notepad c:\windows\system32\drivers\etc\HOSTS" on my desktop.

When I put a new entry into HOSTS, I may have to close my browser and run the command "ipconfig /flushdns" from the command line to make the change take effect.

"But Tim", you may say, "can't you just re-edit the HOSTS file and get back on youtube immediately?" Yep. But I haven't yet. Who knows why this works? Maybe it's the nature of this beast that I only indulge mindlessly, so when I have to become mindful to override the safefguard, I realize what I am doing and stop doing it.

Consejos para programadores

Tim Allen

He sido programador desde el momento que escribí mi primer programa de FORTRAN utilizando tarjetas de Holerith cuando tenía 15 años (imprimió la fecha de la boda de Carlos y Diana-- mi madre lo tiene en un cajon en algún sitio). Ha sido más o menos una adicción desde entonces. Pensaba aprender japones y trasladarme a Tokyo para escribir juegos electronicos por Nintendo. En cambió, aprendí castellano y me trasladé a Barcelona para escribir programas de bases de datos. Los sueños de la juventud...

En los más de 20 años que llevo programando, he aprendido un par de cosas. He hecho practicamente todos los errores posibles (y tal vez imposibles) que se pueden hacer. Y no sólamente como programador, sino como empleado, jefe, entrenador, estudiante... soy como una máquina de errores. Sin embargo, como dijo Socrates, "Quién sabe lo que no sabe es lo más sabio". Cada error me ha ensanyado mucho (a veces he disfrutado tanto de la lección que he vuelto a hacer el mismo error varios veces).

Bueno, basta de farfullar-- os voy a explicar unos cuantos de los errores que evitar, porqué evitarlos, y cómo evitarlos. Tal vez os sirve de algo:

---

Errores de Programación

Error: No tener plan ningúno:

Posiblemente lo más habitual. La mayoría de los programadores se sientan más cómodos programando que hablando con el cliente. Sabemos más de nuestros lenguajes y sistemas operativos que negocios. Por eso existen analistas.

Para sistemas pequeños, a veces tiene sentido que el plan sea "Empecemos programando ahora mismo y veamos que sacamos". Esto no es ningún pecado-- la mayoría de los programas que escribo para mi hijo y mi mujer empiezan así. Mientras tengo en mente que ése es el plan, no tiene nada de malo.

Pero con sistemas más grandes, a menudo el problema no se entiende bien la primera vez que se explica (ni la segunda, la tercera....) Si empezamos con el problema en mente en la forma original en que lo hemos entendido, el peligro es que vestigios de esa primera impresión equivocada se queden en el sistema hasta la versión 10.3 (vease Windows).

Error: Asumir que entendamos el problema cuando no sea cierto:

En base, este error se puede reducir a un problema de comunicación. El cliente habla en su dialecto ("Quieremos un sistema que gestione ventas de la línea 3542 (verano)"). Los analistas hablan en el suyo ("Quieren un sistema de Business Intelligence con Balanced Scorecard como sistema de DSS"). Y nosotros hablamos en el nuestro ("Quieren unas bases de datos Oracle con datacubes y los front-end en Java y PHP"). Añades el lenguaje de los vendedores ("Hace tiempo que no vendemos un sistema de Darwin-- este cliente es perfecto") y tenemos lo que se llama una maraña.

William James dijo algo al afecto que la manera más rápida de no aprender algo era asumir que ya lo entendiamos. Lo más sensato es llevar una sensación de incertidumbre sobre lo que hace el sistema en cada momento. No tengáis miedo de preguntar a los demás del equipo o incluso al cliente qué significa algo si no lo entendéis completamente. El problema aquí es que a nadie le gusta parecer tonto o que no hacía caso en algún momento. Pero la mera verdad es que casi nadie puede captar todas los matices de un problema en la primera vista. Por eso el analisis y la programación son procesos iterativos. A lo mejor lo preguntas al cliente qué significa la línea 3542 (verano) y no lo sabrá tampoco.

Error: Utilizar el prototipo como base del sistema final

A veces hace falta hacer alguna maqueta para entender el problema. Lo ensenyamos a los analistas, ellos hacen sugerencias, lo cambiamos, los analistas se quedan felices, lo demostramos al cliente, y después de unos cuantos cambios, él también queda convencido de la interfaz. Bien.

Ahora, ¿qué hacer con la maqueta? Lo más habitual es llevarlo a la oficina y empezar el primer borrador del sistema con él. Pecado mortal. Ya lo sé, porque lo he hecho vez y otra vez. Odio el concepto que he hecho un montón de trabajo en la maqueta y ya lo tengo que hacerlo otra vez. Pero hay buenas razones para reiniciar el desarollo:

• Ya que entendemos el problema, puede que la maqueta no sea escrita en el lenguaje más adecuado. Un buen ejemplo de esto es que una maqueta de un programa te puede costar un día de programar en Perl/Tk o Visual Basic, tener buen aspecto, funcionar de maravilla en la demostración, y fallar completamente cuando tenga que operar contra datos reales. Pero utilizando los mismos conceptos de la interfaz de la maqueta en un sistema final escrito en C++, el rendemiento se mejora. La misma maqueta escrita en C++ te hubiera costado dos semanas, así que hemos aprovechado de las fases de maquetar y de finalizar el sistema en la mejor manera.
• En el proceso de hacer la maqueta, a lo mejor hemos empezado por un sendero, lo hemos dejado, hemos empezado por otro, hemos decidido que el primero era mejor... al final hay muchos cabos sin atar. Lo puedes ver en sistemas escritos en Visual Basic-- casi siempre hay una ventana llamada "Form1" (el nombre por defecto de nuevas ventanas) que tiene dos o tres botones encima y no está conectada al programa de ninguna manera. Cuando tengo que arreglar uno de estos, triplico mis estimaciones del tiempo de cumplir.
• Empezar escribiendo el sistema otra vez nos exige entender bien el problema. En el proceso de maquetar, el enfoque está en hacerlo funcionar. Pero tal vez no hemos hecho mucho caso a todos los detalles del analisis, intentando cumplir los requerimientos más importantes. En la versión final, podemos aprovechar del hecho de que ya (esperamos) entendemos mejor el problema.

Error: Poner datos de prueba/mensajes de error tontos/obscenos/políticos

Ya lo sé, la tentación de hacerlo es tremendo-- tienes que inventar cien personas de prueba para comprobar que los datos se leen bien. Y ningun nombre te ocurre otro que Jordi Pujol o Jose María Aznar. O estás en la fase de depurar y hay un bug que no se va, así que (con un poco de frustración), escribes un gestor de errores con un mensaje como, "El maldito cliente ha hecho click en el maldito botón dos malditas veces, leche!".

Pues, os tengo un par de historias sobre el tema que contar. Primero: erase una vez, en un reino no tan distante, un programador de Visual Basic trabajando en el Ministro de Presupuestos del Presidente de Los Estados Unidos. La noche anterior de la demostración ante el cliente, el programador estaba trabajando hasta las tantas intentando arreglar un bug de lectura de datos. Lo habia localizado en un módulo global (fijados en la palabra "global"), y el arreglo era tan elegante, tan inteligente, que el programador, feliz en el hecho de que sus cuatro años de universidad le habia ensenyado algo, puso el mensaje de error, "Apuesto que el cliente nunca verá este error." Bastante inocente.

Y menos mál, porque el día siguiente, el programador, aun convencido de sus poderes ultra-programadoriales, hizo la demostración. Os recuerdo que el módulo de nuestro heroe era global, o sea, todo clase de función en el programa acudía a ese modulo para obtener datos. El programador arrancó el programa, tocó el botón de mostrar los primeros datos, y: Apuesto que el cliente nunca verá este error. Nuestro programador itenerante sonrió, explicó que era una pequeña broma, y siguió con su presentación. El único lugar en que no salió el error Apuesto que el cliente nunca verá este error era en la pantalla en que salió el error durante sus pruebas originales. Por semanas mis compañeros me decían, "Apuesto que el cliente nunca verá este error!". Desde entonces, 1) No trabajo hasta las tantas el día anterior a la demo, y 2) Mis mensajes de error siempre son:

Código de error: xxx
Un error ha ocurrido: ....

Otra historia, un poco más fuerte. En 1998, la compañia AutoDesk sacó al mercado una nueva versión de su producto AutoCad Mechanical. Cien mil copias se vendieron en los primeros meses. Cien mil copias, todos con un mensaje de ayuda que dijo:

Haga clic en esta pantalla para ver una explicación, idiota

Como os imagináis, AutoCad tuvo que reemplazar todas las copias del programa. El presidente de la compañía tuvo que enviar una carta disculpando AutoCad del hecho. ¿Y el programador? Se quedó en paro, y supongo que ya es un taxista (no digo que hay nada mal en ser taxista, pero a mi me gusta programar).

Ni siquiera pongo mensajes tontos en mis propios programas que escribo en casa. ¿Porqué? Por que el día de mañana me daré cuenta que el problema en que estoy trabajando tiene solución en ese programa que escribí para mi hijo Daniel hace dos semanas. Copiar, pegar, y funciona, sin que tenga que revisarlo a ver si hay algo que no quiero que el cliente vea.

Por cierto, ese consejo también se puede aplicar facilmente al mundo de los analistas. Alguna vez habeis visto un documento de analisis con una sección que dice:

Technical Architecture
El Tim aún no me ha dicho nada de eso...

Queda un poco mal. Mejor que otra persona mire tu documento antes de entregarlo. Lo cual nos lleva al próximo punto:

Error: Depurar tu propio programa

Cada persona usa el ordenador de forma distinta. Yo, por ejemplo, no suelo utilizar el ratón mucho (que dinosauro soy). Veo a otras personas utilizando un programa que he escrito y me soprende bastante que hacen cosas que a mi me parece completamente a reves. Así que, casi seguro que cuando yo arranco mi propio programa, no me va a fallar. Pero el usuario puede hacer acciones que a mi nunca me ocurriría. Por ejemplo, cuando hago un programa de Web, y sale un campo para poner un numero de teléfono, suelo poner el número de la oficina, o mi casa, o otro que sea un valor "legal". Pero tal vez mi usuario no tiene teléfono. Pone "No lo tengo" en el campo. Y si no he depurado bien, el programa falla.

No soy genio. Hay muy pocos, y suelen ser aun más despistados que yo. A Einstein le costó 2 años descubrir que habia hecho un error simple en su teoría de la curvatura del espacio-tiempo, lo cual no se resolvió hasta que su amigo David Hilbert miró sus ecuaciones y descubrió el error. Si lo pudiera pasar al mismisimo Einstein, seguro que me pasa a mi.

Napoleon tenía un caporal a quien le explicó todos sus planes. Si el caporal no lo entendió, sabía que el plan no iba a funcionar. En muchas empresas, hay una persona no técnica que sirve como "El caporal de Napoleon". Es la persona que hace pruebas del programa, y explica si el interfaz es imposible entender a una persona no técnica.

Los depuradores profesionales suelen ser gente no muy populares con los programadores.

Error: No documentar, o olvidar sacar comentarios "muertos"

Los programadores tenemos fama de ser gente a quien no le gusta hacer documentación. No soy una excepción. Hay un refran de los programadores: buen código se auto-documenta. Y un jamón. La verdad es que cuando miro mi propio código a una semana después de escribirlo, me pregunto lo que estaba intentando hacer. Aquí os pongo un ejemplo:

#include <stdio.h>
main() {
    double n,d,a;
    for (a=0,n=1.0,d=1.0;d<10000;a+=n/d,d+=2,n*=-1)
        printf("%.6f\n",a*4.0); }

Este es un programa completamente legal en ANSI C. Incluso funciona bien y rápido. Pero es muy difícil entender lo que hace y cómo. Si tuviera que arreglar el programa, tendría que encontrar el programador original (y no le hablaría con mucha calma).

Aquí está el mismo programa, formateado y con comentarios. Se ve facilmente cómo funciona, incluso por no programadores:

/* Programa: pi.c
 * Autór: Tim Allen 
 * Descripción: Implementa el algoritmo de la serie infinita de Arctangente
 * descubierto independientamente por James Gregory (1638-1675) y 
 * Gottfreid Wilhelm Leibniz (1646-1716).
 * La serie funciona así:
 *                 x^3   x^5   x^7
 * arctan(x) = x - --- + --- + --- + ...
 *                  3     5     7
 * 
 * y como arctan 1 = π / 4, sigue que π = 4 * arctan(1)
 * Referencia: http://www.escape.com/~paulg53/math/pi/greg
 */
/* Incluir la biblioteca estándar de entrada/salida */
#include <stdio.h>
/* definimos constantes */
#define MAX_DENOM 10000
#define EXITO 0
int main() {
    /* declaramos y inicializamos valores de la ecuación */
    double numerador = 1.0;
    double denominador = 1.0;
    double arctan = 0.0;
    /* un bucle para implementar la serie infinita */
    for (denominador=1.0; /* inicializamos el denominador a 1 */
         denominador < MAX_DENOM; /* bucleamos hasta que el denom > 10000 */
         denominador+=2) /* después de cada bucle, incrementamos el denom */
         {
        /* incrementamos el valor del arctan del próximo
         * valor de la serie */
        arctan += numerador / denominador;
        /* imprimimos el resultado multiplicado por 4 */
        printf("%.6f\n",arctan * 4.0);
        /* cambiamos el signo del próximo valor en la serie */
        numerador *= -1;
    }
    /* devolvemos el valor de exito al sistema operativo */
    return EXITO;
}

Os mentiría si dijera que siempre hago comentarios tan extensos para programas-- por ejemplo, un programador no suele necesitar comentarios para entender cómo funciona un bucle for. Incluso puede resultar molesto si hay demasiados comentarios. Pero mejor demasiado que demasiado poco.

Otro problema en poner comentarios en el código es olvidar borrar comentarios "muertos". Si cambiamos de idea medio rumbo, hay que acordarnos que los comentarios haciendo referencia a la idea original ya son invalidos.

Consejo: Separa la interfaz de la parte funcional

Es un error concentrate en la interfaz demasiado temprano. Es posible que pases todo el tiempo alocado arreglando dónde van los botones y el color de los iconos y pierdes algo importante en los requerimientos. Si se puede separar la interfaz completamente de la parte funcional del sistema, mejor. Esto te deja la posibilidad de desarrollar varios interfaces para el mismo programa sin duplicar trabajo.

La extensión avanzada de esta idea es el scenario Cliente-Servidor. Aquí, la parte funcional del programa se ubica en otra máquina que la interfaz. A menudo hay mejores en rendimiento importantes en sistemas construidos así. Incluso existe el concepto de la arquitectura "3-tier" en que una máquina se encarga de los datos, otra del programa, y otra de la interfaz.

Consejo: Trucos para la demostración

Hacer una demostración del producto es como pedir que falla algo. No es mi tarea preferida. Pero tarde o temprano el cliente querrá ver el progreso de la obra. Así que he compilado una lista de truquillos para suavizar el proceso un poco:

• Evita enseñar cada caracteristica del producto. Resulta monotono para el cliente. Lo mejor es hablar mucho y concentrate en como hemos cumplido los requerimientos del cliente.
• Escucha las preguntas del cliente atentamente. Si piden algo que sabes no existe o que no funciona, repitalo al cliente: "Le gustaría que haga...", apuntalo, y sigue con la presentación. Intenta no comprometer a añadirlo al producto, porqué es posible que piden algo que está afuera del alcanze del proyecto. En íngles, el fenómeno que resulta de este tipo de pedido es "Scope Creep" (que significa que el proyecto crece sin parar.)
• Si es posible, arranque dos copías del producto. Si falla la primera durante la presentación, ponte en frente de la pantalla un momento y charla sobre algun aspecto del programa mientras cambias a la otra copia del programa. En el 90% de los casos, el cliente no se entererá del fallo. Apuntalo mentalmente que hay que arreglar esa parte, y evita mostrarlo otra vez. ¿Honesto? Bueno, no mucho. ¿Funciona? Casi siempre.

---

Consejos profesionales

Consejo: Concentrate en la solución

Es facil caer en el error de pasar todo el rato quejandote de que mal ha sido el analisis, o que no hay un estación de trabajo para ti, o otra mil razones porque no se puede cumplir el proyecto. Sí son problemas, pero hay soluciones también-- o habrán, una vez que las has hecho.

Consejo: Nunca criticad al sistema que estáis reemplazando

Esto he hecho. Estaba modernizando un sistema de MVS-- uno de los viejos, con pantalla verde y todo. El cliente me lo enseñó, y mi primera reacción era decir, "Puaaa, que feo. No me estraña que quieres reemplazarlo." Lo has adivinado: el mismo cliente habia escrito el programa.

Consejo: Ojo con el email, chatear, bajar MP3...

Casi no hace falta comentar en este consejo. La tentación de utilizar la conexión de Internet del trabajo para finales personales es fuerte. Como norma general, nunca visito Hotmail en el trabajo. He deinstalado el sistema de chat en mi máquina. Y nunca he bajado un MP3 (tengo convicciones personales sobre robar obras de artistas-- pero eso para otro clase.)

Compañías que tienen mucha gente chateando en el trabajo suelen fallar. Soy una persona bastante tranquila, pero si veo a alguíen en mi cargo chateando, y si no es con la línea de ayuda de IBM o parecido, la primera vez lo documento, la segunda, despido.

También tengo cuidado en tener una cuenta personal de email ajena del trabajo. Por buenas razones: un día, cuando estaba trabajando en Oracle, me llegó un email de un desconocido. Era un email personal (muy personal) que habia enviado a una amiga. Aparentamente, el escritor quería enviar una copía a su dirección personal, lo cual tenía el alias "home". Desafortunadamente, Oracle también tenía una lista de email llamado "home" para nosotros que trabajabamos desde casa. La lista tenía más de 500 direcciones. El chico habia enviado su email a 500 empleados de Oracle, quejándose, entre otras cosas, de que él estaba bebiendo mucho últimamente y pensaba que tal vez era alcohólico, que su jefe era imbecil, que las chicas no le hacian caso.... Basta con decir que nunca, nunca utilizo mi cuenta profesional para mensajes personales, es demasiado peligroso.

Consejo: Vistedos mejor que el cliente

Esto he aprendido aquí: en Estados Unidos, es común que el CEO de la compañía llega a la oficina en tejanos. Pero las aparencías cuentan por mucho aquí. Es importante entender que el cliente nos ve como un servicio-- nos ha comprado. La impresión más destacada en su mente debe ser una de competencía, eficaz, etc. Si llegas a la oficina del cliente vestido en una manera no llamativa, te das la oportunidad de destacar tus talentos antes de tu sentido de estilo. Aunque el cliente viene al trabajo muy informal, mejor no hacerlo tú.

Aunque tenemos un día informal el viernes, es importante recordaros que siempre puede salir un imprevisto. Te pueden llamar a la oficina del cliente sin avisar. No llevo traje el viernes, pero tampoco vengo vestido en tejanos.

Consejo: No liguéis en el trabajo

No comment. Peligroso al máximo. Especialmente no ligues con el cliente. Lo he hecho; confia en mi, no es muy recomendable.

Consejo: Utilizad más las orejas que la boca

Especialmente al principio de un proyecto, es vital que el cliente nos ve como receptivo a sus ideas. La mejor manera de conseguir esto es escucharlo sin comentar mucho, pedir clarificaciones si hace falta, y después repetir lo que acabas de oir al cliente. Es preciso no formular conclusiones basados en la mitad de la información.

Consejo: En cursos, aprendad, no enseñéis

Lo habeis visto. En cada curso, hay algún listillo que quiere demostrar que sabe más que nadie (incluso el instructor). Preguntas como, "¿Y no ves el protócolo IPv6 como una extensión pre-anticuada del actual?" son habituales en este tipo de estudiante. Tenemos que acordarnos que en los cursos, estamos para aprender. Después del curso, nadie recordará que Tim sí sabía mucho de Unix. En cursos en que he tomado ese papel (sí, lo he hecho), no aprendí practicamente nada.

Consejo: Conoce al portero...

...y la secretaria y la señora de la limpieza.... Perece poca cosa, pero especialmente si eres el único de la compañía en la oficina del cliente, resulta agradable (y útil) tener contactos no conectados con el proyecto. Adamás, no te gustaría que la gente te tratara como si fueras un mueble de la oficina. A ellos tampoco.

Cómo aprender un nuevo lenguaje de programación

Aprender escribir en un lenguaje de programación es como aprender escribir en un lenguaje hablado. Cada persona lo hace de una manera diferente. Aprender uno nuevo, especialmente si es uno de los primeros que has aprendido, puede resultar dificil. Pero vale la pena: "si sólo tienes un martillo, todos los problemas parecen clavos". Tener elección en cual lenguaje utilizar es bueno. A mi me han funcionado bien estos consejos:

Consejo: Consigue un buen libro

Nadie puede aprender programar leyendo un libro. Pero ayuda mucho tener una referencia al lado cuando empiezas. Normalmente cojo algo simple ("Aprender C++ en 21 Días") que tiene mucho ejercicios.

Consejo: Haz amigos con un experto

A veces un concepto de 20 páginas en un libro se puede explicar en 5 minutos con la persona adecuada.

Consejo: Lee el código de otros

Así puedes ver la manera en que se usa el lenguaje habitualmente. En estos días de fuente abierto, es fácil conseguir copías de el fuente de programas.

Consejo: Usa el nuevo lenguaje por todo

Esto te hace crecer en conocimientos rapidamente. Aunque sabes que el mismo programa te costaría tan sólo tres minutos en Visual Basic, aprenderás mucho más intentandolo en el nuevo lenguaje. Puede que no sea el lenguaje más indicado para el problema, pero no importa. La idea es aprender.

MS Access: VBA Regular Expressions (RegEx)

As a Perl programmer (hey, I founded Barcelona Perl Mongers), I always am looking for Perlish features in other languages. I know VBA (Visual Basic for Applications) is not a beautiful language, but I run into so very often that I actually use it more than Perl now. And I've found that it has plenty of Perlish features. Two of the most useful for me have been Associative Arrays (i.e. hashes) in the form of the Dictionary object, and Regular Expressions. Recently a friend asked me how to extract ZIP codes from a field in a Microsoft Access table in a SELECT statement. For example, she wanted to say:

select some_function(ZIP_expression)
  from some_table;

on a table with values like "Philadelphia, PA 19107-1234" or "Lumberton, TX 77657" and have it return just the ZIP. I couldn't make an unwieldy version of this using just INSTR, MID, etc. Maybe there is a way. However, there is an elegant solution using regular expressions. You have to include a new Access module, and put this code in it. Prior to putting the code in, you have to include a reference to "Microsoft VBScript Regular Expressions 5.5" (Tools->References):

Function zipfinder(t As String)
    Dim re As New RegExp
    ' Look for five digits.  Optionally, look for a dash and four digits
    re.Pattern = "\b(\d{5}(-\d{4})?)\b"
    ' This version finds the first match.
    ' If you want the last match, set Global to false.
    re.Global = False
    Dim m
    For Each m In re.Execute(t)
        zipfinder = m.Value
    Next
End Function

Then you can use this as a function in a SELECT statement:

select zipfinder( ZIP_expression)
  from some_table;

Bikram Yoga: Drink water! Stop fidgeting!

I've learned two new things about Bikram yoga:

1) For a while now I've been drinking a liter of water on the way out the door before Bikram class. I forgot to do this on Sunday, and I was pretty bad off about three poses in. My balance was off, I couldn't catch my breath, and I was in a general pain fog for most of the class. Even drinking a lot of water during class didn't help. So drink a lot of water before class.

2) When I first got started doing Bikram, I made a mad dash for my towel and my water bottle between every pose. I would end up a little out of breath every time I did this. I've figured out that I do a lot better if I just don't move at all between poses. I've gotten used to being soaked with sweat and not wiping it off (it does no good to wipe off anyway, you're sweaty again in .3 seconds). And I've figured out that if I drink water before class, I don't need much during class. And I feel a lot better if I don't fidget, scratch, wipe, and drink between poses.

In fact, I've discovered that, for me, my mind makes up excuses to move around. I always want to shift my feet in the standing poses. But it turns out that whereever I shift to is never better than where I started.

A USNA Companymate killed himself

I got some bad news this morning. One of my companymates and a member of my class at the Naval Academy killed himself.

We lived a couple of doors apart-- never really got along too well. He always seemed angry at me when I was around. At 18 I didn't understand that maybe that was more a sign of a deeper suffering.

I talked with one of my roommates from the Academy this morning for the first time since graduation (20 years ago in May!). It was good to hear from him, and get emails back and forth to another classmate.

It really made me think about those four years at Annapolis. There were some amazing people there. I always felt a little inadequate in comparison. I talked with a friend of mine this morning who pointed out the insanity of this. It's important to appreciate the gifts we are given.

No one understands what our classmate did. I hope his family is... I can't imagine that his family is okay.

SQL Server: Error 9002, Log file is full

I got this error when I had backed up a database but neglected to truncate its log file and shrink the database. Normally you don't notice this because the default in SQL Server is to allow unlimited growth of data files. Here's the error message:

Name:  Database log file is full. Back up the transaction log for the database to free up some log space.
Description:  Error: 9002, Severity: 17, State: 6
The log file for database 'FOO' is full. Back up the transaction log for the database to free up some log space.

Here's the code to fix it:

use master

BACKUP LOG foo
with truncate_only

/*
 * The second parameter is the percentage 
 * of free space you want to leave for 
 * further growth.
 */
DBCC SHRINKDATABASE (foo, 10)

Bikram Yoga: You better breathe!

This is my third week doing Bikram Yoga at Bikram's Yoga College of India in Philadelphia (http://www.bikramphiladelphia.com/). I'm going three times a week now.

On Friday I went with Sonia for our weekly yoga date. For some reason I lost control of my breathing about a quarter of the way through class. I just started panting. Considering that this is a 90 minute class, that's pretty serious. I had a solid core of pain right in the center of my body for the last hour of class. I didn't really catch my breath until about an hour after class. It wasn't pretty.

When I went into class this morning I promised myself to concentrate on my breathing, even if it meant I'd do less on the poses. I really watched it, controlled it, modified the pose if necessary to be able to control my breathing. When I finished class, I felt great. Oddly enough, I got even deeper into the poses than last time. I guess it *is* all about breathing.

Becoming accustomed to the presence of peace

On Monday I was having a tough day. I couldn't put my finger on what the trouble was. I was talking with a friend when we hit on the fact that I had not prayed or meditated in three days.

It's always bugged me when I've heard people say that they skipped a day's prayer or meditation and had a bad day. It smacks of magic to me: that a prayer to the "cosmic concierge" affords me a good day, and if I don't pray, I am punished with a bad day.

But I think I'm beginning to understand it: when I pray and meditate on a regular basis, I accustom myself to the presence of peace.

This is important: people are not always right or easy to deal with. Sometimes people will come with aggression where none is necessary. Sometimes they will act out of despair. If I am not accustomed to the presence of peace, I have nowhere to go in that situation but towards more aggression. But if I let myself experience peace on a regular basis, I can meet aggression with peace. Aggression plus aggression escalates. Aggression met with peace: that's something we can work with.

One of my set prayers is the Prayer of St. Francis. It gives a formula for what is basically unfamiliar to me by nature: peace in my daily relations. The prayer asks that when there is hatred I may bring love, that where there is wrong I may bring the spirit of forgiveness. As a male, I have been taught that my basic tools in most cases are aggression and comic deflection. Learning something new here for me is essential. If someone near me makes an obvious error, becoming aggressive to that person hurts everyone. Forgiveness of an error is a path towards peace.

It seems easier to think about this in terms of people I already love: my wife, my coworkers. But it gets tougher with people I have not learned to love yet-- for example, when President Bush makes what I believe to be an error, if I aggressively condemn him, I actually cause less peace in the world. If I can somehow find the spirit of forgiveness for one of his errors, I channel more peace.

This is a difficult concept for me. Neither do I think I am supposed to blindly follow my president down a hate-based path of aggression towards all. I think I'll have to think about this aspect of my spirituality some more.

Bikram Yoga: Week one

As I mentioned last week, I found a couple of Yoga places here in Center City, Philadelphia (Yoga Classes in Philadelphia). I had done one Bikram Yoga class in New Orleans before we evacuated, and I had been happy with it. The schedule was right. And I'm a little bit of a masochist (really-- I have the T-shirt and everything.)

So I signed up for Bikram Yoga at Bikram Yoga of Philadelphia. I've done four classes in the last week, including a date with Sònia last Friday. She's gotten into it too.

Bikram is hard. I've decided that Bikram is the fisting of Yoga. On Wednesday I did a class after getting only four hours of sleep. My balance was all messed up. I was trying to do this head to knee pose where the goal is to touch your forehead to your toes-- I thought, yeah, right. But I really pushed it, and I actually felt my hair touch my toes! Right then, it was like someone turned on a big switch-- for a moment, I was completely awake. I had complete consciousness. I heard the theme music to "Bonanza". It was cool.

Then I went out of the pose and it was gone. Guess I'll have to go back next week.

SQL: Calculating the geometric mean (GEOMEAN)

I had a user at the last place I worked who needed to calculate the geometric mean of a column in a table. The geometric mean is like a regular mean, but has the practical effect of throwing out outliers (unusual spikes in the data). This is a standard function in Excel, but not in most databases.

The standard method of calculating the geometric mean is by multiplying all of the terms together, then taking the n-th root of the product, where n is the number of terms. So if you had 5, 7, 55, 6, and 3 as terms, you would take the fifth root of 34,650, giving you 8.0899. Notice how the final result is not very much affected by the outlier (55).

This technique won't work in most databases for non-trivial datasets. Why? Because if you take 100 terms and multiply them, it will likely overflow even the largest column type. In fact, calculating the geometric mean this way with a calculator can quickly become impossible for large data sets.

There is a solution. If you take advantage of the magic of logarithms, you can calculate the geometric mean for very large data sets without overloading a standard relational database data type.

For a given set of terms, sum the log (I will use the natural log) of all terms. Then divide this sum by the number of terms. Finally, take the anti-log of the result (since I use a natural log, this means raising e to the result). Using our example:

ln( 5) =  1.6094
ln( 7) =  1.9459
ln(55) =  4.0073
ln( 6) =  1.7918
ln( 3) =  1.0986
         -------
         10.4531

10.4531/5 = 2.0906

e^2.0906 = 8.0899

which is what we got using the standard technique. This can be translated into SQL this way:

select exp(sum(log(sample)) / count(*))
  from sample_table

-- or, depending on your flavor of SQL...
select exp(sum(ln(sample)) / count(*))
  from sample_table

UPDATE: Isabelle pointed out that there is a neater way to do this. Since the sum(FOO)/count(*) is an average, the expression can be simplified to this:

SQL Server

select exp(avg(log(sample)))
  from sample_table

Oracle, PostGreSQL, MySQL

select exp(avg(ln(sample)))
  from sample_table

Nicely figured out, Isabelle.

As far as I can tell, this will work in Oracle, SQL Server, and Access, and I can't think of why it would not work in MySQL or PosgreSQL.

MS Access: FIX: Left function doesn't work

I have an old Access database. I found that a lot of scalar functions didn't work in it. I tried something simple like this:

select left(l_name, 15)
  from test_table

and got this error:

Undefined function 'left' in expression.

I hit alt-F11 to bring up the Visual Basic for Applications (VBA) IDE, and immediately got this error message:

Your Microsoft Office Access dataabse or project contains a missing or broken reference to the file 'MSOWC.DLL' version 1.0

I check my references (tools->references) and saw an entry: "MISSING: Microsoft Office Web Components" which referenced the file "C:/Program Files/Microsoft Office/OFFICE10/MSOWC.DLL"

I went looking for the file-- the first thing I found out was that, althought I have an OFFICE10 directory, I'm running Office 2003 out of OFFICE11. There was no corresponding DLL in the OFFICE11 directory.

I simply unclicked the MISSING reference, saved the file, and it all works fine now. I suspect that the missing file is unneeded in 2003.

Yoga classes in Philadelphia

Since the search for lunchtime kung fu/tai chi classes didn't work out (see No Lunchtime Kung Fu in Center City), I'm looking for yoga classes now. You say flaky, I say eclectic. Anyway, I've turned up two likely studios.

The first one is Bikram-- I went to one Bikram class in New Orleans before we evacuated, and it was cool. Or rather, not, it was done in a 100 degree room. It truly kicked my ass, but in a way that I liked.

Bikram Yoga of Philadelphia
http://www.bikramphiladelphia.com/schedule.shtml
M-F - 06:15 - 7:45, 12:00 - 13:30, 19:30 - 21:00
$20 first week,
10 classes - $120, month unlimited $150

The second one does Ashtanga yoga, which I don't know anything about. However, I like that they do a 12:00 - 12:45 class. That's realistic. One thing I picked up in my Kung Fu search was that studios sometimes take the attitude that if you can't bend your life around the class, you aren't dedicated enough. I guess I'm not very dedicated, but I *would* like to get in some form of exercise.

The Yoga Loft
http://www.theyogaloftphilly.com/schedule.html
M-F 07:00-08:00
M,W,F 12-12:45

By the way, the info I have here is only for classes that worked for my schedule. Go to the websites listed to see the entire schedule.

39:45 South Philadelphia Loop - Chelsea Williams

It's been a while since my last run, and o lord could I feel it. I was about to go microwave some couscous when a tiny voice in my mind reminded me that I am 41 and will quickly become a tremendous lard-ass if I just eat and never exercise.

I found Chelsea Williams through a friend of a friend. She's a busker in Los Angeles. Man, busking must be tough in Los Angeles if this is the level of quality. I busked quite a lot in Barcelona, and I always thought I was pretty good, but I suck by comparison.

Unfortunately, I wasn't able to download more than two of Chelsea Williams's songs, so I filled out the rest with standbys. Here was my playlist:

1. Undecided - Chelsea Williams


2. This Will Be - Chelsea Williams


3. Te Recuerdo Amanda (I remember you Amanda) - Jose Mercé


4. La autoradio canta (The car radio sings) - Miguel Bosé


5. Dime quién soy yo (Tell me who I am) - Niña Pastori


6. I'm Digging Your Scene - Dr. Robert


7. Springtime for the World - Dr. Robert


8. Telephone - Zap Mama

I got into some flamenco today (Jose Mercé & Niña Pastori). And the Miguel Bosé song was a favorite from 1996.

The Dr. Robert songs are from a new acoustic album coming out from the former singer of "The Blow Monkeys".

The run itself was very hard-- I got really tired about halfway through. Not out of breath, oddly enough, just generally worn out. My god I'm an old fart.

Microsoft Paint (PBRUSH) Text Toolbar Disappears

I use Microsoft Paint (pbrush) a lot. I recently had the problem that my Text Toolbar just disappeared. I tried selecting and unselecting "Text Toolbar" from the view menu, but nothing worked.

You can fix this from Regedit. The keys are:

My Computer/HKEY_CURRENT_USER/Software/Microsoft/Windows/CurrentVersion/Applets/Paint/Text/PositionX

and PositionY

From Regedit you can set both of these to 0 and the text toolbar will show up on screen again.

HP-32SII :: Hewlett-Packard RPN Scientific Calculator

What this page is about

I set up this page to see if I could find any other HP32/HP32S/HP32SII
enthusiasts (that is, geeks). I had a great HP-15C when I was
in University, but alas, I could not find another when it came
time to buy a new calculator (I sold the 15C like an idiot).
The HP32SII seemed to fit my bill. It was RPN, programmable,
and had a lot of the same features of the HP15C.

Sample Program
Euclid's Algorithm for the Greatest Common Divisor

  ; This is an HP-32S version of Euclid's Algorithm to find the greatest common divisor.
  ; You run this by putting the two numbers for which you want to find the GCD and pressing "XEQ E"
  ; I'm modifying this to put the non-shifted
  ; key in brackets next to the shifted key
  ; to make it less confusing. (Thanks, Stefan)
  ; Where I say oShift, I mean hit the orange shift key first
  ; Where I say bShift, hit the blue shift key first
  E01 LBL E     ;[oShift +]
  E02 STO A
  F01 LBL F ;[oShift +]
  ; Divide y register by x register
  F02 ÷
  ; Get the Fractional part
  F03 FP ;[bShift square root]
  F04 RCL A
  ; Multiply the fractional part by the first number you put in
  F05 x
  F06 1
  F07 x>y? ;[oShift ÷] then select >
  F08 GTO G ;[oShift XEQ]
  F09 R(DOWN)
  F10 PSE ;[bShift R/S]
  ; Exchange the x-register with STOrage A
  F11 x <-> A ;[bShift x<->y] (next to ENTER)
  F12 RCL A
  F13 GTO F ;[oShift XEQ]
  G01 LBL G
  G02 RCL A
  G03 RTN

Links

• Wikipedia HP32SII Entry

SQL Server: Understanding SYSFILES/SYSALTFILES status

SYSFILES and SYSALTFILES describe the data files used by SQL Server databases. They have the same structure, except that SYSFILES refers to data files in the current database, and SYSALTFILES resides in the MASTER database and refers to all data files in the database.

The STATUS column of SYSALTFILES is made up of a series of flags to tell you properties of a data file. The following query checks for the flags. A zero indicates the flag is not set, a non-zero indicates that the flag is set:

select name, 
       status,
       status & 0x1 [Default device (unused in SQL Server 2000)],
       status & 0x2 [Disk file],
       status & 0x40 [Log device],
       status & 0x80 [File has been written to since last backup],
       status & 0x4000 [Device created implicitly by CREATE DATABASE],
       status & 0x8000 [Device created during database creation],
       status & 0x100000 [Growth is in percentage, not pages]
  from master..sysaltfiles

There is more information on this table in Inside SQL Server 2000 by Kalen Delaney from Microsoft Press.

SQL Server: Data file monitor stored procedure

This is a script that lets you monitor datafile sizes in SQL Server. It collects the sizes of all data files in the database and stores them in a history table. You can set this up to run with a SQL Server Agent Job to collect data file sizes nightly.

The table the history gets stored in looks like this:

create table altfiles_history (
date datetime,
dbid smallint,
fileid smallint,
size int
)

The stored procedure code looks like this:

/*
 * size_monitor
 * Timothy Chen Allen
 * http://www.timallen.org/blog
 */
create procedure dbo.size_monitor 
as
set nocount on
	
-- Cursor: names and sizes of data files
declare c_dbsize cursor for
 select dbid, fileid, size
   from master..sysaltfiles

-- Declare variables into which to fetch
declare @dbid smallint
declare @fileid smallint
declare @size int
  
open c_dbsize

fetch next from c_dbsize
 into @dbid, @fileid, @size
 
while @@fetch_status = 0
begin
    fetch next from c_dbsize
     into @dbid, @fileid, @size
    
    insert into trf_dba_utilities..altfiles_history (date, dbid, fileid, size)
    values (getdate(), @dbid, @fileid, @size)
end

return

You can query the history table to see how your data files are growing with this query:

/*
 * 1) size and maxsize are displayed in 8K blocks.  You can
 *    get this to MBs by dividing by 128.
 * 2) maxsize is -1 when the datafile is allowed unlimited
 *    growth.
 */
select s.date,
       d.name database_name, 
       f.name datafile_name,
       s.size/128.0 datafile_size, 
       case when f.maxsize = -1 
            then -1
            else f.maxsize/128.0
       end max_datafile_size
  from altfiles_history s
  left join master.dbo.sysaltfiles f on s.dbid = f.dbid and s.fileid = f.fileid
  left join master.dbo.sysdatabases d on s.dbid = d.dbid
 order by d.name, f.name, s.date

Basically, I'm just storing the size column and keys from SYSALTFILES. This history is a nice thing to have so you can watch file growth and see if you need to be automatically shrinking databases, or to watch for unexpected jumps in size.

The Katrina Tapes

I thought I had just about written my last Katrina category blog. Guess I was wrong.

I just watched the tapes of the President's briefing four days prior to Katrina hitting New Orleans: http://video.ap.org/v/en-ap/v.htm?f=CADIU . I dunno. Go watch them yourself.

The tape of the White House Press Secretary shows Scott McClellan attempting to spin this somehow to say that the President was blameless. I'm tired of that. Even I was not blameless in this-- there were things I could have done to better ensure my family's safety. I could have done a lot of things better.

That's why it makes me angry to see the most powerful man in the world dance around like a fly girl trying to convince us that he never did a damn thing wrong. Everyone knows he screwed up-- it just makes him look like a liar on top of it for him to claim that he didn't. The kind of excuse making I'm hearing out of the White House in that press conference had a name when I was at the Academy: Sea Lawyering. It's not owning up to one's responsibilities because of some loophole. It's trying to pin a company-level screwup on some PFC in the third rank. It's not being President-- it's just cowardice.

No lunchtime Kung Fu in Philadelphia

It's been nasty cold here in Philadelphia, and I've taken a hiatus from running as a result.

I've been looking for a place to learn Kung Fu for the last three weeks. Some advice for Martial Arts instructors in Philadelphia: if you want to get rich, set up a Martial Arts class in Center City, Philadelphia, at lunch hour. There are no Kung Fu, no Aikido, no Karate, and no Tae Kwon Do classes within walking distance of Chinatown in Philadelphia at lunch time. There is one place that teaches Brazilian Jiu Jitsu (http://www.maxercise.com) but have you seen those guys fight? They start by pulling each others' ears off! I still might consider that place, though.

We're also looking for a T'ai Chi Ch'uan (Taijiquan) here in Southern Philadelphia for both Sonia and me, but again, it seems there is a dearth of Tai Chi schools here. Which seems odd, because I thought people were getting more into this sort of thing, but I guess they really are just interested in Yaga and Pilates these days.

SQL Server: How to create a user login and give it access

This is just a script that I put together to create a user and assign it database access and roles. The login actually existed previously with the wrong roles, so the script starts out by deleting the login from both the database and the server. Here are the commands I entered and the server's responses:

-- Start up OSQL from the command line
c:>osql -S SERVER_NAME -d DATABASE_NAME -E

-- Set the database to the one we want to
-- grant access to
1> use DATABASE_NAME
2> go

-- Drop the login's database access
1> sp_revokedbaccess @name_in_db='USER_NAME'
2> go
User has been dropped from current database.

-- Drop the SQL Server login
1> sp_droplogin @loginame='USER_NAME'
2> go
Login dropped.

-- Add the SQL Server login with the
-- correct password and default database
1> sp_addlogin @loginame='USER_NAME', @passwd='PASSWORD', @defdb='DEFAULT_DATABASE'
2> go
New login created.

-- Grant database access to the login
1> sp_grantdbaccess @loginame='USER_NAME'
2> go
Granted database access to 'USER_NAME'.

-- Grant the login the "db_owner" role
-- to this database
1> sp_addrolemember @rolename='db_owner', @membername='USER_NAME'
2> go
'USER_NAME' added to role 'db_owner'.

This was for a login with "Standard" security-- that is, not a domain user name.

21:05 Southern Philadelphia

I rolled out of bed at 6AM for this one-- I've figured out that to run and meditate before work, I have to get up pretty early. It was nice to run down South Street at 6AM. No traffic, just a lot of different sounds: trucks loading, wind chimes in front of "Garland of Letters", a homeless guy mumbling. Knees didn't hurt, probably because I could run on the actual road.

45:00 Southern Philadelphia with Daniel

We have house guests (Angeles and Jerry-- the third series of house guests since Katrina!), and Dan and I had been horsing around playing Power Rangers all morning while the Spaniards sat and chatted. We got bored, so I whispered, "psst... let's get out of here and go running". Dan whispered back "okay!".

We got bundled up, grabbed the baby jogger, and took off. I meant to do about 30 minutes, but Dan nodded off about 10 minutes into the run, and I wanted him to get a little rest, so we kept going.

Down South Street to the end where it meets up with Front Street an back, then looping around a bit. The only drag was that I was running on the sidewalk the whole time to keep Dan out of traffic, and my knees hurt like tarnation after that.