SQL Server 2000 notes

A place for stuff that I don't want to forget. (There's stuff I only do with it once a year, and I'm perpetually looking this stuff back up.)

1) bcp.exe will let you dump a table at a time to a CSV file. DTS absolutely sucks for the same task, as it's a stinkin' wizard, that doesn't remember what you did last time, etc., etc., blah blah freakin' blah. You can at least script bcp with a .bat file, and have a record of what you did, for next time.

SQL Server Timestamp Field

It's been so long since I've had a decent Microsoft rant, I guess it must just be time. A while back, in a SQL Server database (don't laugh, the client insisted), I needed to have a timestamp for when a row is created. Silly me, I actually looked through the data type list, saw "timestamp", and thought, "Hey, that must be a timestamp!". Yeah, silly me.

The timestamp type? It isn't even a usable type. It gets set whenever the row gets updated, is binary data, and has nothing to do with dates or times. What I really wanted was a Datetime field with a default value of getdate(). Not a timestamp.

I'm so glad that the REST of my coding life, I get to be in Postgres. In Ubuntu. On Linux. Away from Microsoft. Harumph!

SQL Server Default Values (or "Why Open Source Is (Still) Better")

In getting our Java app to run on Windows, I'm forced to use SQL Server as a database. (Against my will, I might add.) I have an external application that will be adding rows to a table via ODBC, and I'm thinking it'd be nice to have SQL Server automatically generate a date value for when the row was written. In PostgreSQL, it'd be a default value in the DDL (I think; I've never needed it yet), but I wasn't sure what it'd be in SQL Server, so I tried timestamp.

That didn't look right to me, as the value in Enterprise Mangler displayed as '<binary>', rather than the actual date. Okay, let's try datetime with a default value. Well, that complained about something odd. I googled and found that timestamp *is* what I want, display problems notwithstanding.

Which leads me to today's punchline: when I switched from datetime w/default to timestamp, and tried to save my changes, EM complained about a default value not being appropriate for that datatype. Why, on earth, did EM not clear the default value when I switched datatypes?!

Okay, maybe I'm reading too much into this, but this is a problem you just don't see in open source programs. In the O/S world, the first decent coder to hit that problem would have pulled down the source, fixed the problem, and submitted a patch. The next version of the program would be fixed, perhaps in less than a month, depending on the release cycle. Heck, I was itching to do it, myself, except, whaddya know, I can't get at the code.

But I'll wager that this problem will never get fixed. It's not "important enough" (i.e. to the marketing team) to make it into a bug list, unless it annoys a SQL Server developer enough to make it happen "while he's doing other things" (i.e. without permission).

Harumph. When will I be free of this nonsense?