John Backus, inventor of FORTRAN, dead at 82

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

New Orleans Trip

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

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

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

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

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

SQL Server: How to reset an IDENTITY column

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

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

DBCC CHECKIDENT('MyTable')

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

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

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

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

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

Some things to remember when configuring the connection:

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

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

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

SQL Server: How to write to the event log

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

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

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

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

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

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

 eventvwr.msc /computer=OTHER_NAME 

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

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

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

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

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

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

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

 DBCC TRACEON (3226, -1) 

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

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