I always strive for portability just to make life easier and then use vendor specific code when it makes sense. For example, 90% of SQL will work between SQL Server/Oracle/MySQL in a typical app, but if it's something data intensive and needs some database specific functions, there's no reason to make something slow just to avoid using db specific functions. After all there's usually a reason to use a specific db for a purpose.

I just always try to keep it clean and then optimize for a db where it makes sense. As other posters have mentioned, db code should be in it's own assembly/library so that it can be loaded for the appropriate db.

We've been using iBatis and found it invaluable for this. All the SQL calls are in xml files (don't be scared until you've seen it) and the data access framework converts between that and your Java/.NET/Ruby objects. It's really nice because a dba can see and tweak all the sql calls by just looking at the xml files. Plus, you can use any sql you want including procs and vendor specific sql. And, swapping between SQL Server, Oracle, MySQL whatever just means you write a new set of xml files for that rdbms. It's pretty slick and used in some pretty high load environments.

As far as just app code goes, we've been able to write our Java code pretty cleanly and it runs just fine on the Windows or UNIX servers here without even a recompile and it's pretty fast on the latest JVMs, so I don't think write once run anywhere always means such everywhere :^) although I agree it can mean that. Of course, we keep it nice and simple and don't use J2EE.