At work, we are using MS SQL Server 2000. Works great. At home though, I have more of a developer's environment. I am running CF Developer's edition and MS SQL Server 2005 single user edition (or something, can't quite remember what it is).

I don't get a lot of time to work on stuff at home, but one night, I had some time to kill, so I brought a personal site from work to home. I copied the files over, created the datasource, started her up and she opened file... until I started to click around. Once I went to any DB driven page I got a SQL error that "optional components had not been installed." Not being a SQL master, I had no idea what this meant, nor did I have any idea how to debug it. So, I just started taking out parts of the query until things started running again.

Apparently, what was causing the error was my use of the CF_SQL_DATE sql type in my query params. Not having internet at home (crazy I know), all I could do was look up the CF help files. Well, in the CF query param help files, apparently CF_SQL_DATE doesn't actually translate very well to MS SQL server, if I understood the documentation correctly. It seems that CF_SQL_TIMESTAMP is the one type that translates over to DATETIME in MS SQL server.

So I went back into the query, replaced the DATE with the TIMESTAMP, ran the page again and presto-chango, works like a charm. I then did this for all the pages on the site and everything is running great.

The only caveat with TIMESTAMP is that is uses the TIME part of the date, where as CF_SQL_DATE, I believe, did not. So now, I just have to be sure to use date's that only have date portions before using them in the database.