SQL Server 2008 and ColdFusion 9: no problems, right?

Sure, once it's set up. lol.

ColdFusion and SQL Server talk to each other very well, except when they don't. In a development environment, like, say, your laptop, they don't talk at all by design: CF wants to connect via TCP/IP and SQL Server ships with TCP/IP disabled by default.

So, thanks to Adobe help, Ben Forta's post, and Marc Esher's post, I got it working ...

• Follow the instructions in Esher's post above to open TCP/IP services in your SQL Server instance (enable TCP/IP, click on Properties, IP Addresses, delete all Dynamic Port values, and enter 1433 as the TCP Port, assuming you do have it running on 1433).
• Follow the instructions in Forta's post as well (enable Windows and SQL Server authentication, create a SQL Server account for ColdFusion[1], and give it access to the database in question).
• In the CF Administrator, enter the username and password for the SQL Server account you just created[2], leave the server name blank, and enter the name of the database to which you want to connect.

That's everything, I think, although I probably skipped over some of the painful details. Anyway, it's more complicated than it should be, and it's not all on Microsoft: there are plenty of JDBC connectors that don't have this issue. Adobe just hasn't improved their built-in connector.

---

[1] Unless, of course, you already have ColdFusion running under a separate user account, which is good, in which case you simply let CF log in using Windows Authentication. (return)

[2] Username and password are required only for SQL Server authentication; for Windows authentication, neither are required (because the user is already logged in). (return)

Poor form design

Even Google gets things wrong sometimes. (I don't mean like Buzz. That's more like "Even Google completely nukes a section of the internets and then attempts to apologize later, as if words could somehow make up for the incredibly stupid decisions they made.")

Have you nominated your area to be a test market for Google's reported 1 Bbps fiber service? You should. I did, and when I submitted the form, Google politely told me that while they'd saved my information on the page (good), there was something I had to correct ...

One of the boxes could contain no more than 384 characters.

Why 384? Who knows. (Typically it's 255; you may get smaller numbers if they do something to the text going in or coming out, and I suppose this could be a natural-language field, but I digress.) The point is that they showed me a restriction after I submitted the form ... and not only that, they didn't even tell me how much I had to change.

This is very bad practice. First, you should do this checking prior to form submission so that you don't make the user waste a step. We don't like trying things that don't work. (Google is notorious for low-load pages ... but then they have AJAX to bring up search tips and such, so why would they not use JavaScript here?) Second, if there is a problem, tell the user exactly what it is, and if possible, make recommendations to fix it. Tell me how many characters I've typed. Don't make me copy the text into Word just to count it. Better yet, add code to the text box to show me exactly how much room I have.

Ah, Google. Just like the rest of us, aren't you?

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.

A helpful tip for saving a date and time in Access using ColdFusion

two steps ...

1. Use odbccreatedatetime() so that you have an ODBC object with both date and time, otherwise all Access gets is a date.
2. Use cf_sql_timestamp (you are using cfqueryparam, right?), so that the ODBC driver understands it's getting a date-time parameter.

Ta da! Date and time in your Access database.