Hey Rich, how are things at Site? I think "sessions" is the wrong label for this (although I could be wrong) :) -- I believe scope_identity() works on the "batch", whereas an open session for example in Query Analyzer can execute multiple batches. I think CF Server in particular only creates one session at a time for each DSN and then executes all queries within that same session and each cfquery tag pair is a new batch.
> In MS SQL I've seen two ways to handle returning the last > inserted primary > key: > INSERT INTO t_doctors (#ColNames#) > VALUES (#preserveSingleQuotes(ColValues)#); > SELECT SCOPE_IDENTITY() AS newpkey; > This will return the last inserted primary key for the > current scope > (session), so there is no concern of race conditions. > INSERT INTO t_doctors (#ColNames#) > VALUES (#preserveSingleQuotes(ColValues)#); > SELECT @@identity AS newpkey; > While this will return the last inserted primary key > across scopes > (sessions). Using @@identity will leave you open to > potential problems with > race conditions. > This is from information I've read in the BOL as well as > been informed from > our DBA. I can say that when using SCOPE_IDENTITY() I've > never found any > errors under load, but if anyone else has any input I'd > love to hear it. > Rich Kroll > Application Developer s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236881 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
