Problems with CF 8's Generated Keys Feature

So you may have read about CF 8's new ability to return generated keys. For SQL Server 2000 and greater this means that the driver attempts append the SELECT SCOPE_IDENTITY() statement to end of insert statements. If you want to get an idea of how this is done you can take a look at the source for the jTDS JDBC driver. While the jTDS driver isn't what ships with ColdFusion, you can bet that CF's SQL Server driver from DataDirect is doing something similar. In fact you can actually confirm this using SQL Profiler.

[More]

Comments
Aaron Longnion's Gravatar Not sure if this will help, and I didn't test thoroughly yet, but I noticed with SQL Server that if you use SET NOCOUNT ON above your INSERT, then the IdentityCol is not returned in the results struct. Let me know if this helps ;)
# Posted By Aaron Longnion | 1/30/08 9:23 AM
Dan G. Switzer, II's Gravatar @Nathan:

Thanks for this information. This is one of those issues I think is pretty important to bring to people's attention, since it's happening and most people have no idea it's happening.

Anyway, I blogged about it here:
http://blog.pengoworks.com/index.cfm/2008/1/30/Two...
# Posted By Dan G. Switzer, II | 1/30/08 9:45 AM
Nathan Mische's Gravatar @Aaron - I haven't tested this, but adding SET NOCOUNT ON probably triggers the "Auto-generated keys" error, which CF 8 silently ignores. The application I'm working with has 8,000+ queries so making this type of modification really isn't feasible.

@Dan - I think this is very important because you are basically changing the load on your database without changing any code. Most people probably don't even realize this is happening and, depending on your application, this change could have serious consequences.
# Posted By Nathan Mische | 1/30/08 10:39 AM
steve petty's Gravatar got your name from mike petty. im his uncle and am looking for a web maintenance and update and addtions to an existing site. are you interested? please advise me your terms. thanks. steve petty
# Posted By steve petty | 2/26/08 7:16 PM
Kevin Penny's Gravatar @Nathan - I wasn't clear on your 'SET NOCOUNT ON' comment, but I was logging output through the dsn setup in cfadmin 'Log database calls to ' a log file - and saw the generated keys error on a number of queries on our system (we stress starting every with with SET NOCOUNT ON) and for those queries that were throwing silent errors, when I did Add the SET NOCOUNT ON for a query that was INserting into a table - the error DID go away which I think contradicts your statement of "adding set nocount ON probably triggers the 'auto-generated keys' error" - when in fact it removes the error.
# Posted By Kevin Penny | 5/21/09 12:12 PM
Nathan Mische's Gravatar @Kevin - That comment should have read "...adding SET NOCOUNT ON probably _still_ triggers the "Auto-generated keys" error...". Anyway, it is good to know that SET NOCOUNT ON fixes the issue. It is also worth noting that Adobe released a fix for this, so you don't need to go in and change all of your queries: http://kb2.adobe.com/cps/403/kb403432.html
# Posted By Nathan Mische | 5/21/09 12:55 PM
Kevin Penny's Gravatar Well, in my initial testing, on 8.0.1 which is supposed to have this patch already - PLUS using the jvm config arguments, there are still many queries that throw this silent error as documented here:

http://kb2.adobe.com/cps/405/kb405729.html

Their response: basically rewrite the query as separate queries -
# Posted By Kevin Penny | 5/21/09 2:23 PM
Nathan Mische's Gravatar @Kevin - I hadn't seen that tech note so thanks for sharing.
# Posted By Nathan Mische | 5/21/09 4:07 PM
Nathan Mische's Gravatar @Kevin -Quick followup. Are you able to disable auto-generated keys using the -Dcoldfusion.noautogenkeydsns java arg? I can't. I've tired on CF 8,0,1,195765 with and without the hotfix in TechNote kb403432.
# Posted By Nathan Mische | 5/21/09 4:55 PM
Kevin Penny's Gravatar @Nathan -
Nope - same for me - those args do nothing different for me either - and I have the exact same version.
# Posted By Kevin Penny | 5/22/09 10:03 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.