This annoying error that occurs when using JDBC and MS SQL has been around for a while. The JDBC system in Coldfusion caches information about the tables you access. If you change a table (add a column for example) the column ids get out of whack and they no longer match the correct column. For Example, the first time you run a query you might get back the following:
SQL Sees | CF sees |
---|---|
username | username |
userid | userid |
fname | fname |
lname | lname |
bestFriend | bestFriend |
spouse | spouse |
SQL Sees | CF sees |
---|---|
username | username |
userid | userid |
fname | fname |
lname | lname |
bestFriend | bestFriend |
bestFriendsGirl | spouse |
spouse | ? |
If fact, what often happens is that the offset results in a "data type mismatch". The specific error is "Invalid data type for column A" or something similar. For example, if the username is now in the place of the userid column and you try to insert a username into userid (which we assume is an int) you will end up with an error resulting from attempting to insert an int into a character column. Incidentally this error is much preferable than the alternative of matching data types incorrectly. When the offset exists but the data types are the same you end up in the unenviable position of inserting data from column A into column B. This could result in Bob Johnson from Pokipsy being inserted as Johnson Pokipsy from Bob. That's a much more difficult error to find and repair - and it could even go undetected for a while.
The rule of thumb is to be careful when adding columns to tables in your database - especially on production! Make sure you have a plan to mitigate this error. Possible solutions often suggested are: