When is SQL Server not SQL Server?

When it's MySQL, of course.

(note: programming-heavy content. Normal sports, gaming, and random content resumes tomorrow.)

You see, it goes like this. We're using ColdFusion 8 and want to use Windows Authentication to login to SQL Server databases (2005, for the most part). Of course that's not directly supported, so we have to set up the data sources as Other and use Microsoft's JDBC driver to connect.

Now for the most part, that's not that big a deal (once you remember that you have to grant access to that user to the database itself - oops on my part). But when a sequence is involved ... (tangent: SQL Server is absolutely stupid when it comes to sequences. I have no idea why they don't separate them from tables like Oracle does)

So when I add a record to a table, either I have to use janky code that runs multiple queries in a single cfquery tag, or I can take advantage of the new feature in CF 8 that returns the identity (SQL Server's name for sequence) value that was just used. Duh.

Of course Adobe "helpfully" set it up so that each database returns a different key-value pair for the same purpose, rather than using a single key for any database. That would have been entirely logical and reasonable. And of course Microsoft's driver prevents CF from realizing that it's connected to a SQL Server database for these purposes, so as a result, it returns the key for a MySQL database (GENERATED_KEYS) instead of the one for a SQL Server database (IDENTITYCOL).

not. helpful.

+1 to me. -1 to Adobe. times 2.